0

I want to transpose column criteria B C D E and column A repeat in every criteria. I don't know the query. please help me. This the link google sheets sample i made, my original data was thousand. thank you

1wGH-MGpuDE_vwRVuiWuNywRMIvPdF7OaFeLxpgR5wK0

1

I placed the following formula into a new sheet ("Erik Help"), cell A9:

=ArrayFormula(SPLIT(FLATTEN(A2:A3&"\"&B1:E1&"\"&B2:E3),"\",1,0))

NOTE: FLATTEN is not yet an official Google Sheets function. I recommend you do the following, once the formula creates the new layout:

1.) Select the entire new data range created by the formula and hit Ctrl-C to copy it to clipboard.

2.) With the range still selected, hit Ctrl-Alt-V ("Paste Special").

3.) A tiny clipboard icon will appear screen lower right. Click it and choose "Paste values only."

This will convert the formula-generated data to an in-place hard copy of the data.

11
  • Thank you very much for helping, that's mean a lots for me. GBU Aug 22 '20 at 2:03
  • Great! If this addresses the issue in your post to your satisfaction, please take a moment to mark the answer as "Best Answer." It signals to others that this issue is resolved, and it will help future information seekers such as yourself to find answers to their own questions more quickly.
    – Erik Tyler
    Aug 22 '20 at 2:28
  • Hi, if there any way to show only the criteria with value? Aug 22 '20 at 5:59
  • I don't understand your question. What do you mean by "criteria with value"? Please show on your sheet the desired result and leave notification here when you have done that.
    – Erik Tyler
    Aug 22 '20 at 10:01
  • Hi, i have update the sheets, i want the blank cell doesn't appear. Can we use if it's not null "" it will appear, something like that. Aug 23 '20 at 0:47
0

See your file sheet "test2" cell G1.

Formula consists of 3 parts for each column.

ARRAYFORMULA(TRANSPOSE(SPLIT(JOIN(",",REPT(A2:A&",", COUNTA($B$1:$E$1))), ",")))

These part of formula repeat values from column A N times, where N is number of columns with data in your initial table (3 in example), thus you can adjust it to table size changing this part COUNTA($B$1:$E$1).

ARRAYFORMULA(TRANSPOSE(SPLIT(REPT(JOIN(",",$B$1:$E$1&","),COUNTA(A2:A)),",")))

this formula repeats headers of initial table N times, where N is number of rows in your data. Also can be adjusted for different tables by changing $B$1:$E$1 - headers range.

ARRAYFORMULA(TRANSPOSE(SPLIT(CONCATENATE(B2:E&","),",")))

Last part transforms values from table into single column. Also can be adjusted for different tables by changing B2:E.

1
  • Thank you very much for helping, that's mean a lots for me. GBU Aug 22 '20 at 2:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.