Macro for pasting, deleting, and shifting data

Hello,

I have a challenge for any macro wizard out there - this is something I really need and I'd be happy to reimburse whomever can help with an iTunes gift card or something.

I regularly receive a dataset such as the one you see here on the tab called 'Data comes in varying # of rows' (either link should work):

http://s25.quicksharing.com/v/498783...eeded.xls.html
http://www.sendspace.com/file/auz2zm

The dataset comes each time with a different number of rows, and what I need to do is combine all the 'Issue' and 'Issue Description' columns (there are 8 total) into just two columns.

I think this should be relatively easy for someone who has a better knowledge of macros by simply copying and pasting the data on top of itself (as I have done on the next tab called 'Want to change to this format'), and then simply deleting from the second section the 'Issue #1' and 'Issue #1 Description' columns, and shifting all the data left by two columns.

If you repeat this process for the third and fourth sections (for the third, you must delete 'Issue #1' and 'Issue #1 Description' and 'Issue #2' and 'Issue #2 Description' and shift the data left, while for the fourth you must delete Issues and Issue Descriptions 1 to 3 and shift the fourth Issue and Description to the left), you will end up with the columns A, B, C, and D as I have on the 'Want to change to this format' tab. (The extra columns after column D should all be deleted.)

Finally, the 3 extra header rows that are now at the top of the bottom three copied-and-pasted sections should all 3 be deleted.

The trick, I'm sure, is getting Excel to count the rows and only delete the ranges described above, but for varying dataset sizes. Any help is great appreciated.

Hi

Make sure you have a sheet in the file called OutPut, then select the data sheet and run the macro.

Please Login or Register  to view this content.

See how that goes.

rylo

Hi there,

rylo has beaten me to the punch, but here's my attempt nevertheless:

Please Login or Register  to view this content.

This uses the last entry in Column A (Account) as the maximum row number each time the macro is run.

HTH

Robert

Wow - thanks to you both, Rylo and Robert, so much for taking the time to help- this is more useful than you know!

The actual spreadsheet I receive has actually many more columns before the "Issue" columns begin, probably about 10 or so. In your macro, Robert, could I accommodate for this by simply replacing B with, say, J (where J = the last column before the "Issue" columns begin), and then replacing

Range("E2:F"

with Range("K2:L"

and so on?

Again - big, big thanks you guys.

Hi there,

Thanks for the feedback. Yes that should work fine - just try it on a backup of the relevant tab to be sure.

Cheers,

Robert