Hi

Keval

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.

Paul Newcome

Try something like this:

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

Paul Newcome

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

Mike Wilday

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.

https://help.smartsheet.com/articles/2481944-set-formulas-for-all-rows-with-column-formulas

Mike Wilday

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

Keval

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,

Paul Newcome

Try something like this:

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

Keval

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?

Paul Newcome

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

Keval

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

Mike Wilday

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.

https://help.smartsheet.com/articles/2481944-set-formulas-for-all-rows-with-column-formulas

Paul Newcome

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

Mike Wilday

Haha. @Paul Newcome thanks! Keep in your reference for the future 😝