Dynamic range that needs to be parsed, then reaggregated.

[CrashBandicoot]

I really would appreciate some extra eyes on this, I’m thoroughly stumped. Let me know if you have any questions or clarifications.

I have a daily reconciliation with 4 unfortunately un linked data sources. Thus far I have created a macro that can handle 3/4 sources but I've hit a wall on the 4th.

There is a sheet called "Data" with two columns that are causing issue, for discussions sake "A" & "B". A has a tag (RS##### format) and the adjacent cell B has a string of contiguous numerical text values (4-6 Digits) which are separated by commas (1234,54321,51423).

I need to parse the data in each cell of column B (row length varies from day to day).
With the Original 1st value remaining in column B and each subsequent 4-6 digit number in their own cell to the right of the original value in column Beta (which I can do successfully through VBA logic akin to text to columns, although I'm not sure its the most efficient way).
Then for each of the cells in a row, I need to re aggregate the original value and the subsequent values to the right into one contiguous list an another sheet.

EX.

The whole process would:

Step 1 (no issue - info purposes only) ‘Original Data
‘Sheet “Data” ‘ Columns A & B*

<code style="box-sizing: inherit; margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace; font-style: inherit; font-variant-caps: inherit; line-height: inherit; vertical-align: baseline; max-height: 300px; overflow: auto;"> A3 B3</code>RS1234 | “12345, 54321, 51423”
BR4353 | “ 34567,23145,2345,6789,22222,34543”
Etc.* | Etc. (Until last Row)*

• Last row A & B always the same

Step 2 (have achieved using text to columns but interested in different approaches if anyone has a better idea) - mostly info purposes only)
<code style="box-sizing: inherit; margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace; font-style: inherit; font-variant-caps: inherit; line-height: inherit; vertical-align: baseline; max-height: 300px; overflow: auto;">
Result of parsing:</code>‘Sheet “Data” ‘Columns A | B & Last column with data

A3 | B3 | C3 | D3 | E3
RS1234 | 12345 | 54321 | 51423 | 55443
A4 | B4 | C4 | D4
BR4353 | 34567 | 23145 | etc..
Etc..

• Note: if there is some way to skip this step that makes it easier I’m open to it, this was just my thinking as to where there might be a way forward. 


Step 3 Result of re-aggregation:

‘Sheet “Multi”
Next available cell in Row Y| Next available cell in row Z
<code style="box-sizing: inherit; margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace; font-style: inherit; font-variant-caps: inherit; line-height: inherit; vertical-align: baseline; max-height: 300px; overflow: auto;">
RS1234 |12345

RS1234 |54321

RS1234 |51423

BR3453 |34567

BR4353 |23415

etc*| etc.(until last row “A:B”with value on sheet “Data”)
</code>
Step 4: I can pick back up the process from this point.
Admittedly, I’m really stuck. I’m not a coder by trade, more of a puzzler who’s looking for answers to a problem that I can’t put down. It would save me ages and I feel like its feasible and It’s driving me nuts.

I sincerly appreciate all suggestions and assistance.

Thanks,
Crash