2

I am trying to go from a 'wide-formatted' table see image sample to a 'long-formatted' table see image sample using Google Spreadsheets.

I have created an spreadsheet to re-create the scenario, having this in mind:

  • Data is being updated by users on tab 'wide'
  • Id's can be added at any time (no Ids can be removed)
  • Tags can be replaced, removed or even a new tag can be added (limit is 9 tags)

Test document: https://docs.google.com/spreadsheets/d/1lKAFCON2Bh8n3etGKTEFI3LklGdfMT0wrKm1JyoOHag/edit?usp=sharing

Tab 'wide' has an example of how data is originally formatted. Tab 'long' is the expected output (all id-tags possible unique combinations)

Which is the best approach to get a 'long-table' formatted data that also enables me to capture any update on the original source of data?

Thanks!

player0
  • 124,011
  • 12
  • 67
  • 124
luamar
  • 314
  • 2
  • 7

1 Answers1

0

try:

={"id"\ "tag"; ARRAYFORMULA(SPLIT(QUERY(FLATTEN(
 IF(wide!B2:Z="";;wide!A2:A&"♦"&wide!B2:Z)); 
 "where Col1 is not null"); "♦"))}

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124
  • the first part of the formula was throwing me an error `{"id"\ "tag";...` I just removed it and force the column header names to be Id and tag and it worked! Thank you very much @player0! – luamar Jul 30 '20 at 21:17