Forum Discussion

tw1211's avatar
tw1211
Copper Contributor
Sep 27, 2023

Transposing data related to same ID in row

Hello,

I have a table where column A is for ID, column B is the date for measurements in column C.

I would like to transpose rows having the same ID to columns where column A would be the ID, B the date for the first measurement in C, D the date for second measurement in E, etc...
One ID can have between 1 to 11 different measurements.


Could someone help me with this problem?

I have 2300 rows to look through and I would love to find a quick solution. 

Thank you in advance.

Here's an example of how it is now and how I want it to look like:

 

Which I would like to transform like this:

(The variables and the numbers are not the ones I look at.)

 

    • tw1211's avatar
      tw1211
      Copper Contributor

      OliverScheurichpeiyezhuPatrick2788, thank you for your input, it worked!

      I want to now copy/paste my new table in a new worksheet but Excel is displaying an error message since this second table is dependent on the first one.
      How do I "break" this dependence to make it possible to create a new worksheet with this new data presentation without the first table?

  • Patrick2788's avatar
    Patrick2788
    Silver Contributor

    tw1211 

    A 365 solution.

     

    Reduce, the new workhorse!

     

    =LET(
        uID, SORT(UNIQUE(Table1[ID])),
        Pivot, LAMBDA(a, v,
            LET(
                scores, TOROW(FILTER(Table1[[nscore_date]:[nscore]], Table1[ID] = v)),
                IFERROR(VSTACK(a, HSTACK(v, scores)), "")
            )
        ),
        DROP(REDUCE("", uID, Pivot), 1)
    )

     

    • PeterBartholomew1's avatar
      PeterBartholomew1
      Silver Contributor

      Patrick2788 

      Reduce, the new workhorse!

       

      Yes, but ...

      Although REDUCE does not suffer from the 'array of arrays' problem that cripples SCAN, MAP, BYROW, BYCOL, it does have performance limitations when used with VSTACK to gather prior results.  I have recently found that recursive bisection performs better, despite being even more convoluted in terms of its logic than usual!

      Worksheet formula
      = LET(
          uID, SORT(UNIQUE(Table1[ID])),
          HSTACK(uID, BMAPλ(uID, Pivotλ))
        )

      where the Lambda helper function BMAPλ is given by

      BMAPλ
      =LET(
          n, ROWS(X),
          Y, IF(
              n > 1,
              LET(
                  ℓ, n - QUOTIENT(n, 2),
                  X₁, TAKE(X, ℓ),
                  X₂, DROP(X, ℓ),
                  Y₁, BMAPλ(X₁, Fnλ),
                  Y₂, BMAPλ(X₂, Fnλ),
                  IFERROR(VSTACK(Y₁, Y₂), "")
              ),
              Fnλ(X)
          ),
          Y
      )

      and

      Pivotλ
      = TOROW(FILTER(Table1[[nscore_date]:[nscore]], Table1[ID] = v))

      What it does is bisect the list of IDs until only one is left and returns the result associated with that ID.  It then evaluates the result from the other ID of the final pair and stacks the result.  It then repeats with the next pair of IDs and so on back up the calling tree.

       

      Once one is into 1000s of rows the performance differences can be large.  For example, I had to rework one solution because REDUCE was taking over 3½ minutes to evaluate.  With BMAPλ this reduced to <1sec.

       

       

       

      • Patrick2788's avatar
        Patrick2788
        Silver Contributor

        PeterBartholomew1 

        This is certainly a creative workaround! I can see where calculation speed is much better than Reduce/VSTACK.  If I'm reading this correctly, you're significantly reducing the amount of times VSTACK is being employed by bisecting the data.

         

        If there are 4 unique IDs like in the example provided, REDUCE/VSTACK is run 4x.

        With your workaround, VSTACK is run 2 times?

  • tw1211 

    =HSTACK(UNIQUE(Tabelle7[ID]),IFERROR(DROP(
    REDUCE("",SEQUENCE(ROWS(UNIQUE(Tabelle7[ID]))),
    LAMBDA(x,y,VSTACK(x,TOROW(FILTER(Tabelle7[[nscore]:[score]],Tabelle7[ID]=
    INDEX(UNIQUE(Tabelle7[ID]),y)))))),1),""))

     

    This returns the intended output in my Excel for the web sheet. The name of the table in this example is "Tabelle7". You can replace this name with the name of your table.

     

    • accountantbot's avatar
      accountantbot
      Copper Contributor

      Is there a way to add more columns? I'm not that good at excel but I basically have data with multiple ids and different entries. I'm unsure how to account for the extra columns of data. 

      • OliverScheurich's avatar
        OliverScheurich
        Gold Contributor

        Does this return the expected result?

        =HSTACK(UNIQUE(Tabelle7[ID]),IFERROR(DROP(
        REDUCE("",SEQUENCE(ROWS(UNIQUE(Tabelle7[ID]))),
        LAMBDA(x,y,VSTACK(x,TOROW(FILTER(Tabelle7[[nscore]:[time]],Tabelle7[ID]=
        INDEX(UNIQUE(Tabelle7[ID]),y)))))),1),""))

         

        The only change (in row 3 of the formula) is this

        Tabelle7[[nscore]:[time]]

        instead of that

        Tabelle7[[nscore]:[score]]  .

Resources