IF(JOIN(COLLECT Statement

sawuzie

Hello!

I am using this formula to collect a set of ID numbers based on a criteria.

=JOIN(COLLECT([MED ID 2]:[MED ID 2], [Duplicates 2]:[Duplicates 2], >1), " ")

This gives me exactly what I need, but I want to wrap it in an IF function so that if the criteria is not met, the cell will say "No"

This is what I've been trying to use, but I get "invalid operation"

=IF([Duplicates 2]:[Duplicates 2] > 1, JOIN(COLLECT([MED ID 2]:[MED ID 2], [Duplicates 2]:[Duplicates 2], >1), " "), "No")

Cory Page

Ok, I think I see whats going on there is no error when nothing is found so things are coming out blank.

This should let you say no when the collect finds nothing, sorry I probably should have just built the formula and tested it real quick.

Updated: I had to add your original Join Collect its way easier for you to review that was I would think.

=IF(JOIN(COLLECT([MED ID 2]:[MED ID 2], [Duplicates 2]:[Duplicates 2], >1), " ") = "", "No", JOIN(COLLECT([MED ID 2]:[MED ID 2], [Duplicates 2]:[Duplicates 2], >1), " ")))

If you translate this to your sheet it should do the trick I would think..

Cory Page

I honestly dont use Join and Collect very much but I have seen multiple community posts talking about it over the past few days I may have to test this formula out more.

But if its like most formula's that search for things you should be able to add an iferror in front, give it a quick try and let me know if that does the trick if not I will try to wrap it in an if statement real quick.

=iferror(JOIN(COLLECT([MED ID 2]:[MED ID 2], [Duplicates 2]:[Duplicates 2], >1), " "),"No")

sawuzie

Hi Cory,

That did get rid of the error, but I don't seem to get "No" for the cells where the criteria is not met.

Cory Page

Ok, I think I see whats going on there is no error when nothing is found so things are coming out blank.

This should let you say no when the collect finds nothing, sorry I probably should have just built the formula and tested it real quick.

Updated: I had to add your original Join Collect its way easier for you to review that was I would think.

=IF(JOIN(COLLECT([MED ID 2]:[MED ID 2], [Duplicates 2]:[Duplicates 2], >1), " ") = "", "No", JOIN(COLLECT([MED ID 2]:[MED ID 2], [Duplicates 2]:[Duplicates 2], >1), " ")))

If you translate this to your sheet it should do the trick I would think..

sawuzie

It was unparseable because of the extra parenthesis at the end, but otherwise the formula worked, thank you!

Cory Page

@sawuzie perfect, sorry about the oversight glad it worked.