I have a worksheet with a lot of raw data. In one column I have an
identifier, in another I have dates, and in another column I have values.

I need to build a summary table on a new worksheet that will (given a date
and identifier) return the max value. I know MAX will get me the max value
overall, but not limited to just my double match. In the past I've used
things like sumproduct to pull multiple matches, but in this case I don't
want all the values returned, just the max value.

I don't need a polished formula, just some ideas on where to get started.

Thanks!
Keith

ID Date Value
--- ------ -------
A 1/4/06 12
B 1/4/06 8
A 1/4/06 11
D 1/4/06 7
G 1/5/06 14
F 1/1/06 12
A 1/5/06 17
D 1/3/06 8
B 1/4/06 5
V 1/3/06 9

so if I was checking A, 1/4/06 the result would be 12

ker_01 wrote:
I have a worksheet with a lot of raw data. In one column I have an
identifier, in another I have dates, and in another column I have values.

I need to build a summary table on a new worksheet that will (given a date
and identifier) return the max value. I know MAX will get me the max value
overall, but not limited to just my double match. In the past I've used
things like sumproduct to pull multiple matches, but in this case I don't
want all the values returned, just the max value.

I don't need a polished formula, just some ideas on where to get started.

Thanks!
Keith

ID Date Value
--- ------ -------
A 1/4/06 12
B 1/4/06 8
A 1/4/06 11
D 1/4/06 7
G 1/5/06 14
F 1/1/06 12
A 1/5/06 17
D 1/3/06 8
B 1/4/06 5
V 1/3/06 9

so if I was checking A, 1/4/06 the result would be 12




You will likely get some formula options, but you might also want to consider a
PivotTable. You can place "Date" in the Page Fields, "ID" in the Row Fields and
"Value" in the Data Items (change from Sum to Max).

=MAX(INDEX((A2:A11="A")*(B2:B11=--"1/4/2006")*C2:C11,))


"ker_01" wrote:

I have a worksheet with a lot of raw data. In one column I have an
identifier, in another I have dates, and in another column I have values.

I need to build a summary table on a new worksheet that will (given a date
and identifier) return the max value. I know MAX will get me the max value
overall, but not limited to just my double match. In the past I've used
things like sumproduct to pull multiple matches, but in this case I don't
want all the values returned, just the max value.

I don't need a polished formula, just some ideas on where to get started.

Thanks!
Keith

ID Date Value
--- ------ -------
A 1/4/06 12
B 1/4/06 8
A 1/4/06 11
D 1/4/06 7
G 1/5/06 14
F 1/1/06 12
A 1/5/06 17
D 1/3/06 8
B 1/4/06 5
V 1/3/06 9

so if I was checking A, 1/4/06 the result would be 12

=MAX(IF(A2:A20="A",C2:C20))

as an array formula, so commit with Ctrl-Shift-Enter, not just Enter.

--
__________________________________
HTH

Bob

"ker_01" wrote in message
...
I have a worksheet with a lot of raw data. In one column I have an
identifier, in another I have dates, and in another column I have values.

I need to build a summary table on a new worksheet that will (given a date
and identifier) return the max value. I know MAX will get me the max value
overall, but not limited to just my double match. In the past I've used
things like sumproduct to pull multiple matches, but in this case I don't
want all the values returned, just the max value.

I don't need a polished formula, just some ideas on where to get started.

Thanks!
Keith

ID Date Value
--- ------ -------
A 1/4/06 12
B 1/4/06 8
A 1/4/06 11
D 1/4/06 7
G 1/5/06 14
F 1/1/06 12
A 1/5/06 17
D 1/3/06 8
B 1/4/06 5
V 1/3/06 9

so if I was checking A, 1/4/06 the result would be 12