Hi
I Have two set of data in different column suppose Data1 and Data 2. Is there any formula to get all data that is present in data 1 but missing from data 2. And i want to get them in new column.
Thanks for your time.
Best Answers

Try something like this:
=JOIN(COLLECT([Barcode 1]:[Barcode 1], [Barcode 1]:[Barcode 1], FIND(@cell, JOIN([Barcode 2]:[Barcode 2], ", ")) = 0), ", ")

There are a few different parsing solutions tucked away here in the community that you will need to leverage.

Here is what I came up with.
=IFERROR(IF(VLOOKUP([Barcode 1]@row, [Barcode 2]:[Barcode 2], 1, false) = [Barcode 1]@row, ""), [Barcode 1]@row)
Basically, it looks at the value in barcode 1 and compares it to barcode 2's list and then leaves it blank if it finds it but repeats it in column 3 if it doesn't find it in column 1.
I set it up as a column formula so it automatically works on the each row.
Answers

Is there a list of several items? Can you share a screenshot? That would help us to understand what exactly you are comparing?

Hi Mike,
So here in Picture Barcode 1 and Barcode 2 has some different value. I want to know if there is any formula so that i can get all the unique barcode that is not present in Barcode 2 but present in barcode 1. I want that value in column 3.
Thanks for you time,

Try something like this:
=JOIN(COLLECT([Barcode 1]:[Barcode 1], [Barcode 1]:[Barcode 1], FIND(@cell, JOIN([Barcode 2]:[Barcode 2], ", ")) = 0), ", ")

Hi Paul,
Thank you for helping, it works but all the value are in one cell. I want one value in one cell. Is that possible?

There are a few different parsing solutions tucked away here in the community that you will need to leverage.

Can you share some so that I can try to work on that. Thanks

Here is what I came up with.
=IFERROR(IF(VLOOKUP([Barcode 1]@row, [Barcode 2]:[Barcode 2], 1, false) = [Barcode 1]@row, ""), [Barcode 1]@row)
Basically, it looks at the value in barcode 1 and compares it to barcode 2's list and then leaves it blank if it finds it but repeats it in column 3 if it doesn't find it in column 1.
I set it up as a column formula so it automatically works on the each row.

@Mike Wilday That's a great idea. I wish I had thought of it. Haha.

Haha. @Paul Newcome thanks! Keep in your reference for the future 😝
Help Article Resources
Categories
Check out the Formula Handbook template!