Pivot Table/VBA/Vlookup problem...

[jim5309]

I use excel 2007. I have about 5 years of daily dates in 1 column going all the way down. Across the top in a row i have 15 points (they are price index points). I also have the corresponding month and corresponding year in columns A,B--going down reading off data in column C. My goal is to be able to quickly bring up data from this chart. I want to be able to pick "month" "year(s)" and index point to bring up the data I want but a pivot table is not working. Does anyone know of a way to manipulate the data to get these drop down boxes as essentially inputs to bring up what i need? THANKS SO MUCH for reading this, and even more thanks if you take time to help me resolve this. Jim

 

[Jerry Sullivan]

Hi Jim,

If you are trying to show each row of data (each day) for the month and year selected in Columns A and B, then you would probably use Autofilters directly on your data.

If you just want to see the result of subtotaling each column of data for a selected Month and Year,(using a subtotal function like sum or average), then you would probably use a pivot table and Filter by Month and Year.

In both these methods, you would see all 15 columns of data and you can look at the index of interest to you.

Please explain which of these you are trying to do...and where you are getting stuck.

 

[jim5309]

thanks for taking the time to respond jerry, i sincerely appreciate it. i prefer the pivot table as it is easier. the auto filter isn't a bad idea, but it's just a little slower than i would like. from column d through column W i have cities. from c2 all the way down about 4000 rows i have each day starting with 1/1/1999. The data in each cell is an index price for that city. I added two columns in A, B, one just states the year. one just states the month. Doing a pivot table now doesn't allow me to see, "show me the price average for detroit, for january of 2001". The pivot table option is having trouble with the horizontal cities and gives me all the prices--not the check boxes to choose which one i want. let me know wht you think. Thanks again.
jim

 

[Jerry Sullivan]

Jim,

I agree that the horizontal nature of the 15 indexes in your current data set makes it hard to filter your PivotTable by a single index.
Here are a couple of approaches you might take in laying out your PivotTable . The best fit for you will depend on the kinds of analysis you want to do.


1. The layout that would be most compatible with the way your data is currently organized would be to have:

• Row Labels: Year, Month
• Summarize Values: Average of Detroit, Average of Chicago, etc....

Option A: You could have a column/field for each of 15 Cities displayed and just read the Index you want.
Option B: You could use the PivotTable Field List to toggle On just the City that you want to see.
​

2. You could reorganize your data into a 5 column format: Columns A,B,C remain as Year, Month, Date.
Use Column D for City and Column E for Value.



Your table size will go from its current 18 Column by ~4000 Rows to 5 Columns by ~60,000 Rows. Now you can layout your PT with:

• Row Labels: Year, Month, City
• Summarize Values: Average of Values

This would allow to filter by Rows as you were previously trying to do. You could also see averages of groups of Cities instead of just single Cities.


The first approach is best if you want to compare the Cities against one another for selected time periods.

The second approach is best if you want to easily select, Year, Month, City and show only one City at a time as you described in your original post.