A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Picking out the max



 
 
Thread Tools Display Modes
  #1  
Old June 22nd, 2004, 10:32 PM
foolio
external usenet poster
 
Posts: n/a
Default 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/

Ads
  #2  
Old June 22nd, 2004, 11:43 PM
pikapika13
external usenet poster
 
Posts: n/a
Default Picking out the max

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/

  #3  
Old June 23rd, 2004, 12:58 AM
foolio
external usenet poster
 
Posts: n/a
Default Picking out the max

Works like a charm Pika, Thx


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

  #4  
Old June 23rd, 2004, 08:34 AM
David McRitchie
external usenet poster
 
Posts: n/a
Default Picking out the max

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/



  #5  
Old June 23rd, 2004, 09:04 AM
Alan Beban
external usenet poster
 
Posts: n/a
Default Picking out the max

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/




  #6  
Old June 23rd, 2004, 11:46 AM
AlfD
external usenet poster
 
Posts: n/a
Default Picking out the max

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/

  #7  
Old June 23rd, 2004, 04:20 PM
foolio
external usenet poster
 
Posts: n/a
Default Picking out the max

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/

  #8  
Old June 23rd, 2004, 06:06 PM
Alan Beban
external usenet poster
 
Posts: n/a
Default Picking out the max

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/

  #9  
Old June 23rd, 2004, 06:07 PM
David McRitchie
external usenet poster
 
Posts: n/a
Default Picking out the max

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/



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:25 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 OfficeFrustration.
The comments are property of their posters.