issue consolidating data in a pivot table - different text strings for the same field (vendor name)

[sschwant]

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.

Here's a view of the PT:

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.

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

 

[sschwant]

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.

Here's a view of the PT:

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.

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 . . .

 

[runnyspoon]

Steve

If you have a master vendor list with the Group ID, you could add a column to your source data (something like "Vendor Label") and add a lookup, using the Group ID as the key. Make sure that source data (maybe between columns D and E) has the lookup of the Vendor Label (the result of the lookup), put that in your pivot table instead of "Vendor":


-- removed inline image ---


HTH

Runny