Is it possible to display row data into column (not exactly transpose)

One smartsheet had data in the following format:

Column1 Column2 Column3 Column4
Row1 A1 B1 D1
Row2 A2 C2
Row3 A3 C3 D3

I want above displayed in another smart sheet in the following way:

Column1 Column2
Row1 A1 B1
Row2 A1 D1
Row3 A2 C2
Row4 A3 C3
Row5 A3 D3

If this possible in smartsheet, if yes how to do it?

Answers

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp Community Champion

    Hi @SivaM

    First, I added two helper columns, [Row] and [Count], to Sheet 1.

    [Row] =MATCH([Column1]@row, [Column1]:[Column1])
    [Count] =COUNTIFS([Column3]:[Column3], ISTEXT(@cell), Row:Row, <=Row@row) + COUNTIFS([Column4]:[Column4], ISTEXT(@cell), Row:Row, <=Row@row)

    https://app.smartsheet.com/b/publish?EQBCT=5847b5e83bdf4eefa1cb8e89971be8e7

    Second, I added a [Row] helper column to Sheet 2 to dynamically populate Column 1 to 3 values.

    https://app.smartsheet.com/b/publish?EQBCT=db04d9e08da640538ac48bf7d4430b51

    The first formula creates Row 1, Row 2, etc, up to the number of Column 3&4 nonblank values.

    [Column1] =IF(Row@row <= [Count Column 3&4 of Sheet 1]#, "Row " + Row@row)

    [Column2] =IF(Row@row <= [Count Column 3&4 of Sheet 1]#, INDEX({Sheet 1: Column2}, INDEX(COLLECT({Sheet 1: Row}, {Sheet 1: Count}, >=Row@row), 1)))
    [Column3] =IF(Row@row <= [Count Column 3&4 of Sheet 1]#, INDEX(DISTINCT({Sheet 1: Column3:Column4}), Row@row))

    The second formula uses the Count value we created in Sheet 1. It gets the Row value of Sheet 1, whose Count value is greater than Sheet 2's Row value. Then, use this value as the INDEX formula's Row Index.
    For example, the Count value in Sheet 1, in the first row with A1 is 2. So, for Rows 1 and 2, the formula returns the row index of 1, returning A1s to Column2.

    The third formula first creates distinct values of the Column3:Column4 range of Sheet 1 and gets the value up to the [Count Column 3&4 of Sheet 1]#, or 5, in this example.


    I wonder what you use it for.😅