comparing lists

[Glenn]

I'm not sure if this is something that I can do in Access or Excel, so I am
sending it to both lists.

I have a few different lists that contain data (lists of names), one of
which includes all the possible names that should be in either of the other
two lists. I'd like to be able to view a report that shows which names are
on more than one report.

For example:

List A contains the master list of names: Mary A., John C., Mike D., Bill
B., Lauren P. Martha S., Lynn Q.

List B contains the names: Lauren P., John C., Martha S., Lynn Q.

List C contains the names: Bill B., Martha S., John C.

I'd like the report / worksheet / table to look like either of the following
(with preference being Option A):

Option A:
List A List B List C
Mary A.
John C. X X
Mike D.
Bill B. X
Lauren P. X
Martha S. X X
Lynn Q. X

Option B:
List A List B List C
Mary A.
John C. John C. John C.
Mike D.
Bill B. Bill B.
Lauren P. Lauren P.
Martha S. Martha S. Martha S.
Lynn Q. Lynn Q.

Are either of these possible? If so, which program should I use, and how
can I find out how to do it?

TIA,
Glenn

 

[Bernie Deitrick]

Glenn,

If your lists are in Excel, you can use a simple formula for this. For
example, say that list A starts in cell A1.

In B1, use the formula
=IF(COUNTIF(listB,A1)>0,"X","")
In C1, use the formula
=IF(COUNTIF(listC,A1)>0,"X","")

where listB and listC are named ranges in the same workbook.

Copy those formulas down, and you're done.

HTH,
Bernie
MS Excel MVP

 

[Trevor Shuttleworth]

Glenn

assuming List A is in Column A, Cells A2 to A8 with a Header in Cell A1,
List B is in Column A, Cells A11 to A14 with a Header in Cell A10, List C is
in Column A, Cells A17 to A19 with a Header in Cell A16

Then in Cell B2, put the formula:
=IF(ISNA(VLOOKUP(A2,$A$10:$A$14,1,FALSE)),"",VLOOKUP(A2,$A$10:$A$14,1,FALSE)
)

And in Cell C2, put the formula:
=IF(ISNA(VLOOKUP(A2,$A$16:$A$19,1,FALSE)),"",VLOOKUP(A2,$A$16:$A$19,1,FALSE)
)

Or, for the other alternative:
=IF(ISNA(VLOOKUP(A2,$A$10:$A$14,1,FALSE)),"","X")
and: =IF(ISNA(VLOOKUP(A2,$A$16:$A$19,1,FALSE)),"","X")

Drag down as necessary

Regards

Trevor

 

[Bob Flanagan]

Glenn, another alternative is the Duplicate Finder at my site.

Bob Flanagan
Macro Systems
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel