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

To transpose or not to transpose ...

P

PipBoy808

Member
A sort of conceptual question for the sake of learning:

Lets say I have a list of data in A1:A20 in Sheet 1, and I want to import it into A1:T1 in Sheet 2. Both arrays are the same size, but one is vertical and the other is horizontal. Is it best to copy the first array and paste special / transpose the data into A1 into Sheet 2 or is there a more efficient method of achieving this?

Thanks!
 
Hi,
Copy paste+transpose is the effective way and fast as well.
However, if you wish that you data in Sheet2 to be updated as and when the data in sheet1 is changed so you can use the '=' formula (example: for linking Sheet1!A1 to sheet2!A1 use the linking as =Sheet1!A1) and likewise.

The second approach can be a bit time consuming when compared to the first one however its one time effort if your values in Sheet1 keep changing.

I am sure if there is any other way then other folks on this forum will surely let you know...:)
 
Cool. My plan is to write one macro for a bunch of sheets with horizontal destination arrays drawing from a vertical master array. I was just wondering if there's a better way to code all of this or is copying and transposing in VBA the best option.
 
As I mentioned earlier this completely depends whether your data in sheet1 will keep changing and more importantly will you need the data updated as and when the original data is changed...
 
Hi, PipBoy808!
Adding to what has been said, that process may be accomplished too by an INDEX function. Whether you use a macro or a function will depend on the data update, if it's static and a once time job, if it's dynamic, how and when do you want it updated, etc.
Tell us the frequency, the no. of worksheets, etc., to give you a more accurate advice.
Regards!
 
You must log in or register to reply here.
Back
Top