CODEWITHSUNDEEP
excelexcel-formulavlookup
eric123333
eric123333

Reputation: 1

Find duplicate excel value based on multiple criteria

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.

Upvotes: 0

Views: 1690

Answers (1)

JvdV
JvdV

Reputation: 75860

Here is an option:

enter image description here

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.

Upvotes: 3

Related Questions