Macro to Transpose Data

[aks_npti]

Hello All,

I have data for around 1000 employees arranged horizontally which needs to be transposed vertically to update the database.

There are around 60 columns of data for each employee that need to be transposed. I am attaching a sample excel with this thread.

Any help will be highly appreciated!!!!

Regards,
AKS

 

[Hui]

Select B1:BL10
Ctrl+C
Goto E13

 

[aks_npti]

Hi Hui ,

Thanks for your reply however I think I didn't explained the scenario correctly.

Let me try again :

I have to transpose the WT and associated amount with the employee code. There are 1000+ employees data needs to be transposed. Copying each row and transposing will take lot of time. Hence I thought if there is any macor / formula which can come to rescue me.

Regards,
AKS

 

[bobhc]

As per @Hui has suggested you are not copying one row at a time but all in one copy/paste(transpose). B1 through to BL10

 

[aks_npti]

Thanks for the reply...

But this is not what my output looks like. I have updated the attached spreadsheet with output example. One column for emp. id , second column for wagetype & third column for the amount for the corresponding employee id & wagetype.

Sorry for confusing both of you!!!

Regards,
AKS

 

[Hui]

A13: =OFFSET($A$1,INT((ROW()-12.1)/(COUNTA($1:$1)-1))+1,)
B13: =OFFSET($A$1,,MOD(ROW()-13,(COUNTA($1:$1)-1))+1)
C13: =OFFSET($A$1,INT((ROW()-12.1)/(COUNTA($1:$1)-1))+1,MOD(ROW()-13,(COUNTA($1:$1)-1))+1)

Copy the 3 cells down as far as you need

or see attached file:

 

[aks_npti]

Thanks a lot Hui !!!

It worked like a charm.