Excel formula to locate any duplicate numbers on sheet 1

[nat101]

I have an interesting requirement. On a sheet with about 250 rows and more than 100 columns, most containing numbers, I need to locate anytwo cells whose numbers are the same.
I don't expect too many hits, but if I do, I might want to limit the 'search' to a range.
The next thing is, how the macro/formula should record the hits. Should I simply highlight the cells, or list all the hits in a range designated for output of the formula?

Thanks for the input.
-Nat

 

[DaleWatson123321]

Nat,

I see the day has passed and no one has yet offered a solution.

So here's one solution. Using Excel's database extraction function (Data - Filter - Advanced Filter), I would...

1) Extract each of the 100 columns of data to a SEPARATE sheet, appending each of the blocks of data to the SAME column. Let refer to this separate sheet as All_Data. The number will only occupy Column A.

2) From the All_Data sheet, extract a UNIQUE set of numbers to another separate sheet - let's call it Unique_Set. Again, the numbers will only occupy Column A.

3) In Unique_Set, in Column B, have pre-set formulas (or they can be inserted later) that use the COUNTIF function.
Example: =COUNTIF(data,A1) - where "data" is a range name assigned to Column A of the All_Data sheet.

4) Assign a range name (e.g. "unique_data&quot to the data in columns A:B of the Unique_Set sheet.

5) Using criteria that tests the data in Column B for being >1, extract to another sheet those numbers from Column A. Let's call the other sheet "Duplicates".

6) For the numbers extracted to Duplicates, assign a range name (e.g. "dup_list&quot.

7) Possibly reference "dup_list" from a ComboBox where the user can pick from the list. Then have VBA code that uses Excel's Search function to highlight the cells where the numbers are located.

I hope this helps get the "creative juices" flowing.

If you'd like help with any of the code related to Excel's not-so-easy-to-use "data extraction" capability, don't hesitate to involve me further.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[nat101]

Thanks Dale, for all that detail. BUT..before I think that all thru in 'heavy' mode, I obviously need to know where the heck the numbers are coming from!!! So, if it finds a pair [or more] of dupes I should know 'this is from A5 and G200 and AA66'. Yeah, true, I can search em later. Maybe it will suffice.
[also,] You expect a lazy guy to manually copy 100+ columns?
I can export the whole thing to ACCESS in this time.
I was [sort of] hoping for a cute little thing that walks all the cells repeatedly (as long as it takes) and marks the hits.

Hey (yep u got the juices flowing!) why not have one output sheet with column A containing a number and Columns B-Z (theoretically) containing the cell address where the number in column A is located. So, if any row has an entry in column C and up, its a dupe! I can write the pseudo code, but alas not the real code.

I need to sit on this a while.

TA
-Nat

 

[PeterMoran]

Hi nat101,

Can I just add my tuppence worth after all Dale's efforts, and suggest that before you go any further, that Chip Pearson has what I think is a great (because its so simple!) solution to highlight duplicates at the following address, and you should have it working in about 2 minutes.

http://www.cpearson.com/excel/duplicat.htm#HighlightingDuplicates

and check under Highlighting Duplicate Entries.

Good Luck!

Peter Moran
Two heads are always better than one!!

 

[nat101]

Thank you much! It looks like exactly what I was looking for.

-Nat