Google Sheets - Transpose & Query

2
votes

We have a relatively large data set, with roughly 26K rows and 24 columns in the "current format" below. However we are trying to reformat it to the desire format below. I think we may need but Transpose and Query to achieve thus but we are open to any method of reaching it.

Any suggestions on how to reformat this would be greatly appreciated. Thank you in advance.

sample

4
google-sheetsgoogle-sheets-formulatransposearray-formulasgoogle-sheets-query

4 Answers

1
votes

use:

=ARRAYFORMULA({"ID", "Value", "Qty"; 
 IFERROR(SPLIT(FLATTEN(IF(B3:D="",,A3:A&"♦"&B2:D2&"♦"&B3:D)), "♦"))})

0

2
votes

JAK, that is a lot of data to process. And it is always difficult to try to write formulas without access to the actual sheet and data. That said, SPLIT/JOIN combos would exceed character limits with that much data. But in theory, the following formula would work (assuming your actual data is an extension of your example and runs from A1:Y.

=ArrayFormula({"ID","Value","Qty"; QUERY({VLOOKUP(MOD(SEQUENCE(COUNTA(A:A)*COUNTA(A1:Y1),1)-1,COUNTA(A:A))+2,{ROW(A:A),A:A},2),HLOOKUP(ROUNDUP(SEQUENCE(COUNTA(A:A)*COUNTA(A1:Y1),1)/COUNTA(A:A))+1,{COLUMN(A1:Y1);A1:Y1},2),
VLOOKUP(MOD(SEQUENCE(COUNTA(A:A)*COUNTA(A1:Y1),1,2)-2,COUNTA(A:A))+2,{ROW(A2:A),B2:Y},ROUNDUP(SEQUENCE(COUNTA(A:A)*COUNTA(A1:Y1),1)/COUNTA(A:A))+1)
},"SELECT Col1, Col2, Col3 WHERE Col3 IS NOT NULL ORDER BY Col1 Asc")})

If you're wanting to set this up in a separate sheet, you'll need to edit every range throughout the formula to include the name of the sheet where the original data resides.

For more on the basis and explanation for this formula: https://www.benlcollins.com/spreadsheets/unpivot-in-google-sheets

0
votes

You could try to download the sheet in .csv and make a program to read the data and do the necessary changes, not sure if you're familiar with programming... There are several languages that could be used to achieve that, i have already done something like that in C. Its not very hard. You could open the file, read the data, process the changes and save in another .csv. Just remember that csv files are just a .txt file separated with commas.

int main(int argc, char** argv) {

FILE *file;

FILE *file2;

char Linha[100];
char *result;
int i;


file = fopen("YourSheet.csv", "rt");
file2 = fopen("YourNewSheet.csv", "w");

i = 1;
while (!feof(file))


{
// reads a line
  result = fgets(Linha, 100, file); 
  if (result)
  fprintf(file2, "%s", result);
  i++;


}




fclose(file);
  fclose(file2);

    return 0;
}
0
votes

Well it can be so simple, and we can use google query SQL features too:

Excel Table:

     A       B
1    Qty   | 200
2    Stock | QUESS
3    Start | 8/24/2019
4    End   | 8/23/2020
5    Today | 8/23/2021

Formula:

=query(Transpose(Sheet6!A1:B5),"select *" )

Output :

Qty | Stock | Start     | End       | Today
200 | QUESS | 8/24/2019 | 8/23/2020 | 8/23/2021