curtishavak
New Member
- Joined
- May 31, 2012
- Messages
- 41
I have a two sets of data from two different subsidiaries that serve the same market. The newer subsidiary "bought" customers from the old one, but the old subsidiary still services the market with those products that still remain outside of the capability of the new subsidiary. Easy, right?
The fun part comes in where the only common data field in the output reports that I have is customer name. Yes, in our genius, no one in IT decided that a unique customer or site number would be a good idea to include as a parameter or output on the report... Then I could at least maintain a join table correlating the customer numbers together.
Anyway, what I need is VBA code that will help me to group these customer sites together to be viewed as a single entity from the corporate home office. I have loaded the data into a pivot report, but there are 1200+ customers that need to be reviewed for grouping and I'd like to do this automatically instead of manually. This is because anytime I do this report, I have to re-run all the historic data because we don't properly control change to the customer master, meaning if someone changes a name, the link to my historic data is broken and I'm no longer able to accurately compare individual customer sites over time.
Back to the problem at hand... Is there a way to code VBA to do a wildcard search of the Customer Name field in a Pivot Table, and then group names that are exact matches with the exception of the characters "(IE)" at the end of the customer name? For example:
Customer A
Customer A (IE)
The above two items should be grouped together. However, I need the code to ignore spaces because sometimes this data is entered with a double space between the customer name and the "(IE)" tag. Example:
Customer A
Customer A (IE) - one space
Customer B
Customer B (IE) - two spaces
The data is sorted alphbetically, so it would appear as above, but mixed with individual non-groupable customers. Example:
Customer A
Customer A (IE) - one space
Customer B
Customer B (IE) - two spaces
Customer C
Customer D
Customer D (IE) - two spaces
As a corollary, it would also be very helpful if the grouped customer could then be renamed based on the value of the non-"(IE)" customer name + "(Grouped)" text. Example, taking the above data set, the resulting, grouped and renamed pivot table rows would look like this:
Customer A (Grouped)
Customer B (Grouped)
Customer C
Customer D (Grouped)
Any ideas? Help me Interwebs, you're my only hope!
The fun part comes in where the only common data field in the output reports that I have is customer name. Yes, in our genius, no one in IT decided that a unique customer or site number would be a good idea to include as a parameter or output on the report... Then I could at least maintain a join table correlating the customer numbers together.
Anyway, what I need is VBA code that will help me to group these customer sites together to be viewed as a single entity from the corporate home office. I have loaded the data into a pivot report, but there are 1200+ customers that need to be reviewed for grouping and I'd like to do this automatically instead of manually. This is because anytime I do this report, I have to re-run all the historic data because we don't properly control change to the customer master, meaning if someone changes a name, the link to my historic data is broken and I'm no longer able to accurately compare individual customer sites over time.
Back to the problem at hand... Is there a way to code VBA to do a wildcard search of the Customer Name field in a Pivot Table, and then group names that are exact matches with the exception of the characters "(IE)" at the end of the customer name? For example:
Customer A
Customer A (IE)
The above two items should be grouped together. However, I need the code to ignore spaces because sometimes this data is entered with a double space between the customer name and the "(IE)" tag. Example:
Customer A
Customer A (IE) - one space
Customer B
Customer B (IE) - two spaces
The data is sorted alphbetically, so it would appear as above, but mixed with individual non-groupable customers. Example:
Customer A
Customer A (IE) - one space
Customer B
Customer B (IE) - two spaces
Customer C
Customer D
Customer D (IE) - two spaces
As a corollary, it would also be very helpful if the grouped customer could then be renamed based on the value of the non-"(IE)" customer name + "(Grouped)" text. Example, taking the above data set, the resulting, grouped and renamed pivot table rows would look like this:
Customer A (Grouped)
Customer B (Grouped)
Customer C
Customer D (Grouped)
Any ideas? Help me Interwebs, you're my only hope!
