Macro to run Pivot Tables

[Banquo]

Hi all.
I'm looking to create the following by means of a Macro. Its shown in S0602vsS0603 attachment


Currently I do it manually. I copy the data into the S0602 and S0603 tabs in the spreadsheet and using SUMIF on the S0602 and S0603 tabs (shown with relevant columns highlighted) to give totals in the S0602vsS0603 tab.
Then I find the difference using B2-C2 etc. But as there are hundreds of thousands of lines it takes forever to run the SUMIF's

The data I use in the SUMIF is is Tabs S0602 and S0603


It runs quicker if I copy the data into the S0602 and S0603 tabs in the spreadsheet and run Pivot tables on S0602 and S0603 and then run VLOOKUPs on the Pivot tables I have created. It is very manual though Is there any way this Pivot Table method can be done by Macros?


Thanks

[dflak]

First I am going to introduce you to Excel Tables. Excel Tables have many advantages over regular ranges: they know how big they are so when you add or subtract data, the table grows or shrinks. All formulas, pivot tables and charts built off of tables change automatically too. Because tables know how big they are, you don't have to reference the whole column. Tables also remember formulas and copy them down automatically. Also formulas use the column header names which makes understanding them easier.

To make a table out of a range, select a cell in the range and select Home > Format as Table and follow the prompts.

The other thing I will introduce named dynamic ranges. These are created using the OFFSET command. The offset command has 5 arguments. =OFFSET(start here, go down x rows, go right y columns, return a range n rows deep and m columns wide).

Go to Formulas > Name Manager. Type in the name you want for the range: for example P_S0602_Pivot and in the refers to box type the formula. =OFFSET(S0602_Pivot!$A$4,0,0,COUNTA(S0602_Pivot!$A:$A)-1,2). Selecting the cells may fill in some of the information for you.

The above formula says that on the S0602_Pivot sheet, start in cell A4, go down zero rows, go right zero columns (so we're still in cell A4) and return a range COUNTA(A:A)-1 rows deep and 2 columns wide. The reason for the -1 is that we literally do not want to count the header.

This named range overlays the result of the pivot table and will grow and shrink with the number of rows returned (that's what the COUNTA does for you). These names can be used as the range to look up IN with a VLOOKUP.

If you select a blank area and press F3, you'll get a list of named ranges and there is an option to download the list on the sheet.

I overlaid each of the pivot tables with named ranges.

S0062 and S0603 are now tables. I gave you a macro to clear them so you can copy and paste data into them. If you are getting this data from another excel file, you can import it rather than copy and pasting it. I can tell you how to do that in a separate post.

Except for the pivot tables, everything else in the workbook is a table. The pivot tables are overlaid with named dynamic ranges, so they act like tables.

If I stopped here, you could manage the workbook by copy and pasting data and refreshing pivot tables.

I decided to sew the whole thing together with some VB code.

I assumed you need a list of unique codes that cover both S0602 and S0603. So I wrote a piece of code that copies all the codes from both tables into one. If you look at the code, you can see how having the data in a table makes the coding a lot easier.

If you want a unique list, the best way to get it is a pivot table. I did this on the combined table sheet. The combined table is in column A and the pivot table with the unique codes is in column E. Yup, I overlaid this with a named dynamic range so I can copy it into the S vs S table.

There is one piece of code that I won't explain and that is the subroutine called ClearTable. Pass it two arguments, a "pointer" to a sheet (even if it's in another workbook) and a Table Name. - It works every time. By "pointer to a sheet" I mean:

Please Login or Register  to view this content.

the sht is the "pointer."

Part of the code clears the S vs S table and copies in the unique codes. Since tables remember formulas and copy them down, then that's all we have to do to make the table work.

[Banquo]

Thanks so much for your reply. I'm a lot closer to where I need to be. But the exact result I need is shown in the S0602vsS0603 tab. The S0602 Pivot and S0603 Pivot tabs show the calculations I need. (You can probably ignore those tabs but they reflect the desired final result that is shown in S0602vsS0603)

In short, S0602 and S0603 can have close to a million rows, with tens of thousands of repeating ID codes (shown in yellow in Cols A). Currently I use SUMIF the respective yellow amount columns for each of these ID codes (but SUMIF takes forever on such big files. So tables seems like a great idea)

To finish, I need to get the difference (shown in Column D). ie the SUMIF total of each individual ID in S0602 minus the SUMIF total of each individual ID in S0603.

[dflak]

There is a raw data table for S0602 and S0603. It does not matter how many rows there are in the table. Since they are tables, they "know" how big they are. No matter how much or how little data you copy / paste into it, the pivot tables built off of them will reference exactly the right amount of data.

Then there are the corresponding pivot tables. They perform two functions: first they get a unique list of codes and second, they perform the "duty" of the SUMIFS. They add up the values associated with each code.

The table in the S0602vsS0603 sheet uses a VLOOKUP to get the data (pivot table value of the "SUMIFS") from the named ranges that overlay the pivot tables.

I made an assumption that the codes in column a on the S0602vsS0603 sheet are not "fixed," that is, the list depends on the codes found on the S0602 and S0603 sheets. This is why I combined the two tables and used a pivot table to get a unique list of codes. The difference between you list and mine is that mine are in alphabetical order thanks to the pivot table.

[Banquo]

Ah yes. That seems to run and give the result I need. Although I dont think I need the 'Combined Table'. Is it possible to get rid of this? I just need a list of unique IDs (like is shown in Column E of the Combined table tab)
I would then use these unique IDs as the 'Asset_ID_Code_040' column in S0602vsS0603 tab.

[dflak]

That is exactly what I am doing.

Column A is the combined codes from the S0602 and S0603 tables. Column E is the pivot table that "extracts" the unique values from this list. Then the code goes on to copy these into the S0602vsS0603 sheet

[Banquo]

I just require unique IDs from the S0602 tab rather than unique IDs from a combined list. I'll try to figure it out :-)

[dflak]

I made the assumption that you needed the combined unique values from both S0602 and S0603.

I "short circuited" the code that makes the combined table and got rid of the combined table. Then I changed the source for the pivot table on the combined sheet to reference S0602. From there the rest of the code is the same.

I think this is what you want.

[Banquo]

Ah yes. That works really well !! Thanks so much. I can tweak and adjust the basic format for so many different uses.