Google spreadsheet: reformat event data (several dates on one row/event) to create a .csv file (one date per row/event)

Question

I currently have a list of several recurrent events, with dates of recurrence on the same row. I hope to create a .csv file for importing into calendars, so would like to have each recurrent date on a separate row in a spreadsheet.

Not entirely sure how to phrase this, so I set up a sample spreadsheet with what I aim to do (including how far I have gotten with concatenate/transpose/etc).

---

In short, I have data such as (each character a separate cell in a spreadsheet):

A   X   1   2   3

B   Y   2   4

C   Z   1   5

And I hope to create individual rows for each 'event' (ABC) and 'date' (12345) combination (hopefully keeping the info, XYZ, intact if possible... but can achieve this with lookups if needed later):

A  X  1

A  X  2

A  X  3

B  Y  2

B  Y  4

(etc)

---

Any help is greatly appreciated! Including links to anywhere I may have missed online relating to this.

Answer 1

Short answer

Formula using the Google Sheets array handling feature:

=Filter(
   {{C5:C7,D5:D7,E5:E7};{C5:C7,D5:D7,F5:F7};{C5:C7,D5:D7,G5:G7}},
   Len({{E5:E7};{F5:F7};{G5:G7}})>0
 )

Long answer

The question is about normalizing cross-tabbed data. There are a lot of posts about this topic. The basic keywords that could help to find them are

• spreadsheet
• normalize
• cross-tab

Several of the results will be based in a programming language. Others could use "complicated" formulas that could be necessary in complex situations, but in the linked demo spreadsheet, the data is in C5:G7. C5:D7 are the row headings, and E5:G7 are the cross-tabbed data. This could be handle by using a simple solution based on Google Sheets arrays and could be easily adapted to normalize a range with any number of rows and few additional columns.

Google Sheets can handle arrays by enclosing values between curly brackets. Some people call them embedded arrays. This feature could be used to easily normalize simple cross-tabbed data

References

• Using arrays in Google Sheets - Docs editors Help

Answer 2

As an example of a complicated formula that hopefully delivers the expected result, try:

=ArrayFormula({VLOOKUP(transpose(split(concatenate(rept(C5:C&char(9), LEN(E5:E&F5:F&G5:G)/5 )),char(9))), C5:G, {1,2}, 0), TRANSPOSE(SPLIT(JOIN(" ", query(transpose(E5:G),,rows(E5:G))), " "))})

This will allow you to keep the ranges open ended (in case there is more data than the ranges you provided as an example), as long as the 50000 character limit of the CONCATENATE-function is not exceeded.

NOTE: if you want to try this formula, make sure there is no data under your source table (because of the open-ended ranges).