I need to Delete Duplicate machine names in a excel spread sheet that has 14 columns.

I have an excel spread sheet that has 14 columns containing hostnames for machines. I need to delete duplicates within the excel Spreadsheet. How can I do that.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Do you mean remove duplicatyes on each row? ie for each row separately: removes duplicates on row 1 from row 1 then remove duplicate in row 2 from row 2 then... Or as a whole?
Or do you mean by column?
Could you please provide a quick example.

Depending on what you are trying to do, you may find the Advanced Filter feature usefull since it has a 'Unique Items Only' option: menu Data > Filter > Advanced Filter. Choose to either copy the list in place or into another location within the same sheet. Leave the Criteria blank. And make sure the checkbox for unique items is checked.

Nautical9Author Commented:
I need to check for duplicates, as a whole. That is... I want to compare all columns with each other. Make sure that a machine name is not duplicated in any other column?

Assuming that your worksheet is called Sheet1 and the range where the machine names are is called A1:P200 (or whatever will cover the names you have)

Add another worksheet, called Sheet2 say and in cell A1 put the following formula :-

=IF(COUNTIF(Sheet1!$A:$P, Sheet1!A1)>1,Sheet1!A1,"--")

Now copy this formula on Sheet2 so that it covers the same range as the names occupy on Sheet1 (A1:P200 in my example).

Those machine names that appear more than once will stand out.  Everything else will be --


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

You can do this with Conditinal Formatting on the original sheet:

Select the whole range ( here A1:B20)
Format,  Conditinal Formatting
set the dropdown to read "Formula Is "
In the text area enter:


Click the Format button and choose a format that makes the chosen cells stand out. Click OK
Jeroen RosinkSoftware testing consultantCommented:
you also might think of using a macro like:

Sub RemoveDups()
Application.ScreenUpdating = False
For Each cel In ActiveSheet.UsedRange
Set rg = ActiveSheet.UsedRange
If Application.CountIf(rg, cel) > 1 Then
    cel.Value = ""
End If
Next cel
Application.ScreenUpdating = False
End Sub

this removes all duplicate values from the active worksheet

Jeroen RosinkSoftware testing consultantCommented:
Not sure if this question is still an issue for you but you might take a look at an very good add-in:

Jeroen RosinkSoftware testing consultantCommented:
I suggest a split between: RogueSolutions, aflockhart and roos01.

as these solution all work

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.

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.