1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

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

Discussion in 'Ask an Excel Question' started by Bipin619, Mar 12, 2018.

  1. Bipin619

    Bipin619 New Member

    Messages:
    21
    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.
  2. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,606
    Hi ,

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

    Narayan
  3. GraH - Guido

    GraH - Guido Active Member

    Messages:
    671
    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)

    Attached Files:

    Bipin619 likes this.
  4. GraH - Guido

    GraH - Guido Active Member

    Messages:
    671
    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.

    Attached Files:

    Bipin619 likes this.
  5. Bipin619

    Bipin619 New Member

    Messages:
    21
    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.

    Attached Files:

  6. GraH - Guido

    GraH - Guido Active Member

    Messages:
    671
    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.
  7. Bipin619

    Bipin619 New Member

    Messages:
    21
    GraH - Guido Sir,

    PFA enclosed herewith.

    I require Separate Column for each field.

    Party name address Product name Quantity Amount

    Attached Files:

  8. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    1,716
    1] Here's is the formula with helper solution

    2] See attached file

    Regards
    Bosco

    Attached Files:

  9. Bipin619

    Bipin619 New Member

    Messages:
    21
    Thank you so much sir.:)

Share This Page