1. ## 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

2. ## 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.

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

5. ## 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. ## Re: Transpose (Excel 2003)

HI Hans,

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

Thanks
Baiju

7. ## Re: Transpose (Excel 2003)

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

8. ## 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
•