-5

i want to put all column for 1 row into row in xls

input: enter image description here

Output:

enter image description here

Gourav Nagar
  • 1
  • 3

2 Answers2

0

Not the cleanest way but it does the job.

enter image description here

Formula in column I:

=INDEX($A$1:$A$4;ROUNDUP(ROW()/5;0))

Notice the division between row number and 5. The divisor is 5 because you got 5 elements to get (c1,c2,c3,c4,c5). Change 5 to whatever columns you need to fecht.

Also, please notice you need to drag manually until you get the quantity of elements. In this case, your data is a table of 4x5 = 20 so you need to manually drag the formula from row 1 to row 20

Formula in column J:

=INDEX($B$1:$F$4;MATCH(I1;$A$1:$A$4;0);COUNTIF($I$1:I1;I1))
Foxfire And Burns And Burns
  • 10,665
  • 2
  • 17
  • 38
  • C1 to C5 is not fixed its variant for each table .......tbl1 c1 c2 c3 c4 c5 c6 c7 tbl2 c1 c2 c3 tbl3 c1 c2 c3 c4 c5 tbl4 c1 c2 Outputtbl1 c1 tbl1 c2 tbl1 c3 tbl1 c4 tbl1 c5 tbl1 c6 tbl1 c7 tbl2 c1 tbl2 c2 tbl2 c3 tbl3 c1 tbl3 c2 tbl3 c3 tbl3 c4 tbl3 c5 tbl4 c1 tbl4 c2 – Gourav Nagar May 30 '22 at 13:41
  • @GouravNagar Please, next time add a **real** data example so you can get the solution you need. Anyways, this answer will return 0/blanks even if the size is different. You just need to filter those rows and delete them. It takes literally seconds – Foxfire And Burns And Burns May 30 '22 at 23:35
0
  1. Select your data
  2. Data -> from Table/Range (unselect "My Data has headers")
    (=> Power Query Editor is started)
  3. Select Column 2 to Column 6 in Power Query Editor
  4. Transform -> Unpivot Columns
  5. Home -> Close and Load to ... (Existing Worksheet "H1")

DONE! ;-)

Screenshot step 2:
enter image description here

Screenshot step 3+4:
enter image description here

result:
enter image description here

simple-solution
  • 1,109
  • 1
  • 6
  • 13