I have written VBA to take one .csv file saved as an .xlsm that conditions data in the first sheet, creates a new sheet and Pivot Table, opens another locally saved .csv, conditions its data, and creates a new sheet and Pivot Table.
4 sheets in total with 2 having conditioned raw data and two Pivot Tables created using said data.
On the sheet entitled "PIVOT_GAP" I have CONCAT formulae to combine the sub-header in the Pivot Table and the following 1, 2, or 3 rows of associated data.
After the data there is another sub-header and its data and repeats until the end of the Pivot Table which will never have the same rows created nor will they be in the same order.
Currently, I manually copy + paste the CONCAT formulae that use the following system:
Where A4 is the sub-header and A5 is the data. For sub-headers with more than one row, each subsequent row would still reference the sub-header but use its row number as the new value:
Each row of associated data will always start with "PRESS" -
could be used as a check.
What I need is to dynamically add formulae that will always CONCAT the sub-header with one row of associated data in a loop, move to the next row, CONCAT the same sub-header with the new row's data, and repeat.
Every time a new sub-header is reached a new CONCAT formula is started with the appropriate "A"& row#. It will do this until the last row is reach.
I'd perform the same process on the other Pivot Table sheet and then XLOOKUP for matching values before finishing the script.
The code I wrote that is close but incorrect is as follows (part of a larger script):
PIVOT_GAPS.png
I attempted to attach an example workbook but I am uncertain if it worked.
Thank you in advance for your assistance!
-Mark
Bookmarks