Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 20
Default Lookup data in a variable table & retrieve data from a pivot table

I am trying to lookup data from two concatenated colums and retrieve data
from a pivot table where the column & row matches the concatenation, return
the max value in the pivot table. Below is my example data:

Pivot Table
Max of Value
Column Row Total
AB_N AB
AL 1.25
AR 1.15
AZ 1.62
BC
CA 1.25
CO 1.95
AL_N AB 1.95
AL 3
AR 1.75
AZ 1.65
AL_S AB 1.95
AL 3
AR 1.75
AZ 1.65

I want to retreive the max value where the concatenation of the below table
matches matches the column and row in the below table.

Table
O Region D State Concatenation
AR_N FL AR_NFL
PA_E GA PA_EGA
VA_E WA VA_EWA
VA_E TX VA_ETX
VA_E FL VA_EFL

Can anyone help?
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
Posts: 9,221
Default Lookup data in a variable table & retrieve data from a pivot table

Assume the pivot's data rows are in A3:C3 down
In D3: =A3
In D4: =IF(A4="",D3,A4)
Copy D4 down as far as required
This quickly creates the required "filled" helper col (for col A)
which remains dynamic to what's in the pivot's col A

Then, assuming the region & state paired inputs
are listed in F3:G3 down, eg:
AL_N AL
AB_N AZ
AL_S AR
etc

(the concat col can be dropped)

Just place this in H3, normal ENTER, then copy down:
=INDEX($C$3:$C$100,
MATCH(1,INDEX(($D$3:$D$100=F3)*($B$3:$B$100=G3),), 0))
to return the required max values from the pivot's col C
Adapt the ranges to suit the max expected extent
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,600 Files:362 Subscribers:60
xdemechanik
---
"Shawna" wrote:
I am trying to lookup data from two concatenated colums and retrieve data
from a pivot table where the column & row matches the concatenation, return
the max value in the pivot table. Below is my example data:

Pivot Table
Max of Value
Column Row Total
AB_N AB
AL 1.25
AR 1.15
AZ 1.62
BC
CA 1.25
CO 1.95
AL_N AB 1.95
AL 3
AR 1.75
AZ 1.65
AL_S AB 1.95
AL 3
AR 1.75
AZ 1.65

I want to retreive the max value where the concatenation of the below table
matches matches the column and row in the below table.

Table
O Region D State Concatenation
AR_N FL AR_NFL
PA_E GA PA_EGA
VA_E WA VA_EWA
VA_E TX VA_ETX
VA_E FL VA_EFL


  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 20
Default Lookup data in a variable table & retrieve data from a pivot t

Thanks Max! This is exactly what I was looking for. I figured I was making
it more complex than it really needed to be. Thanks again!

"Max" wrote:

Assume the pivot's data rows are in A3:C3 down
In D3: =A3
In D4: =IF(A4="",D3,A4)
Copy D4 down as far as required
This quickly creates the required "filled" helper col (for col A)
which remains dynamic to what's in the pivot's col A

Then, assuming the region & state paired inputs
are listed in F3:G3 down, eg:
AL_N AL
AB_N AZ
AL_S AR
etc

(the concat col can be dropped)

Just place this in H3, normal ENTER, then copy down:
=INDEX($C$3:$C$100,
MATCH(1,INDEX(($D$3:$D$100=F3)*($B$3:$B$100=G3),), 0))
to return the required max values from the pivot's col C
Adapt the ranges to suit the max expected extent
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,600 Files:362 Subscribers:60
xdemechanik
---
"Shawna" wrote:
I am trying to lookup data from two concatenated colums and retrieve data
from a pivot table where the column & row matches the concatenation, return
the max value in the pivot table. Below is my example data:

Pivot Table
Max of Value
Column Row Total
AB_N AB
AL 1.25
AR 1.15
AZ 1.62
BC
CA 1.25
CO 1.95
AL_N AB 1.95
AL 3
AR 1.75
AZ 1.65
AL_S AB 1.95
AL 3
AR 1.75
AZ 1.65

I want to retreive the max value where the concatenation of the below table
matches matches the column and row in the below table.

Table
O Region D State Concatenation
AR_N FL AR_NFL
PA_E GA PA_EGA
VA_E WA VA_EWA
VA_E TX VA_ETX
VA_E FL VA_EFL


  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
Posts: 9,221
Default Lookup data in a variable table & retrieve data from a pivot t

Delighted it helped. My pleasure, Shawna.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,600 Files:362 Subscribers:60
xdemechanik
---
"Shawna" wrote in message
...
Thanks Max! This is exactly what I was looking for. I figured I was
making
it more complex than it really needed to be. Thanks again!



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Using 2 cells in a table to retrieve data from another table Andy G Excel Worksheet Functions 0 March 20th 08 12:12 AM
Convert Pivot table back to Data Table Samaa Excel Discussion (Misc queries) 2 March 21st 07 11:02 AM
Best method to retrieve table data D.Jessup Excel Worksheet Functions 3 November 26th 06 06:26 PM
HOW TO EXTRACT (or lookup) DATA FROM A PIVOT TABLE SSJ New Users to Excel 3 November 9th 06 10:46 PM
Excel Pivot Table Plugin? (crosstab to data table) HoMoon115 Excel Discussion (Misc queries) 0 February 22nd 06 09:20 PM


All times are GMT +1. The time now is 08:44 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"