1

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.

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

  • Thank you so much! As quite a spreadsheet beginner that blew my mind as to how you achieved so much just using "{},;" symbols. =) Translated it to my problem and it works perfectly. I would up-vote if I had enough reputation. Thank you again!! I will look into the normalizing cross-tabbed data literature too. – Sonke Hee Oct 14 '15 at 9:27
1

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).

  • Thank you for the answer! Tried it and it worked on the sample sheet, but struggled a little to get it to work on my actual data. I like how this returns the results in an order though! But I think I will stick with the other answer for ease at this point. =) This was what I was hoping to do when I first tried, but had no idea how to achieve... Thanks for this, learnt the rept function and query function when looking through this. Very much appreciated! – Sonke Hee Oct 14 '15 at 9:51
  • Oh, and sorry that I cannot up vote the answer. Not enough reputation at the moment. =( – Sonke Hee Oct 14 '15 at 9:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.