Comments | fionnaghal: Excel is winding me up

fionnaghal

Excel is winding me up

Mar 16, 2009 15:09

I don't suppose anyone knows how to fund duplicate information in excel? I'm looking for duplicate NI numbers in a spreadsheet with 13000 lines.... Filters don't see to help. I need to find them and report on them. Will carry on trying, I know it's possible, as my boss does it. *sigh*. Tum te tum. Edit - using 2007 and ( Read more... )

Comments 10

arabis March 16 2009, 15:26:21 UTC

Which version of Excel are you using?

In 2003 (maybe others) you can 'Filter for unique records' (search help for that term). Could you make a copy of your spreadsheet (I definitely wouldn't do it on the 'live' one), do that filter, delete the unique records and see what's left?

There are probably better ways of doing this, but I'm not an excel expert...

arabis March 16 2009, 15:28:10 UTC

Probably expressed better here: http://office.microsoft.com/en-us/excel/HA010346261033.aspx?pid=CL100570551033

gigolohitman March 16 2009, 15:35:44 UTC

Hard to describe, but I'd do something like:

=countif(Range, =A1)

Where A1 is the first row in your range. Copy that down past along the entire range, so you get a = B1, =C1 and so on.

Then look for values greater than 1. A 1 means Excel has counted 1 instance of that value in the range, so it is unique. 2 or more means it has duplicates.

0 means bad times :-D

gedhrel March 16 2009, 18:12:51 UTC

n-squared goodness! (Mind you, if Excel is still limited to 64k rows that's hardly a problem.)

gigolohitman March 16 2009, 18:30:00 UTC

The efficiency of computation time is as nothing compared to the efficiency of T-time in the total problem solving time :-p

I did write a Macro I named "Monday" once. Perhaps my bad programming habits had some influence on that :-p

gedhrel March 17 2009, 08:59:52 UTC

Sort it and add a column to check if a1=a2.
n log n.

Thread 5

nicnac March 16 2009, 16:41:28 UTC

Jim says have you tried sorting on that column?

skean March 16 2009, 18:50:43 UTC

I make a pivot table of it and filter for counts greater than 1 usually.

uselessinfomine March 17 2009, 22:42:18 UTC

I would:
1) Order the entire sheet by the NI number column.
2) Add a header row, so the numbers start in row 2 (if they don't already).
3) In the last column, after the data, add the following formula (for ease of writing, I'm assuming the column full of NI numbers is cloumn A):
=if(A2=A1,"Duplicate","")
4) Copy this formula down the 13,000 lines.
5) Filter by this column for "Duplicate". This will only give your duplicate records.