Google Sheets – Reformat Event Data for CSV File

google sheets

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.

Best Answer

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

Related Topic