VBA to Compare Two Lists and Display the Differences in Each

Thanks Dave,

I understand your solution and how it would work with formulas on a worksheet but I am not sure I can get to the end result with my limited VBA knowledge.  I use some of the same books that Barry uses for VBA.      :)

I am building the lists on the fly...  comparing a list of people's names I read from and external database and a list of worksheet names in a master worksheet.  Checking to see if I have a worksheet for each person and then checking if I have an extra worksheet for a person that has been removed from the database.  I have snippets of VBA to extract the two lists but not sure how to compare them.

Can you provide some basic VBA with the CountIF that I can try to work with?

Thanks,
Jerry

:)  -  OK - I was typing too long  -  You beat me to it.  Let me look over what you wrote

THANKS!
Jerry

No worries

In brief the code
- defines the size of the ranges for columns A&B, inderts two new columns in C & D
- adds in the COUNTIF formulas  (the rng1.Offset(0, 2).FormulaR1C1 = "=IF(COUNTIF(" & rng1.Address(, , xlR1C1) & ",RC[-1])=0,RC[-1],"""")" part).
- coverts the formulas to values, to provide either text for differences, or to convert the "" into a blank cell (this is the shortcut trick)
- SpecialCells(xlblanks) then kills all the blank cells to remove the spaces in the list

Cheers
Dave

Dave,
Yes - I can make this work.  Thank you very much.  It is nice to have folks on the other side of the globe that are looking at EE when it is the middle of the night here...  :)

A million thanks!
Jerry

Thx for the grade Jerry. :)

The timezone things cuts both ways, I see far less questions here in "my time" than when I was based in the UK, but on the flipside I get to answer most of the questions in the timezone without too many others being around

Cheers

Dave

Dave,

If you are still online can you help me with some additional syntax on your code.  

It turns out that the row count on both the rng1 and rng2 need to be the set to the largest value of the number of rows in column A & B.  The formulas do not copy down far enough on the longer row and the CountIF is missing some values.  There are 97 rows in A and 103 in B.   So... where we are writing the formula to Column C, it stops at row 97 and is missing the remaining 6 comparisons.  Need something like the MAX function would provide.

I have tried a couple of times but I am not having much luck.  I am sure it is simple and but I am just not getting it correct yet.  I guess that is part of learning.   :)

Thanks,
Jerry  

Set rng1 = Range([a2], Cells(Rows.Count, "A").End(xlUp))
Set rng2 = Range([b2], Cells(Rows.Count, "B").End(xlUp))

                                              

1:
2:

Dave,

I believe I figured it out.  I may have taken the long way around but it seems to be working.    :)

Thanks,
Jerry

    Set rng1 = Range([a2], Cells(Rows.Count, "A").End(xlUp))
    Set rng2 = Range([b2], Cells(Rows.Count, "B").End(xlUp))
    Large = WorksheetFunction.Max(rng1.Count, rng2.Count)
    Set rng1 = Range([a2], Cells(Large, "A"))
    Set rng2 = Range([b2], Cells(Large, "B"))

                                              

1:
2:
3:
4:
5:

Just saw this - nicely done :)