1

I feel almost certain this has been answered, but in a time crunch I am hoping someone can outline the solution for me, as my searches returned variations that were not quite what I wanted.

I have data that is arranged with multiple columns per entry:

unique number with multiple columns of data

I would like to transpose the columns data into rows duplicating the first two columns as indices:

multiple rows per unique number with each columns data transposed.

Much appreciation for any solutions.

1

2 Answers 2

0

As you have noted, there is a precedent for this on either StackOverflow or webapps but I couldn't find it, so I've recreated an equivalent solution. I'm pretty sure that this formula can be simplified BUT it does work - so simplification is for another day/user.

Use this formula (assumes that the raw data is in sheet="Data"):

=query({{"Number","Name","Code"};sort(query({query({query({query({query(Data!A2:B6) ,query(Data!A2:F6) },"select Col1, Col2, Col5 where Col1 = Col3");query({query(Data!A2:B6) ,query(Data!A2:F6) },"select Col1, Col2, Col6 where Col1 = Col3 and Col6 is not null")});query({query(Data!A2:B6) ,query(Data!A2:F6) },"select Col1, Col2, Col7 where Col1 = Col3 and Col7 is not null")});query({query(Data!A2:B6) ,query(Data!A2:F6) },"select Col1, Col2, Col8 where Col1 = Col3 and Col8 is not null")}),2,true)})

The logic is

  • create four(4) x two-array queries: the first part is Columns A & B (Col1 and Col2), the second part is Columns A to F (Col3 to Col8)
    • each query lists Col1 and Col2 and one of Col5 to Col 8 respectively.
    • these quesries are then stacked. Note that are joined between curly brackets and separated by a semicolon {;}
  • the end result is then sorted using SORT
  • the last step is to add the column headings (though this is optional) by creating another two-array query

BEFORE

Before

AFTER

After

1
  • My actual data set was a bit bulkier, so this did become a bit expansive with all the copy pasting of the queries. I would still be interested if anyone comes up with a more streamlined approach. This did teach me some new techniques. Sep 8, 2020 at 12:24
0

Another option:

=ArrayFormula({"Number","Name","Code";SPLIT(QUERY(FLATTEN(IF(FILTER(C2:F,A2:A<>"")="","",FILTER(A2:A&"\"&B2:B,A2:A<>"")&"\"&FILTER(C2:F,A2:A<>""))),"Select * Where Col1 Is Not Null"),"\")})

The first part of the inner virtual array (before the semicolon) creates the headers.

You'll notice that all ranges in the formula have FILTER applied. This is so that we can only deal with rows that have numbers assigned in Column A.

Jumping to the IF clause, if anything in the C:F range is null, we want it left null (i.e., we don't want any concatenation to happen). If not, we want to concatenate the Column A data, a backslash, the Column B data, another backslash, and every the data of every separate non-null cell in the C2:F range. So the first entry, given your sample data, would look like this in virtual space: 123\abc\9081

FLATTEN will take all of these strings and form one column from them (which will include all the nulls at this point). NOTE: FLATTEN is as yet an undocumented Google Sheets function.

QUERY condenses the FLATTENed list, squashing out the nulls.

Finally, SPLIT simply splits all the entries at the backslash, forming the three columns.

This is what I came up with on the spot here. As you say, there may be other methods elsewhere online.

2
  • This worked really well, and I learned some new techniques. I accepted the other solution because I used it first. Thank you! Sep 8, 2020 at 12:22
  • Glad you got practical use and knowledge out of it, Zachary. The most important things are that A.) you get the help you came for and B.) you mark something as "Best Answer" if it works, because this signals to other contributors here than an issue is resolved without our having to leave the master listing.
    – Erik Tyler
    Sep 8, 2020 at 15:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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