Excel - Sort and remove duplicates from column A

In column A I have PC Names, In column B I have the location name and column C the speed data.

The data shows PC abc several times with different or sometimes the same speed data.  I only need the highest speed and to remove any duplicate PC Names as I only want one instance of the PC Name.  I have the data sorted by speed data (column C) but have several instances of the same PC name showing up and it's not all together.     I've tried sorting by column C highest to lowest and adding sort column A by A-Z but I find the PC names in Column A all over the place and not sorted together.

Basically I need the speed data highest to lowest and only 1 instance of the PC Name.  This will tell me who is running the highest speed without mistakenly contacting them twice or three times.

Please see attached file for sample.
Exchange-Sample.xls
cmw082160Asked:
Who is Participating?
 
Rory ArchibaldConnect With a Mentor Commented:
Right click the row field (not the data field), choose sort, sort options and then specify descending using the data field.
0
 
-tjsCommented:
what version of excel?  excel 2010 actually has a remove duplicates button on the data tab of the ribbon.

Capture.GIF
0
 
cmw082160Author Commented:
It is Excel 2010 and I have tried that but it removed all but two instances of the duplicates.  Not sure why I could not get it down to just showing 1 PC name.
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
cmw082160Author Commented:
Sorry forgot to add this piece.

I would like 1 PC Name only showing and then sort the speed data highest to lowests.
0
 
ragnarok89Connect With a Mentor Commented:
Here you go.
Sub Macro1()

    Cells.Select
    Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("C2") _
        , Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
        False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
        :=xlSortNormal
        
Range("A3").Select
While ActiveCell.Value <> ""

    upper = ActiveCell.Offset(-1, 0).Value
    lower = ActiveCell.Value
    
    If lower = upper Then
        Selection.EntireRow.Delete
        lastrow = lastrow - 1
    Else
        ActiveCell.Offset(1, 0).Select

    End If
        
Wend

End Sub

Open in new window

0
 
Rory ArchibaldConnect With a Mentor Commented:
I would use a pivot table and set the data field to Max then sort on it. See attached.
Note: I cleaned up dome rogue characters from the data, which may be why the duplicates were not all removed.
Exchange-Sample.xls
0
 
cmw082160Author Commented:
OK, I tried the pivot table but I'm not sure now how to sort on the Data Field?  It is set to Max.
0
 
gowflowCommented:
So just to understand well you want to sort by max speed within the same Computer name and only keep the highest value per computer name and delete the other data for the same computer name and so on for all computers ??
gowflow
0
 
cmw082160Author Commented:
gowflow that is correct.
0
 
cmw082160Author Commented:
While neither of these solutions worked 100% the pivot table came the closest.  I think my data had some issues and while I have less duplication I still have some.

Thank you both for your efforts.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.