Good day to an amazing community,
I am trying to develop a macro to loop through two arrays. Here is an example of how the data is set up:
Raw data is found in C4:C1500
$ data is found in D4:D1500
Partial string values (originaly pulled from Raw data) are in E4:E13.
Categories the Raw data will organized into are in F4:F13
Below is the formula I pieced together as a proof of concept.
=IF(VLOOKUP(" * "&E4&" * ",$C$4:$C$1500,1,0)=ERROR.TYPE(#N/A),"Research",VLOOKUP(" * "&E4&" * ",$E$4:$F$13,2,0))
This works one line at a time, and only until the bottom of the short lists (without writing a massive nested statement). I'm trying to loop the Partial & Category values through the Raw data to then analyze the data by Category & $.
Example: Sheet1
| Row | Column C (C4:C1500) | Column D (D4:D1500) | Column E (E4:E13) | Column F (F4:F13) |
|---|---|---|---|---|
| 3 | Raw Data | $ | Partial Data | Category |
| 4 | 210324Sales.BobParr.UT | 785.54 | BobParr | Sales |
| 5 | ABCDEF.210324.SalesHelenParr.TN | 214.46 | HelenParr | Sales |
| 6 | DashParr.WY.RR.210324 | 238.56 | DashParr | Research |
| 7 | Mgmt.NY.JackJackParr.210324 | 1,200.00 | JackJackParr | Management |
Sheet2:
| Rows | Column B (B4:B13) | Column C (C4:C13) |
|---|---|---|
| 3 | Category | Total $ |
| 4 | Sales | 1,000.00 |
| 5 | Research | 238.56 |
| 6 | Management | 1,200.00 |
From there I will do another VLOOKUP( to SUM( expenses found in Sheet2.
I'm open to all suggestions for both cleaning up the testing formula, and will be grateful for all support with the VBA.
Thank you.
Richard VO
