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

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


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

From novice to tech pro — start learning today.