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:

Please Login or Register  to view this content.

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?

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):

Please Login or Register  to view this content.

then drag down

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

Please Login or Register  to view this content.

then drag down

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

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:

Please Login or Register  to view this content.