Excel Help.!!

Columbo · 26 Sep 2007 at 13:39

I have one column of data that contains 61,000 lines. Its made up of picking locations that will be deployed onto a new warehouse management system for the company that's contracted me.

Anyway, does anyone know a formulae to check to see if any of these are duplicated over the 61,000 lines. I'm normally fine with this sort of thing but just can't seem to come up with anything, short of scrolling through all the lines.

Cheers.

branddaly · 26 Sep 2007 at 13:59

nice guide on doing this here;

http://www.mrexcel.com/tip063.shtml

Which also happens to be the first result if you google for 'excel find duplicates'

Tomsk · 26 Sep 2007 at 14:02

The function you want to use is MATCH. Look it up in Excel help.

branddaly · 26 Sep 2007 at 14:05

Tomsk said:
The function you want to use is MATCH. Look it up in Excel help.

Match would require looking up every single possible permutation, and I think it only gives the position of the first, or closest hit. I'm not sure thats what the OP is after.

Columbo · 26 Sep 2007 at 14:24

I would have looked in google but I'm posting on my PDA currently which does'nt display all websites correctly. It's not the best spec but it does its job.

I'm just looking to find any locations that have keyed more than once and delete 2nd one. This data will then go into an SQL database and be loaded into the new system from SQL.

Tomsk · 26 Sep 2007 at 14:32

branddaly said:
Match would require looking up every single possible permutation, and I think it only gives the position of the first, or closest hit. I'm not sure thats what the OP is after.

Thinking about it a bit more now, MATCH wouldn't work.

branddaly's link works.

Another easy method is to sort column A
in cell B2, type the following formula, =if(a2=a1,1,"") and fill down.
Any duplicates will then be indicated by a 1 in column B

branddaly · 26 Sep 2007 at 14:33

ManCuBuS said:
I would have looked in google but I'm posting on my PDA currently which does'nt display all websites correctly. It's not the best spec but it does its job.

I'm just looking to find any locations that have keyed more than once and delete 2nd one. This data will then go into an SQL database and be loaded into the new system from SQL.

Ah, in which case just use an advanced filter, tick the unique records only box and copy it into a different column, this will remove the duplicates.

Columbo · 26 Sep 2007 at 14:38

Tomsk said:
Thinking about it a bit more now, MATCH wouldn't work.

branddaly's link works.

Another easy method is to sort column A
in cell B2, type the following formula, =if(a2=a1,1,"") and fill down.
Any duplicates will then be indicated by a 1 in column B

Worked like a charm. Much appreciated. Picked up 112 duplications.

Can't thank you enough..!!