Solved

Best Way To Populate Table From Pivot Data

Posted on 2014-07-17
9
227 Views
Last Modified: 2014-07-22
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
0
Comment
Question by:tomfarrar
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 6

Expert Comment

by:johnb25
ID: 40204187
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
 
LVL 7

Author Comment

by:tomfarrar
ID: 40204565
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
 
LVL 6

Expert Comment

by:johnb25
ID: 40204578
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
 
LVL 7

Author Comment

by:tomfarrar
ID: 40204639
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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 21

Accepted Solution

by:
Ejgil Hedegaard earned 350 total points
ID: 40204740
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
 
LVL 6

Assisted Solution

by:johnb25
johnb25 earned 150 total points
ID: 40204788
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
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40205349
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
 
LVL 21

Expert Comment

by:Ejgil Hedegaard
ID: 40205374
{Side note: using commas inside of SUMPRODUCT function - rather than multiplication - operates faster}

I did not know that, thanks Glenn.
0
 
LVL 7

Author Closing Comment

by:tomfarrar
ID: 40211958
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
This article will shed light on the latest trends when it comes to your resume building needs. For far too long, the traditional CV format has monopolized the recruitment market.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now