Looking to summarize data from multiple rows to multiple columns so I can generate a document.

Nikeeta Patil

I have taken over employee attendance tracking at work. I create new rows in a sheet that supervisors go in and confirm time. The columns for that sheet are in the first photo. Each occurrence is captured in a new row. The issue is, I need to be able to generate a document and cannot do that for more than one row. So I am looking to summarize and transpose the data so that each employee has one row and the instances are in columns. I am not the best with formulas, even my coworker who is, says this is a heavy task. I am certainly ok if I need to do this in multiple steps such as the data goes into one sheet and from there into the final one. But I am at a loss on my own. Any help would be appreciated.

KPH

How about something like this:

Step 1

Use a JOIN function to get all of the data you need per infraction into one cell, something like:

=JOIN(Date@row:[6 Month drop off date]@row, " | ")

This can be one hidden column in your existing sheet.

Step 2

Transpose each row into a column.

Use an INDEX function to return the value from the new column from row 1 in column 1, row 2 in column 2, row 3 in column 3….

=INDEX(Combo:Combo, 1)

=INDEX(Combo:Combo, 2)

=INDEX(Combo:Combo, 3)

You would do this in your new summary sheet and replace the column name with a cross sheet reference, so the formula would look more like this (depending on the name you use for your cross sheet reference).

=INDEX({Combo}, 1)

=INDEX({Combo}, 2)

=INDEX({Combo}, 3)

The summary sheet would look like this:

Would that do the trick? Or do we also need to look at the employee name?

If so, you can add the name to the first list:

And to the second summary. Then include a COLLECT function in the INDEX to include only the rows where the names match. (The IFERROR is there to return nothing if there is no match, rather than an error message).

=IFERROR(INDEX(COLLECT(Combo:Combo, [Employee Full Name]:[Employee Full Name], Name@row), 1), "")

Change the 1 to a 2, 3, 4 etc. for each column. The summary table would look like this:

Hope that helps.