Word Search VBA

[canthony24]

I have a pivot table that looks at supplier sales sorted descending on percent to total. Since suppliers have multiple vendor names, a particular supplier might show up as a different vendor in the pivot table. As you can see from a subset of the pivot below, Coca Cola shows up twice on the data, same supplier but a different vendor in a sense. I would like some type of code to search for similar vendor names and add up the sales to only show one. Coca Cola is just one example. Pepsi might be Pepsico, Pepsi Beverages.

Any ideas?

[TABLE="width: 409"]
<tbody>[TR]
[TD]COCA COLA[/TD]
[TD]5,409,343[/TD]
[TD]38.02%[/TD]
[/TR]
[TR]
[TD]PEPSICO[/TD]
[TD]2,614,334[/TD]
[TD]18.37%[/TD]
[/TR]
[TR]
[TD]COCA COLA BOTTLERS SALES&SVC[/TD]
[TD]1,787,875[/TD]
[TD]12.57%[/TD]
[/TR]
[TR]
[TD]NATIONAL BEVERAGE CORP[/TD]
[TD]931,219[/TD]
[TD]6.55%[/TD]
[/TR]
[TR]
[TD]CADBURY SCHWEPPES[/TD]
[TD]863,870[/TD]
[TD]6.07%[/TD]
[/TR]
[TR]
[TD]COTT BEVERAGES INC[/TD]
[TD]828,313[/TD]
[TD]5.82%[/TD]
[/TR]
</tbody>[/TABLE]

 

[AFPathfinder]

Do you have any control over the input of the data? How consistent are the vendor names between macro runs? Are there any columns in the data table that might link those companies together like NYSE symbols? The ideal solution would be to control input of the data by use of a combobox or the like, but if you're pulling this from external sources, you may not have that option.

You could just do the leg work to find all or most of the multiples and have the VBA loop through the data and adjust names accordingly, but new multiples would require further adjustments of the VBA.

A possibly inefficient and error prone way could be to load all vendor names to an array, loop through that array and filter the table on each name in the array, changing the names that match the filter.