Automate the grouping function within a Pivot table based on wildcard search

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!
 

Forum statistics

Threads
1,082,280
Messages
5,364,210
Members
400,786
Latest member
ismi88

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top