Concatenate multiple rows into one cell across multiple columns

Hello!

I have data by month for multiple plant species (Taxon column). There are 3 possible values for each month for each Taxon, and each value is shown on a separate row for each Taxon. I'd like to combine the rows so there is one row per taxon, and the values for each month are separated by a comma.

The problem is best explained in the attached file. The OriginalData shows what I have to start with, and the DesiredResult tab is what the final calendar should look like.

This is a small sample of the data, but I need to be able to do this repeatedly for multiple large data exports from Access. So, I'm looking for the easiest way to do this. Thank you!!

That is amazing! Thank you so much. This worked great. It wasn't working perfectly for taxa with 3 rows, but I tweaked it so that the parts in wavy brackets was "Fl","M","Im","Fl, Im", to account for cells with the value "Fl, Im", and it works perfectly.

In the second formula (columns AC:AO), is there a way the formula could be written so that it works automatically no matter how many rows of data are in the original list? In the example it was 18. I can change that number manually, and then drag the formula, and it works.

Thanks again, you made my day! I would've never figured this out on my own.

Worked perfectly, thanks again!!!

I grabbed the unique items in column A starting in row 21 on OriginalData using UNIQUE:

Formula:

Please Login or Register  to view this content.

Then, in cell B21, I put this:

Formula:

Please Login or Register  to view this content.

I copied down and across to be the same width as the original table.

This works because if you compare a range with a value, you get an array that's the same length and width as the range you're comparing to the value, so this:

Formula:

Please Login or Register  to view this content.

creates an array of TRUE/FALSE, depending on whether the row in original data matches the row header on the new table. Passing a range of the same size to the THEN part of the IF means the returned array will have the text in that column if the IF part is TRUE. I concatenate the returned array with an empty string to make sure it doesn't return a zero where there's an empty cell:

Formula:

Please Login or Register  to view this content.

If the IF part returns FALSE, then just return an empty string.

So, the IF returns a vertical array of the text in each row in that month's column, or an empty string if it doesn't match the row header in the new table. TRANSPOSE flips the array so it's horizontal, then TEXTJOIN joins each item in the array, separating them with a comma (first parameter in TEXTJOIN) and ignoring empty array items (second parameter).