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.

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
--
HTH,
David McRitchie, Microsoft MVP -- Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm


<(E-Mail Removed)> wrote in message news:34241a6f-e2be-45c5-9df3-(E-Mail Removed)...
> 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.

On 12 Apr, 03:55, ErwinAlo...@gmail.com wrote:

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

Another one, just this single formula:

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

Note this is not case sensitive.

Regards,
Peter T

<(E-Mail Removed)> wrote in message
news:34241a6f-e2be-45c5-9df3-(E-Mail Removed)...
> 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.

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

"Peter T" <peter_t@discussions> wrote in message
news:e48F%(E-Mail Removed)...
> Another one, just this single formula:
>
> =SUMPRODUCT(--(COUNTIF(A1:A10,B1:B10)<>1))=0
>
> Note this is not case sensitive.
>
> Regards,
> Peter T
>
> <(E-Mail Removed)> wrote in message
> news:34241a6f-e2be-45c5-9df3-(E-Mail Removed)...
> > 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.
>
>

In message
<8dac8876-dfdb-4841-b0e2-(E-Mail Removed)>, Mike
Williams <(E-Mail Removed)> writes
>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
--
Martin Trump