• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Please Help with transpose ...

V

VilasG

New Member
Dear All,
I have a spreadsheet having variable range ( say 307 rows and 201 columns) and would like to transpose the rows to col but also insert rows. Tried explaining the problem in an uploaded sample example file having two sheets .. ORIG and Results.

Your help is appreciated.

Warm Regards,
VG.
 

Attachments

  • Sample_v1.xlsx
    10.4 KB · Views: 5
Dear Bosco_vip,

Thanks for your prompt reply..much appreciated. I will try it out on my main spreadsheet and let you know I get on.

I was wondering if this can be converted into a VBA code and count last row and last col instead of hardcoding it?
Looking forward to hearing from you or another members.
Thanks & Regards
Vilas. :)
 
VilasG
Something like this... after press [DO IT]
PS. There are differences between 'RESULT' and 'RESULT-org' (= Yours) ?
 

Attachments

  • Sample_v1.xlsb
    21.8 KB · Views: 4
Dear vletm,

looks good but how can copy button and code behind the button to main excel spreadsheet?

Thanks & Regards,
vilas.
 
VilasG ... how to copy ... hmm?
1) mouse over button
2) right click
3) assign marco...
4) edit
5) you'll see code
6) ctrl+a ctrl+c
7) move wanted code page
8) ctrl+v
9) make own button as needed
 
Dear Vletm,

Thanks for your prompt response ...much appreciated. I very new to VBA hence asking all those silly question.

Warm Regards
VG.:):DD:awesome:
 
Dear Vletm,

One more question ... where can get information about what your code does? so that I can understand bit better.

Thanks.
 
VilasG ... as You wrote ..
You would like to transpose the rows to col but also insert rows...
where information ...
from code, other words, Excel'll do it like below:
disables screenupdating
inits two sheet-values
clean old data away from 'result'
use 'orig' as base sheet
init y-coord values
starts 1st loop
begins to reads 'activity ID's one-by-one
inits 'origs' x-coord value
starts 2nd loop
fills 'activity ID', 'date' & 'value' to 'results'
adds 'results' y- & 'orig' x-coord values
checks if 'orig's x-coord place is empty (ends 2nd loop)
adds 'orig' y-cood value
checki if 'orig' 'activity ID's value is empty (ends 1st loop)
stop using 'orig' as base
sets 'result's a-column's used range format to text
selects 'result' to view
enables screenupdating
gives message that "done"
> Ideas ... questions?
 
Dear Vletm,

Thanks for your response ...much appreciated. Explanation given by you helped me understand the logic as well as it will help me in writing a better code.

My sincere thanks once again.
Warm Regards
VG
 
Dear Vletm,

Just looking ahead ... currently only one row and one col is being transposed was wondering whether same logic can be extended to two or three cols?
Thanks for your help ...

Warm Regards,
VG
 
Dear vletm,

I am attaching new file.
thanks & regards,
VG
 

Attachments

  • new_Sample_v2.xlsb
    18.2 KB · Views: 2
Dear Vletm,
sorry to disturb you ... I have another question ...
on one of the laptop having 12GB RAM and i5 cpu macro transposes 365X175 = approx. 52000 rows in a 1-2 minutes or so but on laptop having 2GB RAM and i5 cpu it takes for ever and in task manager/performance wise CPU is always 100% ..
My question is --> Is there any way to split the spread sheet say do 50 rows at a time?

Thanks & Regards,
VG
 
You must log in or register to reply here.
Back
Top