Extensive Data validation in excel

Posted by Mark Luckey on October 09, 2001 12:39 AM

Hello all,

nice to see you all online.

OK, here's the deal. Have several worksheets which contain data spread over about 15 columns, between 1200 and 65,000 lines long. I have to combine these worksheets, but wish to remove any entries which are identical across the 15 columns. Each existing worksheet is incomplete yet has duplicate data from others...if I combine them all, I get duplicates for many things. Affects the search on the final output. Data ranges (i.e. row 32567) is defined as different from the others if only one of the 15 columns (or cells) of data differs - an exact would be between 2 rows, if every value in the row was the same in corresponding columns.

Any idea how to fix this up? I need one final sheet which has all unique entries

Cheers

Mark

Posted by Anon on October 09, 2001 12:23 PM

Put all the data from all workbooks into one workbook (assuming the total rows do not exceed 65536).

In the first row of column 16 enter the following and fill down :-
=A1&A2&A3.......thru to ......A15

In the first row of column 17 enter the following and fill down :-
=COUNTIF($A$1:A1,A1)

Sort by column 17 and then delete rows where the value in column 17 is not equal to 1

Posted by Anon on October 09, 2001 12:26 PM

Correction .....

Correction :-

The formula in column 17 should be :-
=COUNTIF($P$1:P1,P1)