How to compare 2 lists in Excel VBA?

[ChrisG]

Hello All,

It's come to me to create a manner to compare 2 lists of computer
against eachother. I need to return all values from list 1 that ar
missing in list 2 and then return all values in list 2 that are missin
in list 1.

This will be used to compare what computers are checking in against ou
NAV servers to the list of computers we have in AD for that region.
Basically, when we do this by hand, we find computers from one regio
that should be using a different NAV server.


Unfortunately, it's been several years since my limited exce
programming and VBA training and I'm staring at my screen much lik
it's greek. I know it should be a simple task and any tips anyon
could add that could get my mind churning in the right direction woul
be much appreciated.

So far, I've been able to compare list1 to list2 and return th
differences with an example that I've found. The problem being, a
soon as list1 has a different value, the rest of the list is considere
different and returned as such.

In my mind, I picture the solution to look something like:

Range("A4:A500,C4:C500").select
value1 = rng1.value1(first value in range 1)
value2 = rng2.value1(first value in range 2)
if value1 <> value2 then value2 = rng2.nextvalue(I'm not sure how t
move to the next value in a list)

I know the above doesn't work and I haven't gotten a logic loop yet...

Maybe it would be a good time to ask for help... so...

Help!

Thanks,

Chris

 

[Tom Ogilvy]

easier just to put in a formula next to each list

assume list1 is in A1:A100 and List2 is in F1:F120
In B1 put in
=Countif($F$1:$F$120,A1)
then drag fill down to 100

in G1 put in
=countif($A$1:$A$100,F1)

then drag fill down column G to row 120

Now you can use a filter on each list, filtering on the column with the
formulas, and use 0 as the criteria (zero would indicate that the computer
in that list is not in the other list).

 

[keepITcool]

Why dont you download a trialversion of Synkronizer XL 8.0 from
http://www.synkronizer.com


It's an addin that'll do exactly what you want to do, and probably does
it faster and better then you could ever program it.

You can test it for 30 days before you'll need to buy.
User versions: $30 to $80, Developer version: $200


(and..yep.. I've developed a major part of it)

keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >