issue consolidating data in a pivot table - different text strings for the same field

I've posted this on other forums but have not had any replies yet.

I am building a pivot table with a calculated field to compute the variance of actual amounts to forecast amounts (last column in the pt below).
The pivot is based off a table which was consolidated from a table of actual results and a table of forecast amounts.


Here's a few rows of the source data for the pivot table filtered on group id 499, and Department. Here you can see that we have two different text strings for the same vendor.

source data img.PNG

Here's a view of the PT:

PT img.PNG

The problem is the Vendor names are different from the Forecast data set and the Actual data set. If you pull the Vendor field out of the pivot, then the Group ID 499 (vendor Insight Software or vendor "Insight" same vendor) nets to zero variance, as it should. Same issue w/ Grp ID 436 as well btw ... of course there are numerous other instances of the problem.

However, since there are literally thousands of vendors and associated group ID's in the data, the client needs to see the vendor name in the PT in addition to the Group ID. The client would like me to come up with some conditional logic (w/in the source data spreadsheet) that states if the Group ID and Department # Match, then give me the set of data which includes a clean vendor name from the Forecast data, OR a clean vendor name from the Actual data. This is where it gets a little fuzzy. In this example, we'd want to replace the vendor name for the Actual amount, with the vendor name from the Forecast amount. Then they would be the same or equivalent, so they would net to zero in the PT for Grp ID 499 (as it should), as if the vendor name was a a filter and not a field. There may also be some instances where we'd want the opposite ... i.e., pull in / replace the vendor name using the Actual vendor name rather than the F/c vendor name.

Is there an easier way to do this rather than using some long nested series of If / Then / Else statements in the source data???

Thanks in advance for any ideas.

steve

post - post

I found a brute force way to clean up my pivot by going to the data set for Actuals, filtering on those Vendor Names which also included the grp id in the prefix and then using Text to Columns to split out the prefix. Then pasted the new vendor names into the source table for the pivot and voila. Still wondering if there isn't an easier way . . .

You could clean up your data table by having a lookup table of Group ID and Vendor name

499 Insight
436 Inova Solutions


And overwriting the vendor column with

=VLOOKUP(GroupID,TableAddress,2,False)