0

I have 2 sheets that looks like this:

Manager Bob Manager Sally Manager Samuel
Team A Team C Team G
Team B Team X
Team Y
Team Z

(Real data contains 25 columns and 40-50 rows)

I would like to "rotate" the data to look like this:

Team Name Manager Name
Team A Manager Bob
Team B Manager Bob
Team C Manager Sally
Team X Manager Sally
Team Y Manager Sally
Team Z Manager Sally
Team G Manager Samuel

Can you help on how to approach this?

Best Regards

1

2 Answers 2

1

The following will work if used on a different sheet to your source table (i.e. I am assuming that your source table is in tab 'Sheet1' and starts at A1):

=arrayformula(query(split(tocol(Sheet1!A2:ZZZ&"|"&Sheet1!A1:1),"|"),
"where Col2 is not null order by Col2 label Col1 'Team Name',Col2 'Manager Name'",0))

This should work for any number of managers or teams.

1
  • Worked perfectly, thank you! Commented Apr 12 at 8:12
2

Here's one way to unpivot the data with lambda functions:

=reduce({"Team","Manager"},A1:C1,lambda(a,l,vstack(a,map(tocol(filter(A2:C,
A1:C1=l),1),lambda(v,{v,l})))))

Many answers to unpivot questions use text string manipulation and split(), which is sometimes problematic because it can mistreat dates, Booleans and numbers in certain formats, as well as text strings that look like those types. For example, split() will coerce the text string 11-12 to the numeric date 12 November in the current year. The same thing happens with text strings like 1 2, 1 2 3 and 1111-2-3. Conversely, numbers and dates in custom number formats like ₿0.00045 and Feb 17 Friday 2023 get coerced into text strings.

Some existing unpivot answers also stack data by using query(), but that function only accepts one data type per column. If a column contains a mix of text and numbers, or the occasional date or Boolean, the majority type will rule, and other types will be returned as nulls.

The formula above avoids these pitfalls because it does not rely on text string manipulation. Here's a human-readable version that includes additional checks for duplicate or missing manager names and the like:

=let( 
  data, A1:C, 
  managers, chooserows(data, 1), 
  rest, chooserows(data, sequence(rows(data) - 1, 1, 2)), 
  reduce(hstack("Team", "Manager"), unique(tocol(managers, 1)), lambda(a, m, let( 
    teams, tocol(filter(rest, managers = m), 1), 
    vstack(a, map(teams, lambda(t, hstack(t, m)))) 
  ))) 
)

See let(), lambda(), map(), vstack(), hstack(), chooserows(), unique(), filter(), sequence(), reduce() and tocol().

1
  • (for clarity: I am not saying that The God of Biscuits's answer would have an issue. It works fine with the sample data provided by the OP, and successfully avoids most pitfalls associated with split() and query().) Commented Apr 12 at 9:34

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.