How to amend a column based on duplicates in another and leave a unique value in Excel

Question

I have a spreadsheet which has a lot of duplicates I need to cleanse but need to ensure the right data in another column is kept.

Data and desired outcome

Essentially in Column E there are duplicate values but these values could be duplicated any number of times, it is not the same amount each time.

In Column D for each record there should be either an A or B or blank.

Now the trouble is some duplicate sets have different values in column D. I need a way to remove all the duplicates from column E ensuring that each row in column E is unique while still ensuring the right value is kept from column D.

There are currently 3 different results in the raw data:

result 1: The duplicate sets (eg all HC0206 duplicates or HC0208 duplicates in column E) have the same value in column D (either all blank, all A or all B) - These are fine and don't cause a problem.

result 2: The duplicate sets have both blank and A in column D - When duplicates are removed an A must remain in column D.

result 3: The duplicate sets have both blank and B in column D - When duplicates are removed a B must remain in column D.

No duplicate sets have both A and B so we don't have to worry about that possibility.

I just can't work out how to ensure that when the duplicates are removed from results 2 and 3 above, that the letter remains and not the blank. If I could work out a way to ensure that all duplicate sets have the same value in column D then I could just remove duplicates without issue.

Any help would be greatly appreciated.

Thanks

Answer 1

Talking about overthinking.. you could realize it by formula in Office 365:

=LET(sorted,SUBSTITUTE(SORT(SORT(FILTER(D:E,E:E<>"","")),2),"",""),
     uniqueE,UNIQUE(INDEX(sorted,,2)),
     matchD,INDEX(INDEX(sorted,,1),MATCH(uniqueE,INDEX(sorted,,2),0)),
CHOOSE({1,2},matchD,uniqueE))

sorted-part makes sure column D:E are sorted by column 1, then 2 and blanks (that will result in 0) are shown as actual blank. The sorting for later use.

uniqueE-part results in the unique values in column E

matchD-part shows the match of the unique values uniqueE in sorted. The first match in sorted column 2 will return the indexed value of sorted column 1.

matchD followed by uniqueE is your spilled result