Solved

Excel - Sort and remove duplicates from column A

Posted on 2011-09-19
10
284 Views
Last Modified: 2012-06-27
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
0
Comment
Question by:cmw082160
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 6

Expert Comment

by:-tjs
ID: 36560582
what version of excel?  excel 2010 actually has a remove duplicates button on the data tab of the ribbon.

Capture.GIF
0
 

Author Comment

by:cmw082160
ID: 36560614
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
 

Author Comment

by:cmw082160
ID: 36560624
Sorry forgot to add this piece.

I would like 1 PC Name only showing and then sort the speed data highest to lowests.
0
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
LVL 8

Assisted Solution

by:ragnarok89
ragnarok89 earned 83 total points
ID: 36560628
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
 
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 167 total points
ID: 36560657
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
 

Author Comment

by:cmw082160
ID: 36561323
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
 
LVL 30

Expert Comment

by:gowflow
ID: 36561718
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
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 167 total points
ID: 36561723
Right click the row field (not the data field), choose sort, sort options and then specify descending using the data field.
0
 

Author Comment

by:cmw082160
ID: 36562094
gowflow that is correct.
0
 

Author Closing Comment

by:cmw082160
ID: 36562878
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Count text color with conditional formating in Excel 4 67
Paste Table from Excel VBA to the PowerPoint 2010 18 54
need help to look for 16 42
sort time order 10 45
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question