Identifying Duplicete Entries

Topic

New Reply

 

 

i have a worksheet with names of about 100 ships. I have another worksheet with the names of about 300 ships. Many ship names are repeated on both worksheets.

My challenge is to identify which ships on the the 100 ship worksheet are duplicated on the 300 ship worksheet, then to mark a column on the 300 ship worksheet to show which ones also appear on the other (100 ship) worksheet.

The Help files seem limited to finding duplicate values on one worksheet.

Who can tell me how to do what I want?

Lou Sander

Reply | Quote

Replies

Have you tried VLOOKUP?

MATCH may also work.

If you post a subset with just ship names we can scratch something up for you.

cheers, Paul

Reply | Quote

Two quick ways to at least find the duplicates are “pivot tables” and  “conditional formatting”.

Both would require you to have a column with the name of the list for each ship name. (E.g. if Column “A” has the ship name, then Column “B” would have the list name.

Combine the two lists into one big list of all 400 names, then search for Internet advice how to use “conditional formatting”  for “Highlight Cell Rules” for “Duplicate Values”.

Searching for “Pivot Tables” for finding duplicates will find another way to go about it.

 

Reply | Quote

I underestimated the number of ships on the “100 Ship List”. I’ve uploaded both of them.

Ideal for me would be an additional column on each worksheet, with a mark of some kind indicating that this ship is also on the other worksheet.

This is all part of a project to identify the leaders of each ship’s reunion group, and the city where they are holding their reunion in 2021-2022. Most of the ships are out of commission now. and most of the reunion attendees are in their 60’s or older.

Lou Sander

Reply | Quote

This formula in 246 ships will do the lookup and return the ship name (it’s from LibreOffice so the syntax will be slightly different in Excel).

=VLOOKUP(A2,'file:///C:/tmp/311-Ships-from-Many-Reunions.xlsx'#$'From Many Reunions'.A$2:A$312,1,0)

Using MATCH will return a number which is the row of the match.

=IFNA(MATCH(A2,'file:///C:/tmp/311-Ships-from-Many-Reunions.xlsx'#$'From Many Reunions'.A$2:A$312,0),"")

The same formula can be used in the 311 ship sheet.

cheers, Paul

1 user thanked author for this post.

ussrankin

Reply | Quote

I’ll give it a try! I received another solution from a different Paul. Together, I think I have exactly what I need. This is REALLY helpful to us older guys who are still holding reunions of ships we were on many years ago. I’m 82, and my spreadsheet skills are fading fast.

Lou Sander

Reply | Quote

Attached are three XLS files.
The first two (246dup311 and 311dup246) are ‘your source’, plus columns.
477 is a Composite of all entries, singled up.

These files were processed in Lotus Approach, which was pre-XLSX.

1 user thanked author for this post.

ussrankin

Reply | Quote

Wow! Thanks! Pending verification, this is exactly what I need. You are a true Spreadsheet Genius, and you’ve helped make a lot of old guys happy!

Lou Sander

Reply | Quote

Lou, You are very welcome.
A couple of notes:

A – That little excursion concerning the Walke, where there are two hull numbers (although one perhaps is WWI era), reminds me that there is not always a one-to-one match between name and ship number. (I recall that there are instances where the same physical ship (I think a SS, not sure about USS) was assigned a new hull number and name.) And, names are being reused.

B – Some years back I ran across a site which I bookmarked but rarely have consulted. It may be of some use to you, or you may already be aware of it. There almost certainly are others akin. [ https://www.navysite.de/index.htm ]

C – If (when?) you find discrepancies, please notify me (perhaps via DM?) so that I can correct my procedures. Thank you.

Reply | Quote

You must be an old Navy guy! I was an ENS/LTJG from ’61 to ’65. Got out after that. You?

A – Wikipedia has a lot of that sort of thing about ships. I’ve done my part. See user:Lou Sander

B – I’m like you with Navysite. Aware of it, but don’t visit much.

C – None found so far. I’ll let you know.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

I’m like you with

Lou Sander

Reply | Quote