Google Sheets Reverse Transpose

1
votes

I saw this solution from another user and it was answered (by Opal). I am trying to modify the results a bit it but am getting stuck with blank values. I have this table of raw data (as an example)

idn | id name | d1 | d2 | d3 ...
id1 | /name1/ | 23 | 13 | 56
id2 | /name2/ | 67 | 45 |
id3 | /name3/ | 43 |
id4 | /name4/ | 78 | 34 | 45
. .

I want to transpose this list to the following form for uploading to a database, the results should look like:

id1 | /name1/ | date1 | 23
id1 | /name1/ | date2 | 13
id1 | /name1/ | date3 | 56
id2 | /name2/ | date1 | 67
id2 | /name2/ | date2 | 45
id3 | /name3/ | date1 | 43
id4 | /name4/ | date1 | 78
id4 | /name4/ | date2 | 34
id4 | /name4/ | date3 | 45
.
.

How can I do this - my current solution is as follows but does not work with blank spaces. I saw Opal solution which worked with blank spaces, but could not figure out how to create the date column (column 3 in the results)

ArrayFormula({transpose(split(query(rept(A2:A&" ", COUNTA(C1:1)),,50000)," ")),transpose(split(query(rept(B2:B&" ", COUNTA(C1:1)),,50000)," ")),TRANSPOSE(SPLIT(JOIN(" ", ARRAYFORMULA(REPT(join(" ",(query(C1:1)))&" ", COUNTA(A2:A)))), " ")),transpose(split(join(" ",query(transpose(C2:M),,50000)), " "))})

Any suggestions? It is a large data set, and I am hitting the limits on all of the string functions, rept limit of 32,000 and the join limit of 50,000 Thanks.

1
google-sheets
Could you please add a link to the "Opal solution" to which you refer? – Cindy Meister
stackoverflow.com/questions/30354693/… – user5853237
The other problem I have run into is the limit of 50,000 characters in the join function. – user5853237
It is a large data set, and I am hitting the limits on all of the string functions, rept limit of 32,000 and the join limit of 50,000. – user5853237
You can click the "Edit" link below your question and add the additional information directly to the question. That way people who read it will see all the information immediately. – Cindy Meister

1 Answers

1
votes

That is quite a nice formula. I am sure you are aware that if the blanks are 0 then the formula works. I know this is not a perfect solution, but it works. In another sheet use:

=ArrayFormula({transpose(split(query(rept(Sheet1!A2:A&" ", COUNTA(Sheet1!C1:1)),,50000)," ")),transpose(split(query(rept(Sheet1!B2:B&" ", COUNTA(Sheet1!C1:1)),,50000)," ")),TRANSPOSE(SPLIT(JOIN(" ", ARRAYFORMULA(REPT(join(" ",(query(Sheet1!C1:1)))&" ", COUNTA(Sheet1!A2:A)))), " ")),transpose(split(join(" ",query(transpose(Sheet1!C2:E5+0),,50000)), " "))})

The C2:E5+0 replaces the blanks with 0 for the purposes of the formula. It does not effect the original data. This returns the data formatted correctly, but with the unwanted 0 rows. On another sheet, clean up the unwanted rows with:

=query(Sheet2!A:D,"select * where D != 0")

This returns the data you want in the correct format.