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
-
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)Second, I added a [Row] helper column to Sheet 2 to dynamically populate Column 1 to 3 values.
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.😅
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.9K Get Help
- 429 Global Discussions
- 147 Industry Talk
- 487 Announcements
- 5.2K Ideas & Feature Requests
- 86 Brandfolder
- 151 Just for fun
- 74 Community Job Board
- 497 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives