From Row wise to Column wise

[Bipin619]

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.

 

[NARAYANK991]

Hi ,

If possible , please upload a workbook with adequate data in it.

Narayan

 

[GraH - Guido]

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)

 

[GraH - Guido]

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.

 

[Bipin619]

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.

 

[GraH - Guido]

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.

 

[Bipin619]

GraH - Guido Sir,

PFA enclosed herewith.

I require Separate Column for each field.

Party name address Product name Quantity Amount

 

[bosco_yip]

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.

1] Here's is the formula with helper solution

2] See attached file

Regards
Bosco

 

[Bipin619]

Thank you so much sir.