Transpose function help required

[Santoo]

hi,

i have attached an excel file where i want to do transpose, rows to columns, can any one help on this.

Excel file is self explanatory.

Thanks in advance

 

[PCosta87]

Hi @Santoo

Welcome to the forum

Doesn't the copy/paste special(transpose) work?
Copy desired range and then paste special (select transpose) and it should work

 

[Santoo]

hi @Pcosta,

thank for you reply

I tried it, it works, but the problem is one set of database has 57 rows. Like wise i have 100 set.

More over these 100 sets i uses to get daily.

Transpose 100 set takes lots of time. So i was searching for better option.

 

[PCosta87]

I see

You can try disabling screen updating... in my tests:

with screen updating on = 0.03 seconds
with screen updating off = 0.01 seconds

Add this code to a new module and run first macro to disable... when you are done transposing, "re"enable it by running second macro:

Sub DisableScreenUpdating()

Application.ScreenUpdating = False

End Sub

Sub EnableScreenUpdating()

Application.ScreenUpdating = True

End Sub

Let me know how it went

 

[Santoo]

I see

You can try disabling screen updating... in my tests:

with screen updating on = 0.03 seconds
with screen updating off = 0.01 seconds

Add this code to a new module and run first macro to disable... when you are done transposing, "re"enable it by running second macro:

Sub DisableScreenUpdating()

Application.ScreenUpdating = False

End Sub

Sub EnableScreenUpdating()

Application.ScreenUpdating = True

End Sub

Let me know how it went

hi,

i tried out, it transpose quickly, but my purpose is not solved.

You forget row A, let us take Row B, first set of 57 rows said to be 1 database, second set 57 rows said to be 2 coloumn database, like wise it has to go on. this word Transpose i have used is to just converting from row to col. In other it can be considered as append 57 rows as one clm, second next,.... like wise. till 100 set database.

I tried with vlookup, index match, etc, but the problem 2nd column is not getting updated, due to duplicate values. more over i dont know much about excel formulas or vba, a medium range knowledge it.

can you try any other option.

 

[PCosta87]

@Santoo

I'm not sure I follow but if you want some formula to transpose you can try the one in the attachment.
With it, I used Indirect to be able to specify the range I want to transpose (instead of changing the formula)... it can be used with any range.

I used several columns but you could use rows 1:50, 51:100 and so on

Does this help?

 

[Santoo]

@Santoo

I'm not sure I follow but if you want some formula to transpose you can try the one in the attachment.
With it, I used Indirect to be able to specify the range I want to transpose (instead of changing the formula)... it can be used with any range.

I used several columns but you could use rows 1:50, 51:100 and so on

Does this help?

@PCosta87
Thank you very much it has saved my purpose to maximum extent.

Thank once again for time spent for my thread.

 

[PCosta87]

Glad I could help