Best Way To Populate Table From Pivot Data

I am trying to bring back data from a pivot table to a date column that does not exactly match the date in the pivot table, which are identified as start and end dates.  The example should be self-explanatory.  Can you give me some thoughts?  Thanks.  - Tom
ExpertEx.xls
LVL 7
tomfarrarAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

johnb25Commented:
Tom,

If you paste this into I5 of your grid:
=IF(AND(I$4>VLOOKUP($H5,$B:$D,2,0),I$4<=VLOOKUP($H5,$B:$D,3,0)),VLOOKUP($H5,$B:$E,4,0),"")
You will need to change the pivot table to show the Destination on each row.
In Field Settings, Layout & Print tab, check Repeat Item Labels

John
0
tomfarrarAuthor Commented:
That is really quite brilliant, johnb25.  You provided me a totally new perspective on how to leverage the VLookUp function.  Thanks so much.  On a side note, can you show Destination on each row in Excel 2003?  I looked at Field Settings and did not see that option.  I worked around it by adding a fully-populated Destination field column to the left of the pivot table, and then modifying your formula ever so slightly.  Thanks again.  - Tom
0
johnb25Commented:
Tom,

Not sure if you can...I am now on 2013.
That option was greyed out for me, but I was not sure if that was because I did not have access to the source data for the pivot table, or the version of Excel.
Sounds like you have worked around it well anyway.

John
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

tomfarrarAuthor Commented:
Hi John - Glad you are on.  I did find a road block to the solution (I think).  It appears the VLookUp only picks the first line of the Destination when populating the table.  See the attachment. 3006-XYZ should have 7941 in 8/1/2013 through 7/31/2014.  Thoughts?
ExpertEx.xls
0
Ejgil HedegaardCommented:
Use Sumproduct to get data from all rows, not only the first match

=IF(SUMPRODUCT(($A$5:$A$94=$H5)*($C$5:$C$94<=I$4)*($D$5:$D$94>=I$4)*($E$5:$E$94))=0,"",SUMPRODUCT(($A$5:$A$94=$H5)*($C$5:$C$94<=I$4)*($D$5:$D$94>=I$4)*($E$5:$E$94)))

Open in new window

ExpertEx-1.xls
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
johnb25Commented:
Tom,

Copy this into your grid as an array (CSE) formula:
=IF(ISNA(INDEX($E$4:$E$94,MATCH(1,($H5=$A$4:$A$94)*(I$4>=$C$4:$C$94)*(I$4<=$D$4:$D$94),0),1)),"",INDEX($E$4:$E$94,MATCH(1,($H5=$A$4:$A$94)*(I$4>=$C$4:$C$94)*(I$4<=$D$4:$D$94),0),1))
See attached.

John
ExpertEx-2.xls
0
Glenn RayExcel VBA DeveloperCommented:
Ejgil's solution is best, IMO, but it also means that you don't even need a Pivot Table to complete this matrix.  You can enter a modified SUMPRODUCT formula on the raw data itself and get the same totals.

For example, if your data is on Sheet2 in columns $A$2:$D$94 (I found this by looking at the Pivot Table source information) and it is arranged like so:
Col A: Destination
Col B: Effective Date
Col C: Expiration Date
Col D: Total

Then you could insert this formula in cell I5 and then copy across and down and it will work:
=SUMPRODUCT(--($A5=Sheet2!$A$2:$A$100),--(B$4>=Sheet2!$B$2:$B$100),--(B$4<Sheet2!$C$2:$C$100),(Sheet2!$D$2:$D$100))+SUMPRODUCT(--($A5=Sheet2!$A$2:$A$100),--(B$4>=Sheet2!$B$2:$B$100),--(Sheet2!$C$2:$C$100=""),(Sheet2!$D$2:$D$100))

This formula only searches in the first 100 rows (your source example only has 94).  You'll want to increase the row count if you have more data.

{Side note: using commas inside of SUMPRODUCT function - rather than multiplication - operates faster}

I've attached an example file.  I only simulated the data for three destinations (3006-XYZ, 6043-XYZ, Conc-ABC); the rest are random values.

-Glenn

PS  If you didn't have any blank Expiration Dates in column C, the formula would be much simpler:
=SUMPRODUCT(--($A5=Sheet2!$A$2:$A$100),--(B$4>=Sheet2!$B$2:$B$100),--(B$4<Sheet2!$C$2:$C$100),(Sheet2!$D$2:$D$100))
EE-ExpertEx.xls
0
Ejgil HedegaardCommented:
{Side note: using commas inside of SUMPRODUCT function - rather than multiplication - operates faster}

I did not know that, thanks Glenn.
0
tomfarrarAuthor Commented:
Thanks for all the help.  John, your index/match solution though viable, did not work in Excel 2003 for some unknown reason to me. I tried it in a later version of Excel (2010), and it did work there.  But I am working in 2003 at the moment and that is where I need the solution.  But thanks for all your input and help.  

Ejgil, the sum product did the job, thanks!  Glen I appreciate your thoughts too.  Though I must still work with the pivot table, your thoughts might even be better down the road.

Thanks all.  - Tom
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Office Productivity

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.