• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

From Row wise to Column wise

Bipin619

Member
I have a data arranged in the manner mentioned below.

Main Category Row 1
Sub Category Row 2
Name of the product Row 3
.....
and so on

While Main category does not change, sub category and name of the product keep on changing. So I want to arrange the data in such a way that I get three column namely
Main Category Sub Category Name of the product

I don't want to waste my time in doing cut paste all the time.
Please help.
 

GraH - Guido

Well-Known Member
If data is like in your post, assuming there is no 2nd column "row 1" etc, then this could be a way to do it:
  1. convert range to table (I called it tData and the single column is called Data)
  2. =IFERROR(INDEX(tData[[Data]:[Data]],COLUMNS($C$2:C$2)+(ROWS($A$2:$A2)-1)*3,1),"")
  3. drag down an to the side (3 columns)
 

Attachments

GraH - Guido

Well-Known Member
Or alternative with Power query:
  1. convert range to table
  2. Upload table to PQ
  3. Add index column
  4. Add modulo column - "3"
  5. Pivot on modulo column, use "data" as values and choose do not aggregate
  6. fill up columns 3 and 4
  7. filter "not null" in column 2
  8. delete index column
  9. load table to sheet
Click refresh when new data has arrived in the new table.
 

Attachments

Bipin619

Member
Thank you experts for your interest in my query. But can I get the value against the name of the product? PFA format of file.
 

Attachments

GraH - Guido

Well-Known Member
I do fail to see where in your example you have transposed the data as requested in post #1.
Can you give a manual example on how the result should be like.
 
Top