Q:

Excel - checking for duplicates from a list of values within two columns of different groups of IDs

I am trying to find a way to remove duplicates from a list of values between a column of "have and not have" and includes everything from the original list. I need this to repeat for each set of IDs.
This list would be: A1, B1, C1, D1, E1.
An example of the raw data would be:
UniqueID | Have | Not Have
---------------------------
Z12345   | A1   | B1
Z12345   | A1   | C1
Z12345   | A1   | D1
Z12345   | A1   | E1
Y12345   | A1   | D1
Y12345   | B1   | E1
Y12345   | C1   | D1

The output I'm trying to get is:
UniqueID | Have | Not Have
---------------------------
Z12345   | A1   | B1
Z12345   |      | C1
Z12345   |      | D1
Z12345   |      | E1
Y12345   | A1   | D1
Y12345   | B1   | E1
Y12345   | C1   | 

I can't think of any way to approach this. I thought maybe running If statements to check if the IDs are matching and then run an if statement to remove the duplicates, but I couldn't figure out how to run it separately for each set of IDs. Any help or direction on how to approach this would be really appreciated!

A:

Here's my attempt using formulas. I would consider this just a starting point to spark ideas or improve on. Basically I use the offset function to create a dynamic range for each UniqueID set. Can't guarantee that this totally works and with all the lookups I would imagine it might be really slow with a large data set.
I would not consider this robust as I made quite a few assumptions:

Data is sorted by UniqueID
There are no duplicates across columns
The spreadsheet is setup exactly like this

First enter the following formulas:
D2: IF(A2<>A1,IFERROR(VALUE(D1),0)+COUNTA(A:A),D1)
E2: MATCH(B2,OFFSET($A$1,MATCH(A2,A:A,0)-1,1,COUNTIF(A:A,A2),1),0)+D2
F2: MATCH(C2,OFFSET($A$1,MATCH(A2,A:A,0)-1,2,COUNTIF(A:A,A2),1),0)+D2
G2: IF(E2>IFERROR(VALUE(E1),0),B2,"")
H2: IF(F2>IFERROR(VALUE(F1),0),C2,"")

Then copy these formulas down to the rest of the rows. Toggling formulas on (using ctrl+tilde) it should look like this:

Results should look like this (columns G & H):

