Get unique computer names across several sheets/columns

[pbhound]

Hello all,

thanks in advance for any and all help.

I work in the IT field and have spreadsheets that have thousands of computer names in different columns and need to sort out the Unique names between them. how can i do this efficiently? i have already tried if statements, filter statements, count statements all with no luck for what i need.

basically i am looking to learn how to write a formula and/or macro that will allow me to compare names in sheet 1 column A, to sheet 2 Column A, and so on. what is the most efficient way to do this?

again thanks for the help.

[6StringJazzer]

Moderator's note: Please take the time to review our rules. There aren't many, and they are all important. Rule #1 requires good titles. "formula help" does not describe your problem. I have updated it for you this time because you are a new member. --6StringJazzer

I suggest you attach a sample file, or give a much more detailed description ("and so on" is not something you can write a formula or code for). Give sheet names and specific columns.

[pbhound]

sorry i thought the "and so on" was implied sheet 3 column A and continued with multiple sheets.

all machine names are in column A but have several thousand names in them. column names are "Machine Names" all the machine names start at cell A3. the sheet names can be "sheet 1", "sheet 2", Sheet 3".

im not looking for anyone to do the work for me but rather direct me on where to look and what formula to use in this instance.

i attached a "sample" spreadsheet.

in the spreadsheet lets assume that the computer names are not as generic as Computer1.

[6StringJazzer]

sorry i thought the "and so on" was implied sheet 3 column A and continued with multiple sheets.

Yes, that's what I assumed, but to actually write a formula, you need the sheet names. If the number of sheets is indeterminate then you would almost certainly need VBA. Also the fact that data starts in row 3 is important. So precision in the question helps us give you the best answer.

In Excel 365 I might try to do this with the VSTACK and UNIQUE functions but they're not available in 2019.

Since I am comfortable with macros I would probably use a VBA solution for this. This solution will work for any number of sheets with any names, as long as the only thing in the file is sheets with lists of computer names.

Option Explicit

Public Sub UniqueNames()

   Dim WS As Worksheet
   Dim Row As Long ' row
   Dim NameColl As New Collection
   Dim UniqueSheet As Worksheet
   Dim Name As Variant
   
   On Error Resume Next ' ignore error if duplicate item is attempted
   
   For Each WS In Worksheets
   
      Row = 3
      Do Until WS.Cells(Row, "A") = ""
      
         NameColl.Add Item:=WS.Cells(Row, "A"), Key:=WS.Cells(Row, "A")
         
         Row = Row + 1
      Loop
   
   
   Next WS
   
   On Error GoTo 0
   
   Worksheets.Add before:=Worksheets(1)
   With Worksheets(1)
   
      .Name = "Unique Names"
      
      Row = 1
      For Each Name In NameColl
         .Cells(Row, "A") = Name
         Row = Row + 1
      Next Name
   
   End With
   
End Sub

[pbhound]

thank you. as we do not have O365 we do not have the "unique" function yet (we are still on office 2016 (at home i have 2019).

[6StringJazzer]

This VBA solution should work in any version of Excel that supports VBA.

Also, if responding directly after a post it isn't necessary to hit Reply with Quote to quote the whole post. You can just type a reply. (When a quote is required it is best to just use the relevant portion.)