1

I'm trying to figure out a way to do a combination of transposing rows into one long table, but with the row names of the long table concatenated with header and 1st row names. Basically trying to find a formula that will take this:

duration NJ NY NV
30daynew 3 5 10
30dayrepeat 35 55 5
30daysubscribe 50 90 9

And put it into this:

NJ-30daynew NJ-30daysrepeat NJ_30daysubscribe NY-10days NY-30daysrepeat NY_30daysubscribe NV-10days NV-30daysrepeat NV_30daysubscribe
3 35 50 5 55 90 10 5 9

Sample data available here

Polk011
  • 11
  • 1

2 Answers2

1

Transform Data: Transpose and Join

Efficient: INDEX/SEQUENCE

=LET(data,A1:E4,lDel,"-",
    rl,TOROW(DROP(TAKE(data,,1),1)),
    cl,DROP(TAKE(data,1),,1),
    v,TOROW(DROP(data,1,1),,1),
    rc,COLUMNS(rl),cc,COLUMNS(cl),c,rc*cc,
    rs,MOD((SEQUENCE(,c)-1),rc)+1,
    cs,INT((SEQUENCE(,c)-1)/cc)+1,
    l,INDEX(cl,,cs)&lDel&INDEX(rl,,rs),
VSTACK(l,v))

enter image description here

Easy: REDUCE/HSTACK

=LET(data,A1:E4,lDel,"-",
    rl,TOROW(DROP(TAKE(data,,1),1)),
    cl,DROP(TAKE(data,1),,1),
    v,TOROW(DROP(data,1,1),,1),
    l,DROP(REDUCE("",cl,LAMBDA(rr,c,
        HSTACK(rr,c&lDel&rl))),,1),
VSTACK(l,v))
VBasic2008
  • 44,888
  • 5
  • 17
  • 28
1

=VSTACK(TOROW(B1:D1&"-"&A2:A4),TOROW(B2:D4))

Or in the order as defined: =VSTACK(TOROW(B1:D1&"-"&A2:A4,,1),TOROW(B2:D4,,1))

If you combine (&) a row of values to a column of values, it iterates through the combinations.

Or more dynamic:

=LET(range,  A1:D4,
     a,      DROP(range,,1),
     x,      TAKE(a,1),
     data,   DROP(a,1),
     y,      DROP(TAKE(range,,1),1),
VSTACK(TOROW(x&"-"&y,,1),TOROW(data,,1)))

More advanced example of the same principle: https://stackoverflow.com/a/76360221/12634230

P.b
  • 8,293
  • 2
  • 10
  • 25