Picking out the max

F

foolio

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 an
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 las
section you will notice that I would like only one of them to have th
66.61 printed beside it.

Cho
 
D

David McRitchie

You can use Subtotal
1) place descriptive headings on row 1
Select A1:B13 alternatively you can can select A:B
2) Data (menu), Subtotals
each change in : Part
use function: Max
add subtotal to:
[x] Part
[x] Price
[x] Replace current Subtotals
[x] Summary below data

To get a list like this

Part price
907086 $40.54
907086 $41.74
907086 $47.70
907086 Max $47.70
907095 $45.96
907095 $47.31
907095 $54.06
907095 Max $54.06
....etc....

Which you can reduce to by
pressing the [2] level button

Part price
907086 Max $47.70
907095 Max $54.06
907102 Max $47.70
907111 Max $66.61
Grand Max $66.61

To Remove:
Use your backup file
-- just kidding:
You must have a cell within the totals active
Data, Subtotals, Remove all

I have web pages giving overview on such things, one of them is:
http://www.mvps.org/dmcritchie/excel/sumdata.htm

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
 
P

pikapika13

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

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

David McRitchie

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/datamasterfla/Scripts/FunctionAddIns.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,"")
 
A

Alan Beban

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 said:
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/datamasterfla/Scripts/FunctionAddIns.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,"")
Click to expand...
 
A

AlfD

Hi!

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

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

Al
 
F

foolio

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

But I thought of a new way of looking at this that may work a littl
better. You notice that the last price for each part number is alway
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 th
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 woul
have my list...
 
A

Alan Beban

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
 
D

David McRitchie

If they are in fact sorted then your second answer from

pikapika13 > said:
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)
Click to expand...

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