MS Excel Find & Match Duplicate Values & Return Duplicate Unique Value

0
votes

Using a MS Excel Formula, I have three Columns (i.e., Name, Phone, and Duplicate). I'm currently using a formula that finds matching duplicates based on two criterion (i.e., "NAME" and "DUPLICATE") and displays if duplicate is found to display in "Duplicate" column.

I would like an Excel Formula that if a duplicate record is found, to return the unique "PHONE" value, that corresponds and matches with the first instance of the duplicate "NAME" and "DUPLICATE" record and displays unique "Phone" number on the same row adjacent to duplicate record.

See Original Example Spreadsheet:

enter image description here

In the Sample output shown below, using MS Excel formula; instead of only identifying that a duplicate record has been found (i.e., Name & Duplicate); to display the unique value (Phone) identified as matching Name and Duplicate.

I would like to display the unique or second matching "Phone 2" as follows... Thanks

Sample Record Output Desired

enter image description here

1
excelexcel-formuladuplicatesmatchunique

1 Answers

2
votes

In D2 as,

=IF(COUNTIF($A$2:$A2,$A2)=1,IFERROR(INDEX($B:$B,AGGREGATE(15,6,ROW($A$2:INDEX($A:$A,MATCH("zzz",$A:$A)))/($A$2:INDEX($A:$A,MATCH("zzz",$A:$A))=$A2),COLUMN(B:B))),""),"")

Fill down and right as necessary. Filling right will pick up the third, fourth duplocates if available.