Google Spreadsheets Convert Matrix data into Relational rows

0
votes

My Google spreadsheet data is in a matrix form with "holes". See https://docs.google.com/spreadsheet/ccc?key=0Ati5T34RP9mEdGJfcUFzVDV4elZIbExkTGM4aktQT2c&usp=drive_web#gid=0

I'd like to create another sheet from the above data to look like what I've manually done in https://docs.google.com/spreadsheet/ccc?key=0Ati5T34RP9mEdGJfcUFzVDV4elZIbExkTGM4aktQT2c&usp=drive_web#gid=3

Any ideas?

2
google-apps-scriptgoogle-sheetsgoogle-sheets-formulaarray-formulasgoogle-sheets-query
2 questions: 1. there is a formula-based solution for this, but also can be achieved with Google Apps Script; did you have a preference for not using GAS? 2. Your spreadsheet is on the previous version of Sheets; is it viable to migrate to the newest version? – AdamL
I'd love to get my hands dirty with GAS. And, sure, migration to the newest version is certainly viable. – RudyF

2 Answers

2
votes

This should work on the latest version of Sheets:

=ArrayFormula(QUERY(VLOOKUP(HLOOKUP('Table Chart'!A2,'Table Chart'!A2:A,INT((ROW('Table Chart'!A2:A)-ROW('Table Chart'!A2))/COLUMNS('Table Chart'!B1:G1))+1,0),{'Table Chart'!A2:G,IF(ROW('Table Chart'!A2:A),'Table Chart'!B1:G1)},{SIGN(ROW('Table Chart'!A2:A)),MOD(ROW('Table Chart'!A2:A)-ROW('Table Chart'!A2),COLUMNS('Table Chart'!B1:G1))+{2,2+COLUMNS('Table Chart'!B1:G1)}},0),"select Col1, Col3, Col2 where Col2 is not null",0))

For the sake of completeness, this will work on the previous version, but the dates are converted into text strings; they can be converted into numerical values, but the formula would then become even longer:

=ArrayFormula(REGEXREPLACE(TRANSPOSE(SPLIT(CONCATENATE(REPT('Table Chart'!A2:A&CHAR(9)&'Table Chart'!B1:G1&CHAR(9)&TEXT('Table Chart'!B2:G,"d-MMM-yyyy")&CHAR(10),LEN('Table Chart'!B2:G)>0)),CHAR(10))),"^(.+?)\t(.+?)\t(.+)$",{"$1","$2","$3"}))

Google Apps Script will give you a "run once" solution which will be better (IMO) than a formula that is recalculating all the time, although the above formulae could be invoked and then just converted to values only. However, you might need them to update dynamically.

If you wanted a GAS solution, maybe add the google-apps-script tag in to invite other answers.

1
votes

delete range A2:C and paste this formula into A2 cell:

=ARRAYFORMULA(SORT(SPLIT(TRANSPOSE(SPLIT(TRIM(QUERY(TRANSPOSE(QUERY(TRANSPOSE(
 IF('Basic Matrix'!B2:H<>"", "♀"&
 SUBSTITUTE('Basic Matrix'!A2:A, "Indian Randonneurs", "IR")&"♂"&
 SUBSTITUTE('Basic Matrix'!B1:H1, "IR-", )&"♂"&'Basic Matrix'!B2:H, ))
 ,,999^99)),,999^99)), "♀")), "♂"), 3, 1))

0

(note: format B:C internally via 123 button as needed)