Pivot Table Macro When Data Does Not Exist

[uvebeenwarrened]

I am trying to create a pivot table using a macro. The data being pivoted is dynamic and I will have different data to sort out from time to time. I will always have the same data that I want to select though just sometimes different data to deselect.

Attached is a sample workbook.

I may always want to display Code 1, Code 3, and Code 5 but never display anything else. When I create the macro and tell it to not display Code 2 if there is no Code 2 in the data it throws an error.

I read some other threads about using countif to solve this but was not able to figure out how to get that to work.

Can anyone help?

Thanks in advance!

Nate

[kersplash]

If you include your data in a table, then when you paste your new data in the table, then refresh the pivot it will contain the new data. No need for a a macro.

[uvebeenwarrened]

The data is from a report generated from another system and the data will change nearly every time we run the report. I'm looking to just the select the info that I want (if it exists) rather than deselecting data I don't want.

[kersplash]

From my experience, including the whole dataset and then using filters in the pivot table are the way to go. You just filter out what you don't want to see. Less debugging than using a macro.

[uvebeenwarrened]

Yah I agree. This data is too dynamic for that and I am trying to build something that even a beginner user can use. I want them to be able to just open the workbook and click the button and get what they want. If I have them start manipulating the data more than they already are there will end up being data errors.

[uvebeenwarrened]

Anyone else have any ideas?

[dflak]

You are trying to make a pivot table from data that isn't formatted for the purpose. In fact, it looks like it is a copied and pasted pivot table itself.

(A) Your best bet would be to get hold of the source data that created this pivot table.

(B) Second best is to use VBA to "normalize" the data into an Excel Table. Then you can build a real pivot table from this source. The macro that does this will empty the Excel Table before copying in the data. Since the pivot table is reading an Excel Table, it can remain in place. Excel tables know how big they are, so formulas and pivot tables that reference them don't need to be changed when the contents of the table is changed.

Try (A) first. If that option is not available, then let us know. I can do (B), but it's almost quittin' time on a Friday.

Give us a couple of more details about how you get the data, and how do you import it. If you are getting it as a file and the name is predictable and you are willing to save it in a directory, We can even do an import rather than copy / paste.

[uvebeenwarrened]

Thanks dflak.

Yes the data is not formatted for this purpose at all.

A-This is the data I'm stuck with. It is a report generated from one of our systems and I can't get access to the raw data. Having the raw data would make this really simple.

B-I think that I can do that but ultimately end up with the same problem. With the final pivot table I only want it to display let's say "Code 1" and "Code 3" but sometimes other code numbers exist and sometimes they don't so when I write the code...

Please Login or Register  to view this content.

If let's say "Code 2" doesn't happen to be in the data this time than I get an error. There will be times that some codes exist and times when they do. I'm trying to figure out how to get the pivot table/macro to not give me an error when that doesn't exist.

My current work around is that I made a master list of all the possible codes and have them automatically added to the new data every time so that when the macro tries to not make that code visible it is an option to deselect.

[dflak]

This spreadsheet takes the raw data you copy and paste in and converts it to an Excel Table suitable for pivot table analysis.

Copy / Paste, click the button.

You can reconfigure the pivot table any way you want.

[uvebeenwarrened]

That really great work dflak, thanks very much!

How can I get the macro to pre-select in the slicer which options it will display in the pivot table? So if when the code runs it automatically displays Code 1 and Code 3 but not Code 2?

[dflak]

If you want to "hard code" the pivot table to always exclude Code 2, then you can add a helper column to the data called Code OK or something like that with the formula: =[@Code]<>"Code 2" - this column will be true for any code other than Code 2. You can then use this column as a filter to filter out Code 2.

[uvebeenwarrened]

Thank you!

[uvebeenwarrened]

Hey dflak how can I create the data table if the headers on the raw data pivot table are not only dynamic but the amount of columns that are in the data may change?

[dflak]

Actually, I wrote the code with this in mind.

It finds the first row in the pivot table. It also finds the last row. It assumes that column D is always populated to the last row. The code looks at the headers in the first row and finds the last column. It assumes that the very last column is the total and we don't want to look at it. Likewise, the total row at the bottom of the pivot table is ignored

The code looks at columns 5 to the last column (minus 1) and adds a record only if the datum is non-zero. That's why you don't have any "Items" called "Column" - the column labeled "Column" doesn't have any data in it, so no records were created for it.

So, if you have additional columns with new names between column D and the total column (exclusive), the code will pick them up.

[uvebeenwarrened]

I did have additional columns and gives me an error. I thought it was because there were extra columns. I'll scrub the data and share a spreadsheet in a minute.

[uvebeenwarrened]

Here is a sample that is giving me an error.

[dflak]

Well, there you go. You have information above the pivot table. So instead of automatically detecting where the pivot table actually starts, I can "hard code" it to assume it will always start on row 9. And you added total rows, a feature *not* in the original data.

So I adjusted the code to deal with both.

[uvebeenwarrened]

Yah sorry about that stuff I thought that I would be able to deal with those things on my own but wasn't successful. I haven't opened this yet but will this handle a dynamic number of rows as well? There will be times where there may be as little as a few and other times there could be thousands.

[dflak]

Detecting variable columns should work. The issues had to do with rows and non-dates being where dates were expected.

[uvebeenwarrened]

dflak, I have tinkered with this a little bit but haven't had any luck figuring out why but the totals don't add up from the original data (1_1 tab) to the data table/new pivot created. I feel like there is an issue when there is data in multiple columns for one row but haven't been able to pin down the problem. I have attached a spreadsheet with some data that is a little less scrubbed. The codes in column D of the raw data and the codes in row 9 are dynamic will change what the codes are and how many of each and what order they are in every time the report is run. Also the amount of rows of data will be dynamic as well.

[dflak]

Try this one on for size. It seems to yield the correct numbers.

[uvebeenwarrened]

dflak, I thought this one was taken care of and then I found that there is another variant of the original report that does not contain a column that was originally in there. Column C had work date in it and now column C does not exist. I tried manipulating the macro to create the table data differently but can't seem to figure it out. Can you help? The data in tab 1_1 is the new one and the data in 1_1 old tab is the old data.

[dflak]

Please don't take this the wrong way, but HR people tend to make the most horrendous worksheets. They format it like a final report and manually fill in the blanks. HR means "Human Readable" which is not a format Excel can use "out of the box."

Somewhere the data for this report is in a database, then somebody wrote a query to pull it out and then formatted it to be human readable. So the job is to put the sausage back into the output of the grinder, turn the crank backwards to produce the pig.

If they will not let you access the actual data (and I do understand this) then tell them to send you a CVS dump of the data they used to generate the pivot table or tell them to send you a "live" pivot table instead of a static copy of the table. With a live pivot table, you can reorganize the fields to suit your own needs. At the very least you can go to the grand total cell (the one containing 5,833 in this case) and double click on it and it will give you a table of the data that went into the calculation of that cell.

The least you can do is get them to send you the data in a consistent format.