Where to put JOIN to append data

Chiu Bar

Hello everyone,

I have two sheets that I'm working on - the first sheet collates all information supplied by survey correspondents and the second sheet calculates to derive any information needed.

First sheet:

Second Sheet (expected output):

I was able to compose a formula to get the proper count by cross-referencing the above sheets.

However, my formula using JOIN on Properties (second sheet) could not append the second information on the same cell. Any succeeding information with the same team assigned should also be appended.

This is my formula:

JOIN((IF(ISBLANK(INDEX({Property Name}, MATCH([Team Name]@row, {Team Assigned}, 0))), INDEX({Raw Land}, MATCH([Team Name]@row, {Team Assigned}, 0)), INDEX({Property Name}, MATCH([Team Name]@row, {Team Assigned}, 0)))), ",")

Any help will be greatly appreciated on the above.

Thank you.

Paul Newcome

You would need to use a hidden helper column on the first sheet to bring all property names into the same column.

=IF([Property Name]@row <> "", [Property Name]@row, [Raw Land]@row)

Then in your second sheet you would use a JOIN/COLLECT combo to pull in the helper column values based on the team.

=JOIN(COLLECT({Helper Column}, {Team Column}, @cell = [Team Name]@row), ", ")

Paul Newcome

You would need to use a hidden helper column on the first sheet to bring all property names into the same column.

=IF([Property Name]@row <> "", [Property Name]@row, [Raw Land]@row)

Then in your second sheet you would use a JOIN/COLLECT combo to pull in the helper column values based on the team.

=JOIN(COLLECT({Helper Column}, {Team Column}, @cell = [Team Name]@row), ", ")

Chiu Bar

Thanks, @Paul Newcome .

Will try it.

Chiu Bar

Thanks again, @Paul Newcome

It worked!

Paul Newcome

Happy to help. 👍️