Using JOIN-DISTINCT-COLLECT when values are not same data type

Skyler Wities

Hello,

I am trying to create a string of values delimited with "+"s. It is part of a more complicated sheet, that matches Lot IDs to barcodes and other information from referencing another sheet. Essentially, I want the user to put in a lot ID (such as DXXXX) and the return to be a list of all the barcodes in the format:

+barcode1+barcode2+barcode3+

I've used this formula:

="+" + JOIN(DISTINCT(COLLECT({Barcode}, {Lot ID}, [Enter Lot ID]1)), "+") + "+"

It usually works because the vast majority of the barcodes are numbers like 100102. However, a handful of barcodes have letters or other characters in them which causes the error #INVALID DATA TYPE.

My ideal goal would be to be able to list all barcodes in the format above, even when they have letters, but I don't think I can use the Distinct function with that at all. So, I would be okay if I could just skip over the barcodes that are not numbers, and only display the number ones. I have tried to use IF functions nested in Collect, and outside of Collect but I always get an error like #Incorrect Argument.

This is an example of something I've tried.

="+" + JOIN(DISTINCT(COLLECT({Barcode}, {Lot ID}, IF(ISNUMBER({Barcode}), [Enter Lot ID]1, ""))), "+") + "+"

I would really appreciate if anyone has any advice on what I should try. Thanks in advance for the help.

Paul Newcome

It shouldn't throw an error because of text because the JOIN converts everything to text anyway. What happens if you drop off the + "+" from the very beginning and end?

=JOIN(DISTINCT(COLLECT({Barcode}, {Lot ID}, [Enter Lot ID]1)), "+")

Skyler Wities

Hi Paul,

If I delete the "+"s it is the same error for the problem Lot IDs. For the working ones, it has the same return, just without a "+" at the beginning and end.

My thought process was that the nested DISTINCT might cause an error before the formula gets to the JOIN, although maybe that's not how the formula processing works.

Paul Newcome

The DISTINCT function can process both numeric and text values. That shouldn't be the issue. Hmm... I am going to have to think on this one and get back to you.

Skyler Wities

I'm not sure if that's right that DISTINCT functions can process both. The function list says of DISTINCT:

"All values must be of the same data type in order for the function to calculate.

For example, 1, 2, a, 2 will produce a value of 1 if counted. 1, 2, 2, 3 will produce a value of 3 because only 1, 2, 3 are distinct."

Paul Newcome

That's odd. I've used it before like that. I guess I am going to have to go back and double check that they are still working correctly. Having said that... Let's use a helper column to convert everything into text and then evaluate the helper column.

On the source sheet insert another column (that can later be hidden to keep the sheet looking clean) and use a cell reference to pull the lot id over with + "" (plus quote quote) on the end. This will convert any numbers to text and will not affect text values.

=[Lot ID]@row + ""

Then reference this column in your formula instead of the actual Lot Id column.

Skyler Wities

Thank you for your help. Adding the + "" doesn't seem to work, though. I still get Invalid Data Type error. It is not the Lot ID that needs to be either all numbers or all letters, it is the barcode.

I found that if I remove the DISTINCT function (like below), it will work for the problem Lot IDs but for all Lot IDs it shows duplicates, which is an issue.

="+" + JOIN((COLLECT({Barcode}, {Lot ID}, [Enter Lot ID]1)), "+") + "+"

Now I have the string of all the barcodes I want, dleimited with +s but there are duplicates. I'm now having trouble finding a good function to parse or split that string by the +s and then using DISTINCT to remove the duplicates. I'm not even sure if that would work any better because, DISTINCT still wouldn't work with text and numbers I don't think. However, if I got that far I could maybe figure out how to only join them if they are numbers only.

Paul Newcome

My apologies. If the barcode needs to be all text then simply adjust the helper column to look at the barcode column.