Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Jul 2006
    Location
    Bangalore, India
    Posts
    180
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Transpose (Excel 2003)

    Hi,

    I have data of around 2500 employee with their salary break-up. For each employee salary data is in one row. Now i am faced with a challenge and the data for each employee salary break-up has to be in one column. I am not sure how i would be able to do this given the fact that it is 2500 rows that needs to be transposed.

    Is there a way to do it, either by formula or by macro. I have attached an excel with the way the CURRENT DATA is and how the DESIRED DATA should look like.

    Can someone help me with this.

    Thanks
    Baiju
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Transpose (Excel 2003)

    I deleted row 1 in the sample sheet, and set up the following formulas in Sheet2:

    A1: =OFFSET(Sheet1!$A$1,0,MOD(ROW()-1,6)+1)
    B1: =OFFSET(Sheet1!$A$1,(ROW()-1)/6+1,0)=
    C1: =OFFSET(Sheet1!$A$1,(ROW()-1)/6+1,MOD(ROW()-1,6)+1)

    I then filled down as far as needed. If you wish, you can use Copy and Edit | Paste Special with the Values option to remove the formulas.
    Next, I used the Format Painter button to copy the formatting of the desired data to the new data in Sheet2.

    See attached version.
    Attached Files Attached Files

  3. #3
    2 Star Lounger
    Join Date
    Jul 2006
    Location
    Bangalore, India
    Posts
    180
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Transpose (Excel 2003)

    Hi Hans,

    Thanks for the formula.. This is exactly what i was looking for.. You have made my job easy now.

    Regards
    Baiju

  4. #4
    2 Star Lounger
    Join Date
    Jul 2006
    Location
    Bangalore, India
    Posts
    180
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Transpose (Excel 2003)

    Hi Hans,

    I am faced with the new problem, The template that i have received is slightly different from the solution I asked earlier. Can you help me tweek the formula in the attached sheet to get the desired outcome.

    Thanks
    Baiju
    Attached Files Attached Files

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Transpose (Excel 2003)

    See the attached version. Please study the formulas and compare them to the formulas in the previous example, so that you can modify them yourself in the future.

  6. #6
    2 Star Lounger
    Join Date
    Jul 2006
    Location
    Bangalore, India
    Posts
    180
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Transpose (Excel 2003)

    HI Hans,

    The attachment is missing, can you please post the attachment.

    Thanks
    Baiju

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Transpose (Excel 2003)

    Sorry about that, I must have clicked Post It too soon. Here it is.
    Attached Files Attached Files

  8. #8
    2 Star Lounger
    Join Date
    Jul 2006
    Location
    Bangalore, India
    Posts
    180
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Transpose (Excel 2003)

    Thanks Hans,

    The formula works perfect. I shall tweek it if there is any further changes happening to my template

    Regards
    Baiju

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •