Picking out the max

Okay I have a table like this.

0907086 -- $40.54
0907086 -- $41.74
0907086 -- $47.70
0907095 -- $45.96
0907095 -- $47.31
0907095 -- $54.06
0907102 -- $40.54
0907102 -- $41.74
0907102 -- $47.70
0907111 -- $56.62
0907111 -- $58.28
0907111 -- $66.61

I want to pick out the biggest dollar value for each part number and
write it by the part number.

For instance

0907086 -- $40.54
0907086 -- $41.74
0907086 -- $47.70 -- 47.70
0907095 -- $45.96
0907095 -- $47.31
0907095 -- $54.06 -- 54.06
0907102 -- $40.54
0907102 -- $41.74
0907102 -- $47.70 -- 47.7
0907111 -- $66.61
0907111 -- $66.61
0907111 -- $66.61 -- 66.61

Any way of doing this ?

And something else I would like, although not necessary is on the last
section you will notice that I would like only one of them to have the
66.61 printed beside it.

Chow


---
Message posted from http://www.ExcelForum.com/

Assuming worksheet is sorted by Column A first, then Column B (like you
have in your example)...
enter this in C1 and auto copy down.

=IF(AND($A1=$A2,$B1$B2),"",$B1)


---
Message posted from http://www.ExcelForum.com/

Works like a charm Pika, Thx


---
Message posted from http://www.ExcelForum.com/

The suggested formula will only work if the numbers
keep going up within a range. And maybe that is the
way your numbers are -- if they are sorted. Try sticking
$99.99 in the middle of prices for a Part Number.

Here is a page with a downloadable addin that might
be of interest. by Dan Herrera
http://www.geocities.com/datamasterf...ionAddIns.html

The nice thing about the looking for answers on the web or in
the newsgroup archives is that you can even make up a
function name and seem if anyone can match a question for
a non existent function. I wasn't really expecting to find that
someone made up a function, but MAXIF was what I tried for
and it was what I found.

My own attempt with array formulas (Ctrl+Shift+Enter)
I couldn't get Column F without having
the formula in Column E.


--A-- ---B----- ---C---- ---D-- ----E---- ----F----
Part price price 0 FALSE
907086 $40.54 99.99 FALSE
907086 $99.99 99.99 99.99 TRUE 99.99
907086 $47.70 47.7 99.99 FALSE
907095 $45.96 54.06 FALSE
907095 $47.31 54.06 FALSE
907095 $54.06 54.06 54.06 TRUE 54.06
907102 $40.54 47.7 FALSE
907102 $41.74 47.7 FALSE
907102 $47.70 47.7 47.7 TRUE 47.7
907111 $56.62 66.61 FALSE
907111 $58.28 66.61 FALSE
907111 $66.61 66.61 66.61 TRUE 66.61

C2: =IF(AND($A2=$A3,$B2$B3),"",$B2)
D2: {=MAX(IF(A$1:A$13=A2,B$1:B$13,FALSE))}

E2: {=B2=MAX(IF(A$1:A$13=A2,B$1:B$13,FALSE))}
F2: =IF(E2,B2,"")
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"foolio " wrote in message ...
Works like a charm Pika, Thx


---
Message posted from http://www.ExcelForum.com/

If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to the OP's workbook, and if
the part numbers and prices are in A1:B12, the following, entered in C1
and filled down, will return the maximum prices for the part numbers;
unfortunately, it will duplicate the prices for duplicate max prices--I
haven't figured out how to eliminate that duplication:

=IF(B1=MAX(VLookups(A1,A$1:B$12,2)),B1,"")

Alan Beban

David McRitchie wrote:

The suggested formula will only work if the numbers
keep going up within a range. And maybe that is the
way your numbers are -- if they are sorted. Try sticking
$99.99 in the middle of prices for a Part Number.

Here is a page with a downloadable addin that might
be of interest. by Dan Herrera
http://www.geocities.com/datamasterf...ionAddIns.html

The nice thing about the looking for answers on the web or in
the newsgroup archives is that you can even make up a
function name and seem if anyone can match a question for
a non existent function. I wasn't really expecting to find that
someone made up a function, but MAXIF was what I tried for
and it was what I found.

My own attempt with array formulas (Ctrl+Shift+Enter)
I couldn't get Column F without having
the formula in Column E.


--A-- ---B----- ---C---- ---D-- ----E---- ----F----
Part price price 0 FALSE
907086 $40.54 99.99 FALSE
907086 $99.99 99.99 99.99 TRUE 99.99
907086 $47.70 47.7 99.99 FALSE
907095 $45.96 54.06 FALSE
907095 $47.31 54.06 FALSE
907095 $54.06 54.06 54.06 TRUE 54.06
907102 $40.54 47.7 FALSE
907102 $41.74 47.7 FALSE
907102 $47.70 47.7 47.7 TRUE 47.7
907111 $56.62 66.61 FALSE
907111 $58.28 66.61 FALSE
907111 $66.61 66.61 66.61 TRUE 66.61

C2: =IF(AND($A2=$A3,$B2$B3),"",$B2)
D2: {=MAX(IF(A$1:A$13=A2,B$1:B$13,FALSE))}

E2: {=B2=MAX(IF(A$1:A$13=A2,B$1:B$13,FALSE))}
F2: =IF(E2,B2,"")
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"foolio " wrote in message ...

Works like a charm Pika, Thx


---
Message posted from http://www.ExcelForum.com/

Hi!

To get rid of the duplication (at least to make it invisible) perhaps
conditional formatting.

"If C2=C1 then make C2's text colour same as its background". Copy
Down.

Alf


---
Message posted from http://www.ExcelForum.com/

Unfortunatly I can't download anything onto my server...

But I thought of a new way of looking at this that may work a little
better. You notice that the last price for each part number is always
the highest because of how it is sorted.

0907086 -- $40.54
0907086 -- $41.74
0907086 -- $47.70 --
0907095 -- $45.96
0907095 -- $47.31
0907095 -- $54.06 --
0907102 -- $40.54
0907102 -- $41.74
0907102 -- $47.70 --
0907111 -- $56.62
0907111 -- $58.28
0907111 -- $66.61 --

Is there a way to just figure out which part number is the last in the
list and tag it somehow.

0907086 -- $40.54
0907086 -- $41.74
0907086 -- $47.70 -- Last
0907095 -- $45.96
0907095 -- $47.31
0907095 -- $54.06 -- Last
0907102 -- $40.54
0907102 -- $41.74
0907102 -- $47.70 -- Last
0907111 -- $56.62
0907111 -- $58.28
0907111 -- $66.61 -- Last

Something like that ? Then I could just sort by Column C and I would
have my list....


---
Message posted from http://www.ExcelForum.com/

What's going on??? You posted at 4:58pm on 6/22/04 that the solution
posted by pikapika13 at 3:43pm that day "works like a charm". So why
are we still talking about it?

Alan Beban

foolio wrote:

Unfortunatly I can't download anything onto my server...

But I thought of a new way of looking at this that may work a little
better. You notice that the last price for each part number is always
the highest because of how it is sorted.

0907086 -- $40.54
0907086 -- $41.74
0907086 -- $47.70 --
0907095 -- $45.96
0907095 -- $47.31
0907095 -- $54.06 --
0907102 -- $40.54
0907102 -- $41.74
0907102 -- $47.70 --
0907111 -- $56.62
0907111 -- $58.28
0907111 -- $66.61 --

Is there a way to just figure out which part number is the last in the
list and tag it somehow.

0907086 -- $40.54
0907086 -- $41.74
0907086 -- $47.70 -- Last
0907095 -- $45.96
0907095 -- $47.31
0907095 -- $54.06 -- Last
0907102 -- $40.54
0907102 -- $41.74
0907102 -- $47.70 -- Last
0907111 -- $56.62
0907111 -- $58.28
0907111 -- $66.61 -- Last

Something like that ? Then I could just sort by Column C and I would
have my list....


---
Message posted from http://www.ExcelForum.com/

If they are in fact sorted then your second answer from

"pikapika13 " wrote ..
Assuming worksheet is sorted by Column A first, then Column B (like you
have in your example)...
enter this in C1 and auto copy down.

=IF(AND($A1=$A2,$B1$B2),"",$B1)

already holds your answer, add another column
or change the above:
=IF($A1=$A2,"","-- Last")

or you could use conditional formatting and highlight the
row in yellow instead or as well
entering formula with the active cell on row 1
formula is: =$A1$A2
or if you had a header row and did not want it to be
highlighted
formula is: =AND(Row()1,$A1$A2)
--
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"foolio " wrote in message ...
Unfortunatly I can't download anything onto my server...

But I thought of a new way of looking at this that may work a little
better. You notice that the last price for each part number is always
the highest because of how it is sorted.

0907086 -- $40.54
0907086 -- $41.74
0907086 -- $47.70 --
0907095 -- $45.96
0907095 -- $47.31
0907095 -- $54.06 --
0907102 -- $40.54
0907102 -- $41.74
0907102 -- $47.70 --
0907111 -- $56.62
0907111 -- $58.28
0907111 -- $66.61 --

Is there a way to just figure out which part number is the last in the
list and tag it somehow.

0907086 -- $40.54
0907086 -- $41.74
0907086 -- $47.70 -- Last
0907095 -- $45.96
0907095 -- $47.31
0907095 -- $54.06 -- Last
0907102 -- $40.54
0907102 -- $41.74
0907102 -- $47.70 -- Last
0907111 -- $56.62
0907111 -- $58.28
0907111 -- $66.61 -- Last

Something like that ? Then I could just sort by Column C and I would
have my list....


---
Message posted from http://www.ExcelForum.com/