ok, I finally got it working being able to find duplicates from two
columns... now I want to know how I would do three?

So I have three columns and I want to know if any data in any column also
appears in either of the other two columns.

If I named the columns A, B, and C then the scenario would be is anything in
A in B or C, is anything in B in A or C and is anything in C in A or B.

Thanks,
Terry

I think I'd do a little more work.

I'd combine all the values into one giant list (and eliminate the duplicates
from that list).

Then I'd use 3 additional columns showing if the value was in only one, or two
or all three lists.

If that's ok:

Copy the first list to a new worksheet (include the header row with this one).
copy the second list to the bottom of the first list (no header row)
copy the third list to the bottom of the second list (no header row)
(paste all these as values--not formulas)

Now select that column and do Data|filter|Advanced filter
specify Unique Records only
and Copy to a new location (B1 of this worksheet is fine)

Now delete column A--it's served its purpose.

Sort the new column A if you want.

Now in B2, C2, D2, use formulas like:

=isnumber(match(a2,sheet1!a:a,0))
=isnumber(match(a2,sheet2!a:a,0))
=isnumber(match(a2,sheet3!a:a,0))
and drag down

(I assumed that the original lists were in sheet1 to sheet3 all in column A.
Adjust if required.)

Add headers in B1, C1, D1:
In List1
In List2
In List3

And maybe in E1
Count of Lists Used
In e2:
=countif(b2:d2,true)
(and drag down)

Now select column A to column E
apply data|filter|autofilter

Filter on column E (show 1, 2, 3) to see how things stack up.





TerryM wrote:
>
> ok, I finally got it working being able to find duplicates from two
> columns... now I want to know how I would do three?
>
> So I have three columns and I want to know if any data in any column also
> appears in either of the other two columns.
>
> If I named the columns A, B, and C then the scenario would be is anything in
> A in B or C, is anything in B in A or C and is anything in C in A or B.
>
> Thanks,
> Terry

--

Dave Peterson
(E-Mail Removed)

Oops.

I figured the lists were on separate sheets. I should learn to read the
questions!

=isnumber(match(a1,sheet1!a:a,0))
=isnumber(match(a1,sheet1!b:b,0))
=isnumber(match(a1,sheet1!c:c,0))



TerryM wrote:
>
> ok, I finally got it working being able to find duplicates from two
> columns... now I want to know how I would do three?
>
> So I have three columns and I want to know if any data in any column also
> appears in either of the other two columns.
>
> If I named the columns A, B, and C then the scenario would be is anything in
> A in B or C, is anything in B in A or C and is anything in C in A or B.
>
> Thanks,
> Terry

--

Dave Peterson
(E-Mail Removed)