Unpivot Matrix to Tabular. Using counts of two variables into individual rows

Question

I have a basic register format, Matrix format, names in first column and dates across the top row.

A2:A20 Names (Range changes on class size)

B1:E1 Dates (Range changes)

A2:E20 = Counts numeric: (1 to 5)

I would like to convert this into a tabular format, unpivot the results considering the counts.

So if Name 1 attends twice in on Day 1 and Name 2 Attended once on Day 2 and Name 3 attend 5 times on Day 3: the output would show:

Row1:n1, d1

Row2:n1, d1

Row3:n2, d2

Row3:n3, d3

Could anyone help me with this? Will this work on Google Sheets?

Best to pick one platform... – Tim Williams Aug 04 '15 at 17:55 — Tim Williams, Aug 04 '15 at 17:55
apologise, it's my first post. – Dwain Bowman Aug 05 '15 at 08:58 — Dwain Bowman, Aug 05 '15 at 08:58

score 2 · Accepted Answer · answered Aug 05 '15 at 02:28

For a Google Sheets solution:

=ArrayFormula(TRANSPOSE(TRIM(SPLIT(QUERY(TRANSPOSE(QUERY(REPT(A2:A20&", "&B1:E1&" ",B2:E20),,100)),,100)," ",0))))

The REPT forms a string for each value n in the matrix that is a repetition of comma-separated name and day, n times. The inner QUERY is a trick to robustly concatenate each column, space-separated. The 100 just needs to be a value that you know exceeds the number of rows in the matrix.

TRANSPOSE makes the resultant array vertical, and the outer QUERY again concatenates this array, space-separated (the 100 needs to exceed the number of columns in the source matrix, in this case).

You end up with one string, with the name-date pairs separated by at least two spaces in a row. The SPLIT breaks apart the string by those double-spaces, and TRIM gets rid of unwanted spaces at either end of the resulting strings. The outer TRANSPOSE makes the output vertical.

This works really well for Google Sheets! Explanation is also very useful. — Dwain Bowman, Aug 05 '15 at 08:21

Unpivot Matrix to Tabular. Using counts of two variables into individual rows

1 Answers1

Linked