Please help with transpose ..

  • I have a spreadsheet having following details ..



    [TABLE="width: 290"]

    [tr]


    [td]

    Department

    [/td]


    [TD="align: right"]27-Mar-17
    [/TD]
    [TD="align: right"] 03-Apr-17
    [/TD]
    [TD="align: right"] 10-Apr-17
    [/TD]

    [/tr]


    [tr]


    [td]

    1111-11-0001

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    1111-15-0120

    [/td]


    [TD="align: right"]37
    [/TD]

    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    1111-15-0140

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    1111-15-0320

    [/td]


    [td][/td]


    [TD="align: right"]12[/TD]

    [td][/td]


    [/tr]


    [tr]


    [td]

    1111-15-0340

    [/td]


    [TD="align: right"]23.2
    [/TD]
    [TD="align: right"]48.5[/TD]
    [TD="align: right"]82.8[/TD]

    [/tr]


    [/TABLE]


    how to transpose rows & columns to get following results?

    [TABLE="width: 223"]

    [tr]


    [td]

    1111-11-0001

    [/td]


    [TD="align: right"] 27-Mar-17
    [/TD]

    [td][/td]


    [/tr]


    [tr]


    [td]

    1111-11-0001

    [/td]


    [TD="align: right"]03-Apr-17
    [/TD]

    [td][/td]


    [/tr]


    [tr]


    [td]

    1111-11-0001

    [/td]


    [TD="align: right"]10-Apr-17
    [/TD]

    [td][/td]


    [/tr]


    [tr]


    [td]

    1111-15-0120

    [/td]


    [TD="align: right"]27-Mar-17
    [/TD]
    [TD="align: right"]37[/TD]

    [/tr]


    [tr]


    [td]

    1111-15-0120

    [/td]


    [TD="align: right"]03-Apr-17
    [/TD]

    [td][/td]


    [/tr]


    [tr]


    [td]

    1111-15-0120

    [/td]


    [TD="align: right"]10-Apr-17
    [/TD]

    [td][/td]


    [/tr]


    [tr]


    [td]

    1111-15-0140

    [/td]


    [TD="align: right"]27-Mar-17
    [/TD]

    [td][/td]


    [/tr]


    [tr]


    [td]

    1111-15-0140

    [/td]


    [TD="align: right"]03-Apr-17
    [/TD]

    [td][/td]


    [/tr]


    [tr]


    [td]

    1111-15-0140

    [/td]


    [TD="align: right"]10-Apr-17
    [/TD]

    [td][/td]


    [/tr]


    [tr]


    [td]

    1111-15-0320

    [/td]


    [TD="align: right"]27-Mar-17
    [/TD]

    [td][/td]


    [/tr]


    [tr]


    [td]

    1111-15-0320

    [/td]


    [TD="align: right"]03-Apr-17
    [/TD]
    [TD="align: right"]12[/TD]

    [/tr]


    [tr]


    [td]

    1111-15-0320

    [/td]


    [TD="align: right"]10-Apr-17[/TD]

    [td][/td]


    [/tr]


    [tr]


    [td]

    1111-15-0340

    [/td]


    [TD="align: right"]27-Mar-17[/TD]
    [TD="align: right"]23.2[/TD]

    [/tr]


    [tr]


    [td]

    1111-15-0340

    [/td]


    [TD="align: right"]03-Apr-17[/TD]
    [TD="align: right"]48.5[/TD]

    [/tr]


    [tr]


    [td]

    1111-15-0340

    [/td]


    [TD="align: right"]10-Apr-17[/TD]
    [TD="align: right"]82.8[/TD]

    [/tr]


    [/TABLE]


    Thanks for your help.


    Warm Regards,

  • Re: Please help with transpose ..


    Hello,


    Would you mind attaching a sample workbook ... for tests purposes ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Re: Please help with transpose ..


    Hello,


    Attached is your workbook ... with a proposal .. to go from Input ... to the Output ...


    HTH

  • Re: Please help with transpose ..


    Hi Carim,
    That is what I was looking for but .. I couldn't find vba code or steps to achieve the same? would you please give me the details?


    Thanks
    Jr.

  • Re: Please help with transpose ..


    Hello,


    There is no VBA code ...


    To go from Input to Output ... the second row in Output sheet uses the Offset() function which, once copied down ... produces your result ...


    Hope this clarifies

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Re: Please help with transpose ..


    Hello,
    My sheet has approx. 988 rows and 200 columns , how to copy this formula ? please help.
    Thanks.

  • Re: Please help with transpose ..


    Hello,


    Your sample input had 3 Columns ... which led the Offset() function to use 3 as argument ...


    Whatever your actual number of Columns, just replace 3 by your actual number in all three formulas ... and copy down ...


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Re: Please help with transpose ..



    Ok thanks for your help ... I will try it out.

  • Re: Please help with transpose ..


    Hello Alan,


    I am afraid it don't work .... many columns having no value just didn't get transposed but some columns got copied but 2nd column but not the 1st one?


    Thanks & Regards,
    Jr.

  • Re: Please help with transpose ..


    You are welcome ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Re: Please help with transpose ..


    Hello Carim,
    tried offset formula but couldn't understand how it works. I would appreciate it if you could give me description of each three commands?
    My worksheet has 800 rows ( column A) and approx. 200 columns. How can I copy formula ( may be using micro or vba code?)


    your original code : =


    =OFFSET(Input!$A$2,INT((ROW()-2)/3),0)
    =OFFSET(Input!$B$1,0,MOD(ROW()-2,3))
    =OFFSET(Input!$B$2,INT((ROW()-2)/3),MOD(ROW()-2,3))
    Changed code


    =OFFSET(Input!$A$2,INT((ROW()-2)/200),0)
    =OFFSET(Input!$B$1,0,MOD(ROW()-2,200))
    =OFFSET(Input!$B$2,INT((ROW()-2)/200),MOD(ROW()-2,200))


    Please help.
    Thanks & Regards,
    Jr.

  • Re: Please help with transpose ..


    Quote

    My worksheet has 800 rows ( column A) and approx. 200 columns ...


    Hello,


    When using the Offset() function ... there is no room for an approximate number of columns ...


    To be on the safe side, use the Total Number of Columns - 1 ...


    HTH

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Re: Please help with transpose ..


    Hello,


    Ideally, using the Offset() function is fine for small spreadsheets ...and for a very large spreadsheet, a macro would be more logical ...to avoid the re-calculation burden ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!