Searching for Duplicate entries

[tombevans]

Using Excel as part of XP Professional, I am trying to find Duplicat
entries. I have five columns of data as exampled below:

Tape - Index - Asset - Duplicate - [Other entries]

DT1 - 1 - AN1 - Duplicate
DT2 - 1 - AN2 - Duplicate
DT1 - 3 - AN1 - Duplicate
DT6 - 5 - AN1 - Duplicate
DT6 - 4 - AN2 - Duplicate
DT7 - 2 - AN3 - Unique

The "Duplicate" column is essentially a check of which items in colum
3 (the "Asset" column) are unique. IE since there is only one instanc
of "AN3" the entry is "Unique". Since there are two copies of "AN2" th
entry says "Duplicate", and so on.

Here's the problem. If Column 4 "Duplicate" contains the wor
"Duplicate" I need to display - in a fifth column "[Other Entries]"
ALL tape numbers in column 1 ("Tape") that have exactly the same entr
in Column 3 ("Asset"). For example, in the table above, if taking AN
as an example:

Tape - Index - Asset - Duplicate - Other Entries

DT1 - 1 - AN1 - Duplicate - DT1 DT6
DT2 - 1 - AN2 - Duplicate - DT2 DT6

Possibly a macro of some kind which highlights duplicate entries woul
suffice...I cannot think of a way around this. Does anyone have an
ideas?

Thanks in advance!

To

 

[cornishbloke]

Hi Tom,

putting the following formula in the Duplicate column cells works for
the example you provided:

=IF(COUNTIF(C:C,$C2)>1,"Duplicate","Unique")


Clearly the reference to $C2 would need to be changed according to the
row the formula is in but providing you enter the formula correctly in
this cell you can drag and copy it to the rest of the column and it
will do this automatically.

Re, the 'Other entries column'. I'm not sure of a way of doing this
off-hand but wouldn't it be easier to filter the list as-and-when you
need to see details of the other entries with the same Asset No?

Hope that helps.

 

[tombevans]

Thanks a lot!

That's exactly the formula I'm using to search for duplicates, but it's
good to see we are thinking along the same lines!

Unfortunately, I have about 36000 lines to sift through, which is why I
need to try to automate the process. It might end up being impossible
to do it any other way, but I want to exhaust all possibilities first!

Thanks

Tom

 

[cornishbloke]

Tom,

sorry mate, that'll teach me to scan read - didn't mean to patronise.

If you have that many rows have you tried creating a pivot table? That
could provide summary of the number of duplicates and then, by clicking
on the number within the pivot table you could then have all the tape
names with the same asset number.

Good luck!

 

[tombevans]

Thanks again mate I didn't find you patronising at all - I was very
greatful for your input! Apologies if I sounded annoyed with you! I
wasn't in the slightest. I am however, frustrated with this
spreadsheet! I have tried a pivot, and it ALMOST gives me what I need,
but not quite. I fear it may have to be a manual process after all!
Thanks again for your help though, it is really appreciated. As I say,
it's reassuring, because it makes me think that the way I've already
gone about things is the right one so far!

Tom

 

[Guest]

Hi
You could try doing advanced filter to new location for criteria equal to each different asset then in other entries column write formula like this where aa is column where tape is after filter criteria an1 and ag is column where tape is after filter criteria an2
if (rc[-2] = "an1",aa1&a2&aa3 etc,if(rc[-2]="an2",ag1&ag2&ag2etc),0)
it may be long winded at first but could help
this is my first reply so I hope I make some sense