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

SivaM

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?

jmyzk_cloudsmart_jp

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.😅

jmyzk_cloudsmart_jp

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.😅

SivaM

jmyzk_cloudsmart_jp

Thanks for the response. Somehow reference between 2 sheets is not working using the above formula. Can you provide exact syntax you used.

SivaM

jmyzk_cloudsmart_jp, let me know if you have sometime early next week, we can connect to review this. Thanks

SivaM

Yes - I am available. Call me @ +1 208 340 6393