Find duplicate excel value based on multiple criteria

Question

I have a spreadsheet that has 4 columns (see below for example). The first column has a unique ID, and then the next three are date requested, Customer name, and Country. I want to figure out how to match IDs when the other three columns are the same.

Current Data Set:

ID | Date Requested | Customer name | Country
1      1/1/2019         Bob Smith      USA
2      2/2/2019         Sal Jones      DE
3      1/1/2019         Bob Smith      USA
4      12/2/2017        Jim Gold       USA
5      2/2/2019         Sal Jones      DE

I want to be able to create another column that would show the ID of row that has the same information. For example, the new table would look like:

ID | Date Requested | Customer name | Country | Duplicate ID
1      1/1/2019         Bob Smith      USA         3
2      2/2/2019         Sal Jones      DE          5
3      1/1/2019         Bob Smith      USA         1
4      12/2/2017        Jim Gold       USA
5      2/2/2019         Sal Jones      DE          2

Is there a way to use VLookUp for the new column or some other function in excel?

EDIT: The end goal is to be able to actually link the two together with a common 'Group' or ID' number. For example, the table would look like this in the end:

ID | Date Requested | Customer name | Country | Duplicate ID | Group
1      1/1/2019         Bob Smith      USA         3            1
2      2/2/2019         Sal Jones      DE          5            2
3      1/1/2019         Bob Smith      USA         1            1
4      12/2/2017        Jim Gold       USA
5      2/2/2019         Sal Jones      DE          2            2

This way, you could filter the Group Column and the two duplicate entries would be next to each other/have a common link in the same column.

Can there be multiple duplicates, or always just one? Are your IDs purely numeric, or alphanumeric? — BigBen
Do you have a next step? or is this the final result? If you are trying to do something other than just an audit entry, there might be a better method of obtaining your final desired result. — Ron Rosenfeld
will there always be only one duplicate or could be more than one duplicates? and what is your ultimate goal after finding the ID of duplicated entries? deleting them? — Terry W

JvdV JvdV · Accepted Answer · 2019-10-16T12:32:03

Here is an option:

Formula in E2:

=IFERROR(MATCH(1,INDEX(($B$2:INDEX(B:B,COUNTA(B:B))=B2)*($C$2:INDEX(C:C,COUNTA(C:C))=C2)*(($D$2:INDEX(D:D,COUNTA(D:D))=D2))*(ROW($A$2:INDEX(A:A,COUNTA(A:A)))<>ROW()),),0),"")

In case your ID do not match the MATCH =).... you'll need to implement another INDEX:

=IFERROR(INDEX($A$2:INDEX(A:A,COUNTA(A:A)),MATCH(1,INDEX(($B$2:INDEX(B:B,COUNTA(B:B))=B2)*($C$2:INDEX(C:C,COUNTA(C:C))=C2)*(($D$2:INDEX(D:D,COUNTA(D:D))=D2))*(ROW($A$2:INDEX(A:A,COUNTA(A:A)))<>ROW()),),0)),"")

It's a bit extensive, but I wanted to make it dynamic for you to avoid using whole column references and thus speed up your formula. Speed > Length

While it's an array formula, don't worry to enter it as such. The INDEX after the MATCH function catches the results into an array.

Find duplicate excel value based on multiple criteria

1 Answers