How can I find "matching" records and display data from those records

Hello

I have a very large spreadsheet with ~ 10,000 rows.

What I am trying to achieve is to identify any rows where the text in a specific column matches that same column in another row. I then need to be able to display some text from those matches.

Essentially, what I'm looking to do is identify clusters of records. The spreadsheet will be updated with new records daily so I need to find a simple solution I can use everyday. I'm actually only interested in finding if any of the new records added daily, match any older records. An example, shortened table is below... I hope this makes sense.

Column A Column B Column C Column D (output column)
154 Brisbane OrangeRed 154 (Brisbane),1531 (Adelaide), 7898 (Darwin)
2504 Sydney RedYellow 2504 (Sydney)
5134 Melbourne BluePink 5134 (Melbourne)
1531 Adelaide OrangeRed 154 (Brisbane), 7898 (Darwin), 1531 (Adelaide)
8645 Perth GreenBlue 8645 (Perth)
5484 Singapore BluePink 5484 (Singapore)
7898 London OrangeRed 154 (Brisbane), 1531 (Adelaide)
1235 Singapore YellowGreen 1235 (Singapore)

I'm not sure of the best way to achieve this so any/all suggestions appreciated!
Thanks in advance

.
Hopefully this will get it done for you :

Please Login or Register  to view this content.

Thankyou for this code. It seems to be highlighting any rows that have a match somewhere else in the list. What I'm really looking for is a way to identify that if Row 5 and Row 16 have specific cells matching; then in another column at the end of the spreadsheet, it would display data aobut Row 5 and Row 16. Does that make sense?

For example, Cell A is a list of reference numbers, Cell B is a list of locations, Cell C is a list of injury types. If Cell B and Cell C are duplicated anywhere else in the list, then I want to display the reference number. Does that make sense?

Happy to provide some more example data.

Thanks for your time.

Try

Please Login or Register  to view this content.

This is amazing!! Thankyou so much Can I ask one tiny thing? Can you explain which sections of the above code relate to each column? I need to use this same process in another spreadsheet which has extra columns. In the new spreadsheet the affected columns are A (same as before), Q (was Column B) and AY (was Column C).

Once again, thanks for your help.

Since your data starts from A1, and C column is the unique key.
1, 2, 3 represents column reference.

Thankyou both so much. I can't explain how much time this will save me. Forever grateful!!

You are welcome and thanks for the rep.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.