Solved

Best Way To Populate Table From Pivot Data

Posted on 2014-07-17
9
224 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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

757 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

19 Experts available now in Live!

Get 1:1 Help Now