Max number in list of duplicates

[chrisk]

Hi,
I have a sheet with 6000 entries and associated risks
How do I get a list of the property with the highest risk.
eg
Site risk
1 8
1 14
1 6
2 5
2 15
2 9
3 3
3 5
3 7
4 8
4 16
4 4
and what I want is
site risk
1 14
2 15
3 7
4 16
So this has filtered out the duplicate site entries and only returned the
highest risk.

Thanks

 

[Gary''s Student]

Assuming your data is in cols A & B, in C1 enter:
=ROW() and copy down

in D1 enter:
=MAX(IF(A$1:A$100=ROW(),B$1:B$100)) and copy down

NOTE: the formula in D1 is an array formula and must be entered with
CNTRL-SHFT-ENTER rather than just the ENTER key.

 

[chrisk]

Hi, did that, Col C fills with the Row number
Col D is full of 0 in every cell.
What did I do wrong?
Yes, i did the C+S+E its got the funny brackets.

 

[Gary''s Student]

Try it out first on the exact sample data you posted.

Tell me what happens.

 

[Rick Rothstein]

The formulas Gary''s Student posted work correctly for me. It assumes your
data is in Columns A and B... is that where they are?

 

[Harlan Grove]

Hi,
I have a sheet with 6000 entries and associated risks
How do I get a list of the property with the highest risk.
eg
Site    risk
1       8
1       14
1       6
2       5
2       15
2       9
3       3
3       5
3       7
4       8
4       16
4       4
and what I want is
site    risk
1       14
2       15
3       7
4       16
So this has filtered out the duplicate site entries and only returned the
highest risk.

Thanks

 

[Harlan Grove]

I have a sheet with 6000 entries and associated risks
How do I get a list of the property with the highest risk.

....

If your data doesn't change often, then a Pivot Table would be the
best approach for this.

If the table were named Tbl with the column labels in the top row and
the site names weren't just serial integers, you'd also need a way to
list the distinct site names. The easiest way would be to use an
advanced filter on just the site name column, filtering in place and
showing only 'unique' records. Copy the filtered list, and paste it
somewhere else, say starting at cell A1 in another worksheet. Your
sample data would produce a list in A1:A5 with the test string 'Site'
in cell A1. Enter Risk in cell B1, and the formula

=DMAX(Tbl,2,A1:A2)

in cell B2. Then select A2:B5 and run the menu command Data >
Table..., leave the Row input cell entry blank and enter A2 as the
Column input cell, and click OK. The advantage of doing this as a Data
Table rather than as a Pivot Table is that Data Tables are part of
normal recalculation by default.

 

[chrisk]

Hi Gary's Student, yes,
it works really well on this.
Unfortunately my site numbers start with
0100 (twenty entries)
0120 (20 rows)
0412 (14 rows)
0412 old (18 rows)
and on to about 6034 type of thing.
They are relevant to the location so dont want to re-number.
I was trying to simplify by using 1,2,3 & 4

 

[Bernd P]

Hello,

Select a sufficient long area with 2 columns and array-enter
=Mfreq("max",A1:A6000,B1:B6000)

My UDF Mfreq you will get here:
http://www.sulprobil.com/html/mfreq.html

This macro works on numbers as well as on strings - but don't mix
them

Its a compromise between a pivot table and some tedious worksheet
function array-formulas. The advantage is that it updates with each F9
recalc.

Regards,
Bernd

 

[chrisk]

Ok Bernd P, it definately looks like the kind of theing that I want.
I have never used VBA (?) and don't know how or where to enter it.