Help needed with lists.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Chris Mitchell - 28 Jun 2008 07:14 GMT

I have two lists on separate sheets in the same book; each contains a common
field in column A. One list is much longer than the other, and both are
over 5K rows.

In the longer list I need to show if the common field appears in the other,
shorter list. I would like to add a column, 'Exists in shorter list' to the
longer list and have a formulae enter 'Yes' or 'No' as appropriate.

I've tried VLOOKUP, but this doesn't work across different sheets.

How can I do this?

TIA

Chris.

Reply to this Message

pub - 28 Jun 2008 08:03 GMT

> I have two lists on separate sheets in the same book; each contains a
> common field in column A. One list is much longer than the other, and
[quoted text clipped - 12 lines]
>
> Chris.

if Sheet1 starting Cell A1 is your longer list
and Sheet2 Column A is your shorter list
then copy&paste this formula in Sheet1 cell B1

=IF(COUNTIF(Sheet2!A:A,A1)>0,"Yes","No")

then copy&paste down.

the countif() looks at sheet2 and counts cell A1 contents if theres more
then zero. the if() is just the usual true/false.

Reply to this Message

Dave Peterson - 28 Jun 2008 12:51 GMT

You can use =match() to look for matches.

=match(a1,sheet2!a:a,0)

will return a number (the index into sheet2!a:a) if there is a match.

It will return an error if there is no match.

So you could use:

=isnumber(match(a1,sheet2!a:a,0))
to see true for matches and false for no match.

You may want to look at Chip Pearson's site. He has lots of techniques to work
with duplicates:
http://www.cpearson.com/excel/Duplicates.aspx

> I have two lists on separate sheets in the same book; each contains a common
> field in column A. One list is much longer than the other, and both are
[quoted text clipped - 11 lines]
>
> Chris.

Signature

Dave Peterson

Reply to this Message

ShaneDevenshire - 28 Jun 2008 15:30 GMT

Hi Chris,

VLOOKUP does work across different sheets, for example your formula would be
something like this:

=IF(ISNA(VLOOKUP(A1,Sheet1!A$1:A$182,1,FALSE)),"No","Yes")

or

=IF(ISNA(VLOOKUP(J5,Sheet1!A$1:A$182,1,FALSE)),"No","Yes")

or

=IF(ISNA(VLOOKUP(J5,Sheet1!A$1:A$182,1,)),"No","Yes")

Where the values you want to compare are in the current spreadsheet starting
in cell A1 and the other values are in column A of the other sheet (here
Sheet1).

Signature

Cheers,
Shane Devenshire

> I have two lists on separate sheets in the same book; each contains a common
> field in column A. One list is much longer than the other, and both are
[quoted text clipped - 11 lines]
>
> Chris.

Reply to this Message

Chris Mitchell - 30 Jun 2008 08:15 GMT

Thanks to all who responded.

I have now been able to get this to work as I wanted.

Thanks again.

Chris.

>I have two lists on separate sheets in the same book; each contains a
>common field in column A. One list is much longer than the other, and both
[quoted text clipped - 12 lines]
>
> Chris.

Reply to this Message