comparing lists

  • Thread starter Thread starter Glenn
  • Start date Start date
G

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
 
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
 
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
 
Back
Top