Comparing lists in Excel/Visual Basic

[ErwinAlonzo]

Let me set up the problem. I have two columns of data (names of
people) in Excel. I need to see if the names in Column A are the same
in Column B. However, the catch is that the order of the names in both
columns can be different. Also, the maximum number of names in each
column is 10, but there is no minimum, therefore the two lists may
have a different number of names.

The ultimate goal of this exercise is to see whether the names in
Column A and B are exactly the same (the order doesn't matter) and
obtain a simple TRUE or FALSE result.

I've been testing various IF statements (using a For...Next loop to go
down the list) but have had no luck. If anyone can help me understand
the logic to go about solving this, I would really appreciate it.
Thanks.

 

[David McRitchie]

Rather than True or False, if you used actual numbers
you might also spot some duplicates

C2: =IF(ISBLANK(A2),"",COUNTIF(B:B,A2))
D2: =IF(ISBLANK(B2),"",COUNTIF(A:A,B2))

But if you want true or false then
C2: =IF(ISBLANK(A2),"",COUNTIF(B:B,A2)>0)
D2: =IF(ISBLANK(B2),"",COUNTIF(A:A,B2)>0)

use the fill handle to fill down
http://www.mvps.org/dmcritchie/excel/fillhand.htm

 

[Mike Williams]

. . . the order of the names in both columns
can be different. Also, the maximum number
of names in each column is 10, but there is
no minimum, therefore the two lists may have
a different number of names. The ultimate
goal of this exercise is to see whether the
names in Column A and B are exactly the same
the order doesn't matter) . . .

You can write various algorithms for this one, but a very easy method
would be to use a simple "stock" algorithm that you probably already
have to hand, a sorting algorithm. Firstly run through the Excel data
and load the names exactly as they appear in the two Excel columns
into two separate VB String arrays. If the two arrays do not then
contain the same number of strings then straight away you can return a
false, since the list cannot possibly contain exactly the same set of
names. Then, if both arrays do contain the same number of strings,
perform a simple Sort (seperately) on both arrays. The names in the
two arrays will now be in sorted alphabetical order and you can then
simply run through them just once one element at a time conparing
element (n) of the first array with element (n) of the second array.
If you get to the end of the list without finding a "mismatch" on any
element then both arrays are the same. You would probably want to use
a textual comparison, both when yuo perform the sort and when you run
through the elements, so that Bill Gates for example would be seen as
being the same as bill gates.

Mike

 

[Peter T]

Another one, just this single formula:

=SUMPRODUCT(--(COUNTIF(A1:A10,B1:B10)<>1))=0

Note this is not case sensitive.

Regards,
Peter T

 

[Peter T]

Scrub previous! Could give false +ve if there are duplicates in column B.

Try this one instead -

=SUMPRODUCT(--(COUNTIF(A1:A10,B1:B10)<>COUNTIF(B1:B10,B1:B10))) = 0

Regards,
Peter T

 

[Martin Trump]

In message

Then, if both arrays do contain the same number of strings, perform a
simple Sort (seperately) on both arrays. The names in the two arrays
will now be in sorted

How about two ListBoxes with Sorted = True ?

Regards