JOIN(COLLECT formula showing blank cells

Eugene Rock

I have searched the forums and tried several different variations of the JOIN(COLLECT formula, and this one is the only one that doesn't give me an error.

I am trying to combine rows that have duplicate locations. The duplicate is flagged in "Column 23" (helper column). So I want the duplicate rows to move to another sheet and join all of the cells on that sheet. The way I have it now, it returns a blank cell.

What am I doing wrong?

heyjay

=JOIN(COLLECT(
{Copy of Do not use Range 6}, 
{Copy of Do not use Range 2}, CONTAINS("OK", @cell)), ", ")

HAS is for exact match while CONTAINS is for partial match

Eugene Rock

I made those changes and it's still showing a blank cell

Paul Newcome

What are your ranges for your cross sheet references?

Eugene Rock

Eventually, I want to reference every column (the entire column). For the test I am trying to reference the entire "Notes" column.

Basically, we track locations and we have around 100 on the list so I want an easy way to combine rows and this was the only thing I could find.

Paul Newcome

Ok. But exactly which range is what in your formula?

Eugene Rock

Oh, sorry. Range 6 is Notes. Range 2 is column 23. I wasn't sure if I should be using the actual column name or not. I saw both when I was researching it, but when I selected it by clicking on then range, it ended up like this.

Paul Newcome

Try something more along the lines of:

=JOIN(COLLECT({Notes Column}, {Checkbox Column}, @cell = 1, {Location Column}, @cell = Location@row), "delimiter of choice")

Eugene Rock

That still comes back blank. I tried switching it to the actual names of the columns instead of the range number and that comes back #INVALID REF

Paul Newcome

Can you provide a screenshot of the updated formula open in the sheet as if you are about to edit it?

Eugene Rock

The second one gave me the invalid reference error. I'm sure I'm making a dumb, simple mistake. I haven't tried anything like this before.

Paul Newcome

What do you get with this:

=COUNTIFS({Location Column}, @cell = Location@row)

Eugene Rock

That gives me "46". I put it in a few different cells and it gave me the same answer.

Paul Newcome

And this one?

=COUNTIFS({Checkbox Column}, @cell = 1)