vlookup?
Posted by Casey on May 24, 2001 9:39 AM
i have a worksheet that has approx 12000 rows, w/in those rows, there are part numbers in column A. some of those part numbers have duplicate part numbers w/in the 12000 rows in column A. what i'm trying to do is tell it to find the duplicate part numbers, label them false or whatever, just some value that i can tell it to sort by so i can delete it. so basically, what i want it to say is if a part number in A has a duplicate, then label it FALSE. i don't know code, only formulas, so if anyone can help me i would appreciate it!!
tia!!
Posted by Barrie Davidson on May 24, 2001 9:49 AM
Casey, a solution that might work for you.
(Assuming your part numbers are in column A, your data goes from column A to column D, and the first row is labels for the data) Sort your data by part number (column A) and then put the following formula in cell E3
=IF(A3=A2,"FALSE,"") and copy the formula to the end of your data. Then select the entire range of formulas in column E and Copy|PasteValues (over-writing the formula). You now have labeled your duplicate part numbers.
Hope this helps you out
Barrie
Posted by Aladin Akyurek on May 24, 2001 9:56 AM
Casey
One way to tell duplicates apart is to use conditional formatting.
Assuming your part numbers to be in A from A1 on.
Activate A2, activate Format|Conditional Formatting, choose Formula Is, and type the following formula:
=ISNUMBER(MATCH(A2,$A$1:A1,0))
Select Format, choose Pattern tab, and choose color red.
While in A2, click on the Format Painter, then select, patiently, the rest of 12000 cells.
Again patiently, delete all rows or contents of A-cells where you see a red background.
Cheers.
Aladin
Posted by IML on May 24, 2001 10:54 AM
Hi Casey,
You may also want to consider using the advanced filter on this. You could highlight column a and goto
Data-Filter-Advanced Filter
Select the Unique records only box and okay. You can filter it in place or copy to another location. Also, if you haven't visited Dave Hawley's site, you may want to find a response of his and click on his link. He has a section devoted to dealing with duplicates.
Good luck.
