Dynamic range that needs to be parsed, then reaggregated.

CrashBandicoot

New Member
Joined
Oct 16, 2017
Messages
8
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
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,031
Messages
5,856,937
Members
431,841
Latest member
jaybeem

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top