Help: Excel Copy Paste Speical (based on field)

Hey Master Maestros!

I wonder if any another could help me with a keyboard conundrum. It should be an easy thing to do – and super helpful for the community – but I can cant seem to crack this.

I have a 5000 long single row of data. It is basically a set of records but in a single column.
Let’s say the field value “New” defined where the new record starts, so,

New
Data 1
Data 2
Data X
New
Data 1
Data 2
Data 3
Data x
New

etc.

I want to transpose (paste special) the data in the single column into rows. So I need to figure out how to write a macro to find “New” and then copy that set of data up to the next “New” marker and then paste special that content into a row.

Does that makes sense?

The output would be:

New, Data 1, Data 2, Data X
New, Data 1, Data 2, Data 3, Data x
New, …

etc.

Any help would be very gratefully recieved from KM ninjas!

While you could probably do this with some combination of Keyboard Maestro and AppleScript, I think the easiest, and best, solution would be to use Excel VBA.

Do an Excel VBA macro record, and go through the steps manually.
Then you can edit the VBA macro to fine tune.

Something like this.

Note that there is a blank line at the front of Dest at the end - remove it with another action if desired or just delete the extra row after using the macro.

Keyboard Maestro Actions.kmactions (2.3 KB)