Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Best Way To Populate Table From Pivot Data

Posted on 2014-07-17
9
230 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
Easy, flexible multimedia distribution & control

Coming soon!  Ideal for large-scale A/V applications, ATEN's VM3200 Modular Matrix Switch is an all-in-one solution that simplifies video wall integration. Easily customize display layouts to see what you want, how you want it in 4k.

 
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
 
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

Manage your data center from practically anywhere

The KN8164V features HD resolution of 1920 x 1200, FIPS 140-2 with level 1 security standards and virtual media transmissions at twice the speed. Built for reliability, the KN series provides local console and remote over IP access, ensuring 24/7 availability to all servers.

Question has a verified solution.

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

A high-level exploration of how our ever-increasing access to information has changed the way we do our jobs.
The advancement in technology has been a great source of betterment and empowerment for the human race, Nevertheless, this is not to say that technology doesn’t have any problems. We are bombarded with constant distractions, whether as an overload o…
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

828 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