Get rows with greatest value based on value in first column

[newtotheexcel]

Hello all,

I did not know anything about Excel but the most basic functions.

I want only the rows with the greatest value (3rd column) per user (1st column).

Here's a sample of the data:

user1 file1 5
user1 file2 2
user2 file1 244
user2 file2 104
user2 file4 1
user2 file3 1
user3 file2 390
user3 file1 193
user3 file3 2
user3 file4 2
user4 file1 243
user4 file2 85
user5 file2 136
user5 file1 123
user5 file4 17
user5 file3 8


Desired output:
user1 file1 5
user2 file1 244
user3 file2 390
user4 file1 243
user5 file2 136

Thanks for your help!

[NBVC]

Try a Pivot Table.

Insert a row above the data and enter headers.

Then select the Range and go to Insert Menu, select Pivot Table.

Choose destination, then drag user to Row Label area and Qty to the Summation area. Click Sum of Qty that appears in summation area, choose Value Field Settings and select Max.

[newtotheexcel]

Thanks NBVC for the quick reply!

Can I get only the rows with the greatest count--excluding the other rows?

Here's a sample of the data:

user1 file1 5
user1 file2 2
user2 file1 244
user2 file2 104
user2 file4 1
user2 file3 1
user3 file2 390
user3 file1 193
user3 file3 2
user3 file4 2
user4 file1 243
user4 file2 85
user5 file2 136
user5 file1 123
user5 file4 17
user5 file3 8


Desired output:
user1 file1 5
user2 file1 244
user3 file2 390
user4 file1 243
user5 file2 136

[NBVC]

This alternate:

in D2 enter formula:

=MAX(IF($A$2:$A$17=A2,$C$2:$C$17))

confirmed with CTRL+SHIFT+ENTER not just ENTER and copied down

In destination cell, say H2, enter formula:

=IFERROR(INDEX($A$2:$A$17,MATCH(0,INDEX(COUNTIF($H$1:$H1,$A$2:$A$17),0),0)),"")
confirmed with CTRL+SHIFT+ENTER and copy down.

Then, in
Now in column I2: =IFERROR(INDEX($B$2:$B$17,MATCH(1,($A$2:$A$17=H2)*($C$2:$C$17=J2),0)),"")

copied down.

In J2:

=IFERROR(VLOOKUP(H2,$A$2:$D$17,4,FALSE),"")

copied down