Removed duplicate values from 2 lists to give only the unique values in list 3

Hi,

I only want columns G and H to pull through values that are not duplicated between Columns A and B and Columns D and E so the expected result would be pulling what I have typed into G and H.

I am expecting this to be simple enough, but can't seem to get it right.

Any help would be greatly appreciated.

Many thanks
Tom

I am using MS365

Hi Ali,

Profile has been updated.

Thanks
Tom

one option

Formula:

G2: =FILTER(A2:B27,ISNA(MATCH(A2:A27,D2:D14,0)))

be sure to clear G2:H14 before applying above formula (first time), as will spill

Thanks XLent,

It works great on the example.

Is there anything that would work if if dragged a formula down as the real document I am working on has a few thousand rows.

Or could this also work if I expand the cells ranges in the formula?

Or could this also work if I expand the cells ranges in the formula?

yes, modify the ranges -- the FILTER effectively returns A:B range wherever A range cannot be found in D range

your title says remove duplicates, but your post, supported by the example file, suggests you want columns G and H to show items that are in columns A and B, but not in columns D and E.

here is an array formula for cell G2 (control + Shift + enter):

=IFERROR(INDEX($A$2:$A$27,MATCH(0,IFERROR(MATCH($A$2:$A$27,$D$2:$D$14,0),COUNTIF($G$1:$G1,$A$2:$A$27)),0)),"")

then drag down

here is an array formula for cell H2 (control + Shift + enter):

=IFERROR(INDEX($B$2:$B$27,MATCH(0,IFERROR(MATCH($B$2:$B$27,$E$2:$E$14,0),COUNTIF($H$1:$H1,$B$2:$B$27)),0)),"")

then drag down

Hi,

Thanks to both of you - they both seem to do the job.

Greatly appreciated,
Tom

for unlimited rows, just modify the formula from XLent to be the following:

=FILTER(A2:B,ISNA(MATCH(A2:A,D2:D,0)))