Reordering data in worksheet

[R1Law]

I have to move data from multiple rows and columns associated with one record into a single row (in the example Position, Organization, and Job 1 - Job 5 percentages are on different rows and columns). There are approximately 500 data records that require this operation. I recorded a macro to do this operation in the first data set (which also clears the unnecessary rows after the data has been moved) but have no idea how to get that to run on the second data set below the first data set row (and subsequent rows). Any help would be appreciated. Here is the macro, it's obviously useful for only one row.

Please Login or Register  to view this content.

Moderator's note: Please take the time to review our rules. There aren't many, and they are all important. Rule #2 requires code tags. I have added them for you this time because you are a new member. --6StringJazzer

[Richard Buttrey]

Hi,

The attached uses this macro on Sheet1. Just click the blue button.
The Original data tab I left in for testing and refreshing sheet1.

Please Login or Register  to view this content.

[maniacb]

Here is another solution;

Please Login or Register  to view this content.

[Richard Buttrey]

Here is another solution;

Neat!

Bear in mind this will work quickly for the 26 rows in the example but where there are 500 records, implying 2500 rows, a macro that has to jump back and forth between VBA and the Excel App at every pass through the loop will be quite slow.

[maniacb]

Richard, you’re absolutely right. Your code will without a doubt work much more quickly than mine. Thanks for the feedback.

[R1Law]

Thank you for this! It works great and your solution gives me a better understanding of macros, still wrapping my head around this tool.