• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 437
  • Last Modified:

Excel Macro to compare two columns (A and B) and display WHAT IS in A, but IS NOT in B

Hi Experts,

I currently have a Excel spread sheet cotaining two columns one a list of servers from vCenter and another one a list of servers from AD, the vcenter servers are servers that I got using powerCLI and AD servers are servers that I got using PowerSHELL, the names of each list could be lowercase or Uppercase, I need that to be ignore.  

I provided the background of the info just for general knowledge; the first columns of the server column A has 5375 cells and Column B has 8322 with blank spaces, What I am looking for is a macro that looks into the A column and compare to the B column, shows what is in A that is not in B, ignore the CAPS and blanks cells and maybe provide a total of what was found in A that was'nt in B.
  • 2
1 Solution
Results are in column C!

Sub test()

cola = 5375
colb = 8322
counter = 1

For i = 1 To cola
    Match = False
    For j = 1 To colb
        If LCase(Cells(i, 1).Value) = LCase(Cells(j, 2).Value) Then
            Match = True
        End If
    If Match = False Then
        Cells(counter, 3).Value = LCase(Cells(i, 1).Value)
        counter = counter + 1
    End If
MsgBox "Done"

End Sub

Open in new window

IslandrAuthor Commented:

Could you please explain line by line what this macro does please.

It's exactly as you asked, take a cell from column a, compare to every single cell in column b, if no match, add this value to column c. Continue with next cell of column a until all have been compared.
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.

Join & Write a Comment

Featured Post

Simplify Active Directory Administration

Administration of Active Directory does not have to be hard.  Too often what should be a simple task is made more difficult than it needs to be.The solution?  Hyena from SystemTools Software.  With ease-of-use as well as powerful importing and bulk updating capabilities.

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