Pivot Table intersection of 3 data fields

[djarum11]

i am new to pivot tables but am familiar with filtering & sorting data.

challenge: from 12500 lines of 3 columns of data (Price, Day, Minute), find the maximum price of that day and the minute in which occurred.

i can easily find the max price of each day using the PT, by placing Price in the PT's VALUES (with Summarize Values by Max) and Day in the PT's ROWS. however i am having trouble obtaining the correlated minute. if i place the Minute data in VALUES it wants to do a calculation on it (like sum or max). if i place Minutes in ROWS, i get multiple Prices for each day, when i really i just want the max Price.

thanks in advance.

[ashishmehra2010]

Hi,

I have created sample file for you.

Did you try Slicers?

Please check the attached file. You can create multiple pivots & control them with a Slicer.

or you can post the expected output you want. Cheers!

Regards,
AM

[djarum11]

thanks anyway but i don't want any interaction--i don't need slicers. i want a report of the minute that the highest price occurred on each day.

source data:

day high minute
3/24/15 $80 2
3/24/15 $77 21
3/24/15 $46 40
3/23/15 $59 52
3/23/15 $44 31
3/22/15 $66 11
3/21/15 $81 16
3/21/15 $54 42
3/21/15 $51 39
.
.
.

the PT should just show the max value for each day and the minute it occurred. it should look like:

day high minute
3/24/15 $80 2
3/23/15 $59 52
3/22/15 $66 11
3/21/15 $81 16
.
.
.

TIA

[djarum11]

can anyone show me how to do this? i am having no luck finding what i need.
thanks

[djarum11]

bump!

can anyone help with this pivot table request? i think i have clearly outlined what i want, please lmk if it is not clear.

[bartelba]

Hi,

Try this one,

• a sheet with data
• a sheet with the pivot showing for each day the maximum and next to that a lookup for the minute when it occurred.

Bas

[djarum11]

thanks bartelba but my data has multiple values for each primary value.

i would think a basic multiple-criteria lookup would work here. in the attached example i try 2 different ways in F2 & F3 and neither works. i don't understand why.


table multiple criteria lookup.xlsx

[djarum11]

i got this working by:

1. creating a simple 2-column PT of just the date and the max(High), sorting the dates in reverse order, just to get a list of unique dates and the corresponding maximum High value on that date.

2. adapting this multiple-criteria formula (example): {=INDEX(C3:C13,MATCH(1,(B3:B13=C16)*(D3:D13=C18),0))} (from http://blogs.office.com/2012/04/26/u...okup-formulas/). for C16 and C18 (in this example) i use the values from step#1 above (date and highest High) as lookup criteria to then find column 3 (MINUTE).


cheers

[bartelba]

Hi,

Try this one.

Bas