[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 237
  • Last Modified:

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
0
tomfarrar
Asked:
tomfarrar
  • 3
  • 3
  • 2
  • +1
2 Solutions
 
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

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

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now