Joint/Collect

Whalstead1

Hello,

I have searched the forums and found a lot of information on JOIN and COLLECT functions, but I cannot quite figure out how to do what I am looking to do.

I have a form for staff to fill out and each populates a new line. However, due to logic, the columns will not overlap (so basically column Status Update 1 would only be filled out once as that question is specific if the person who fills out the form).

What I am trying to do is to see if there is a way to combine these rows into one row, if the Event Name is the same and the Date is the same. So I would be looking for 1 row that would have:

Event 1 6/17/2024 Update number one update number 2

Is it possible to do this?

Thank you!

Jason Tarpinian

It sounds like you need a helper column to get this done, I've labeled it "Updates Combined"

=IF(ISBLANK([Status Update - 1]@row), "", [Status Update - 1]@row) + IF(ISBLANK([Status Update 2]@row), "", [Status Update 2]@row) + IF(ISBLANK([Status Update 3]@row), "", [Status Update 3]@row)

I used the ISBLANK because for some reason one of the cells was returning a 0 if I just added them together. Then you can use the JOIN(COLLECT()) functions to return the value you're looking for:

=[Primary Column]@row + " " + Date@row + " " + JOIN(COLLECT([Updates Combined]:[Updates Combined], [Primary Column]:[Primary Column], [Primary Column]@row, Date:Date, Date@row), " - ")

You can play around with the formatting on how you'd like all the values to be separated in the end. CHAR(10) is a line break if you'd like the look of that, I think it looks nice when using the JOIN function.

Whalstead1

Hi Jason,

Thank you for your answer. When I tried to do what you said, I got a circular reference error. However, I am not sure if the output is what I need. What I am looking for is that if Primary Column and the Date Columns have the same value, then it will put the value in Status Update - 1 and Status Update 2 into one row. I added a row at the bottom as to what I am hoping to be able to get. Thank you!

Jason Tarpinian

It sounds like then you'd need a separate sheet where you can link distinct Event + Date data to. First build a helper on your source sheet:

=[Primary Column]@row + "&" + Date@row

Then on another sheet (for simplicity of testing, I built this all on a single sheet, so any column reference in the formulas below would be cross-sheet references to the source) built out the following columns. "Distinct Value" would just be numbers 1-1000… and "Distinct Match" will pull in the nth distinct match from the helper.

Now you can use this distinct value to bring in all the event data. Build out the distinct Event/Date columns:

=INDEX([Primary Column]:[Primary Column], MATCH([Distinct Match]@row, [Distinct Helper]:[Distinct Helper], 0))

=INDEX(Date:Date, MATCH([Distinct Match]@row, [Distinct Helper]:[Distinct Helper], 0))

Lastly for the updates, you'd have to use a JOIN(COLLECT()). Just change the bold reference to each update 2/3 for the other formulas.

=JOIN(COLLECT([Status Update - 1]:[Status Update - 1], [Distinct Helper]:[Distinct Helper], [Distinct Match]@row), "")

In the end, your second sheet would look something like this, which I think is more what you're going for?