Databases
StackOverflow2013
Postsdbo
POIs this a good approach to transpose columns into rows in Excel VBA?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POIs this a good approach to transpose columns into rows in Excel VBA?
    text
    copied!<p>I have an Excel with the following structure which I want to convert to another structure:</p> <p><img src="https://i.stack.imgur.com/sOTrH.png" alt="Tranpose Year from Columns to Rows"></p> <p>The actual file is much more complex than this - but I created this schematic to describe the essence of the problem. The file currently has around 5K rows, but it is expected to contain around 50K-100K entries. So, the solution should have a good performance.</p> <p>What I have in mind is </p> <ol> <li>Copy columns from Customer Name to Unit Price and Year 1 Quantity &amp; Year 1 TotalCost and paste it in the destination range and add a column year number and populate it with 1</li> <li>Copy columns from Customer Name to Unit Price and Year 2 Quantity &amp; Year 2 TotalCost and populate year number column with 2</li> </ol> <p>The questions I have are these:</p> <ol> <li>Will this solution perform well?</li> <li>Are there other solutions that avoids copying and pasting multiple times?</li> <li>Is there a way to update the source range itself without pasting the data in a new destination range? </li> </ol> <p><strong>Homework I have done:</strong></p> <p>I did a google search and tried to read as many article as I could. I also read the following threads in Stackoverflow, but none of them has the answer that I am looking for</p> <p><a href="https://stackoverflow.com/questions/12372468/converting-excel-rows-to-columns-smarter-than-transpose">Converting Excel rows to columns (smarter than transpose)</a></p> <p><a href="https://stackoverflow.com/questions/12097773/transpose-multiple-rows-to-multiple-columns">Transpose multiple rows to multiple columns</a></p> <p><a href="https://stackoverflow.com/questions/11725698/excel-converting-rows-to-columns-with-groups">Excel Converting rows to columns with groups</a></p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload