[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 317
  • Last Modified:

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.
JC
0
Nautical9
Asked:
Nautical9
3 Solutions
 
sebastienmCommented:
Hi,
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.

Regards,
Sebastien
0
 
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?
JC
0
 
RogueSolutionsCommented:
OK.

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 --



0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
aflockhartCommented:
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:

=COUNTIF($A$1:$B$20,a1)>1

Click the Format button and choose a format that makes the chosen cells stand out. Click OK
0
 
Jeroen RosinkCommented:
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

regards,
Jeroen
0
 
Jeroen RosinkCommented:
Not sure if this question is still an issue for you but you might take a look at an very good add-in:
http://members.iinet.net.au/~brettdj/

regards,
Jeroen
0
 
Jeroen RosinkCommented:
I suggest a split between: RogueSolutions, aflockhart and roos01.

as these solution all work

regards,
Jeroen
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Tackle projects and never again get stuck behind a technical roadblock.
Join Now