0

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?

player0
  • 124,011
  • 12
  • 67
  • 124
RudyF
  • 805
  • 1
  • 10
  • 16
  • 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 Oct 22 '14 at 23:48
  • I'd love to get my hands dirty with GAS. And, sure, migration to the newest version is certainly viable. – RudyF Oct 24 '14 at 00:30

2 Answers2

2

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.

AdamL
  • 23,691
  • 6
  • 68
  • 59
  • Many thanks. The "previous version" solution does work, but I've just realized that I failed to mentioned that I was looking to sort on the date column. I should be able to tweak the "latest version" solution to do that, so it's just a matter now of converting this spreadsheet ... which I think I can do. – RudyF Oct 24 '14 at 08:31
  • Yes, you can just add a "order by Col2" into the QUERY clause. – AdamL Oct 24 '14 at 10:52
  • This works really well, but has a few downsides that one needs to be aware of. The sheet 'Table Chart' has to have a lot of empty rows for it to work, it seems to require the total number of rows in the matrix sheet (including empty ones) to be at least (numCols * numRows) in the Matrix. Or in other words, if you have a 50x50 matrix when you need to add at least 2450 empty rows after your last row in the matrix sheet. – yzfr1 Jun 23 '19 at 17:22
1

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)

player0
  • 124,011
  • 12
  • 67
  • 124