Adding and showing new data in a filtered Pivot Table

Hi all!

I tried to search for an answer to my question, but I'm not native english speaker so maybe I'm not using the right words - forgive me if this has maybe answered in the past.

I have a set of data (sales report) coming in week after week. Suppose columns are something like this:

Customer name, date, address, zip, product number, product description, quantity, value

I could be able to create a Pivot Table so that I can group Value by Customer name. Easy.

The incoming reports include all of our customers, but sometimes I need to see all the data... and sometimes I only need to see a few of them (let's call it "Customer set"), so I created a macro to filter and only show customers I'm interested to:

With ActiveSheet.PivotTables("PivotTable1").PivotFields("Customer name")
.PivotItems("3P S.R.L.").Visible = True
...
...
...
.PivotItems("BCS S.R.L.").Visible = True
.PivotItems("BEANTECH S.R.L.").Visible = True
.PivotItems("D.S.C. DIGITAL SYSTEM").Visible = True
.PivotItems("D.S.C. DIGITAL SYSTEM COMPUTERS SRL").Visible = True
End With

All is fine.

Suppose a new sales report comes in. I add new sales data to the Data Source Range and refresh the Pivot. Values for customers currently shown are updated, but new (if any) customers are not shown because of the filter in the pivot table.

What I need is: if a new Customer in "Customer name" is found, then I would like the Pivot Table to show it along with the previously selected Customers, so that I can easily see if new customers have bought some products and decide if such customers are interesting and I will add them to the above macro so that the next time I execute it I will have the data of the updated Customer set.

Please note I do not have a current "complete list" of customers that I am interested into; I need to manually be able to add them week after week. Moreover the sales report is coming in from different Resellers and very often the name of the same Customer is spelled with some differences, in example:

D.S.C. DIGITAL SYSTEM
D.S.C. DIGITAL SYSTEM COMPUTERS SRL

is the same Customer.

Seeing new Customers along previoulsy selected Customers in the Pivot Table will help me to spot very quickly new Customers (if any) and let me decide whether to add them to the above macro or not.

Do you think there is a way to do this?

Thank you for your suggestions,
Andrea

Hi Andrea,

The attached is an example workbook of how you could show your 'customer set' and new customers. When you add in the new data and refresh the pivot table all you would need to do is add any new customers to you customer set (if you are interested in them) or add them to your customer list (this could all be done through VBA if you wanted).

Pivot Example.xlsx

Let me know your thoughts,

Jay

Hi Jay, sorry for being so late in answering - January is fiscal year' end in the company I work for!

Well, your suggestion was G R E A T stuff and does not require any VBA (little manual work to do to update the workbook with new data).

I created a full "Customer List" table from existing data and I also created a preferred "Customer Set" table.

The Customer Set table includes two (actually they're more, but for easier explanation they're only two) columns:

A) Customer name B) Account Manager name

When new sales data comes in, I add such data to the "raw" existing data. A "net data" sheet helps me to "clean" raw data and format it like I, er, like.

Now a "Customer name" based VLOOKUP will 1) tell me whether a "Customer name" is an existing customer into the "Customer list" or if it is a new one and - if it is a new one - write "New Customer" on a certain Column (let's call it "Assigned/Unassigned/New" or 2) if such "Customer name" exists within the "Customer set", VLOOKUP the Account manager name and write it into the "Assigned/Unassigned/New" column. I simply added the above column to the Pivot Table and - voilą - it's working great!

The "Assigned/Unassigned/New" grouping is exactly what I was looking for.

When new customers are found, I have very good evidence of their "Customer name" into the Pivot Table. If some new "Customer name(s)" need to be paired to an Account Manager I simply add "Customer name(s)" to the "Customer Set" table.

Well, your idea was great. Thank you so much for your great suggestion!

Hopefully I will be able to help you back, albeit I'm not such Excel wizard

Cheers - Andrea