Join distinct collect

Mary Kam

=JOIN(DISTINCT(COLLECT({pots 1 quantity}, {NUMBER}, STUDY@row)), CHAR(10)) + JOIN(DISTINCT(COLLECT({POTS 2 QUANTITY}, {NUMBER}, STUDY@row)), CHAR(10)) + JOIN(DISTINCT(COLLECT({POTS 3 QUANTITY}, {NUMBER}, STUDY@row)), CHAR(10)) + JOIN(DISTINCT(COLLECT({POTS 4 QUANTITY}, {NUMBER}, STUDY@row)), CHAR(10))

I would like to collect the distinct values from 4 columns but somehow it added 3 numbers that should not be there ex: it should be 1 to 7 but it adds 13,71, 73

What is incorrect about the formula

Mary Kam

Thank you

Paul Newcome

Are those extra numbers in any cell that you are referencing?

Mary Kam

13,71, 73 does not exist anywhere. It seems to have combined the numbers together. EX: 1 and 3, 7 and 1, 7 and 3

So I am not sure the best way to do this formula. I need it to look at the four columns and pull out the unique values from each of the columns with the same conditions.

The columns are not side by side either.

Paul Newcome

Are you able to provide a screenshot of the source data?

Mary Kam

Paul Newcome

And what about the sheet where the formula is?

Mary Kam

Paul Newcome

Try creating a filter on the source sheet to see if any of those columns may contain one of those numbers.

Mary Kam

They do not, I have checked.

=JOIN(DISTINCT(COLLECT({pots 1 quantity}, {NUMBER}, STUDY@row)), CHAR(10)) + JOIN(DISTINCT(COLLECT({POTS 2 QUANTITY}, {NUMBER}, STUDY@row)), CHAR(10)) + JOIN(DISTINCT(COLLECT({POTS 3 QUANTITY}, {NUMBER}, STUDY@row)), CHAR(10)) + JOIN(DISTINCT(COLLECT({POTS 4 QUANTITY}, {NUMBER}, STUDY@row)), CHAR(10))

It seems that the first, join collect distinct which is in the first column, is fine, but the next ones join the values that are collected.

Paul Newcome

Which columns exactly are you pulling in?

Mary Kam

Pots 1 , Pots 2, pots 3, pots 4. I know in the formulas it has quantity added. It is because of privacy I needed to change column names

Paul Newcome

Your formula should be working. I don't see anything wrong with it. If you have applied a filter and it came up with zero rows for those three numbers but your formula is still pulling them, I would venture to say that there is a bug somewhere and it may be time to contact Support.

Mary Kam

After some research I resolved the problem. we need to add an extra CHAR(10)

=JOIN(DISTINCT(COLLECT({pot 1}, {number}, [STUDY]@row)), CHAR(10)) + CHAR(10) + JOIN(DISTINCT(COLLECT({pot 2}, {number}, [STUDY]@row)), CHAR(10)) + CHAR(10) + JOIN(DISTINCT(COLLECT({pot 3}, {number}, [STUDY]@row)), CHAR(10)) + CHAR(10) + JOIN(DISTINCT(COLLECT({pot 4}, {number}, [STUDY]@row)), CHAR(10)) + CHAR(10))

Mary Kam

Thank you

Paul Newcome

And that makes perfect sense too. My apologies for not noticing that sooner, but I am glad you were able to get it sorted.