Sorting data to a completely different look (from a table to 3 columns)

[mgblair]

I have a file where I have to rearrange a fairly large table (a few hundred thousand cells, many of which are blank) into 3 columns. The attached image shows the before on the left and the after on the right (in another worksheet). Basically, the top row is account numbers, left column is items, and the numbers are prices. What I have in VBA is For loops using SpecialCells(xlCellTypeConstants).Count and lots of copying and pasting.

It works, but I imagine there is a more elegant solution. Any thoughts? Very much appreciated! Please let me know if this doesn't make sense.

Capture.PNG

[PCI]

Can you attach the excel file to save clics to prepare the sample

[jindon]

Please Login or Register  to view this content.

[mgblair]

That's the file. It deletes the 2nd column when it finishes copying and pasting. Makes it easy on me. I add the X's on Sheet2 so I can freely use End(xldown).

Pretty sure I don't understand jindon's solution but I'll play with it. Looks much nicer than mine.

Thank you!

123.xlsm

[PCI]

Try

Please Login or Register  to view this content.