Excel question

[E. Buygum]

Hi folks
I know Excel a little but clearly not enough...Google hasn't helped either. I have 2 worksheets (1 and 2) and in Column A I have a list of companies. I'm trying to produce a third work sheet comprising a list of companies which appear both in worksheets 1 and 2 (i.e. the overlap between the 3 groups). Sounds dead easy and it probably is but I'm being thick. Any help with formulae gratefully received.
ta

 

[GS_or_bust]

Use the lookup function to lookup all the entries in one worksheet in the other.

 

[E. Buygum]

Use the lookup function to lookup all the entries in one worksheet in the other.

Tried this but couldn't get any sense out of it. Have simplified things a little. I have two columns of names: A and B. In A there are 500 names so cells A1:A500. In B there are 2,000 names so B1:B2000.

I just need to produce a third column, C, which contains names wich appear in both A and B. Any thoughts? (Formula appriciated). Ta

 

[E. Buygum]

Tried this but couldn't get any sense out of it. Have simplified things a little. I have two columns of names: A and B. In A there are 500 names so cells A1:A500. In B there are 2,000 names so B1:B2000.

I just need to produce a third column, C, which contains names wich appear in both A and B. Any thoughts? (Formula appriciated). Ta

Sorted in a fairly crude way: C1=COUNTIF($B$1:$B$2000,A1) leaves a 1 in C1 if there's a match. Ta.

 

[Gaz]

If programming isn't your thing ...

http://www.xlcomparator.net/

or

http://en.kioskea.net/forum/affich-69962-comparing-two-columns-of-names-in-excel

 

[Clive]

Tried this but couldn't get any sense out of it. Have simplified things a little. I have two columns of names: A and B. In A there are 500 names so cells A1:A500. In B there are 2,000 names so B1:B2000.

I just need to produce a third column, C, which contains names wich appear in both A and B. Any thoughts? (Formula appriciated). Ta

Something like...
In Sheet3.A1 enter
=LOOKUP(Sheet2!A1,Sheet1!$A$1:$A$500)
and copy that to sheet3.A2 thru A2000

 

[GS_or_bust]

Lookup requires the lookup table to be sorted and always returns a value, try

=IF(ISNA(MATCH(B1,A$1:A$500,0)),"",B1)

Copy the formula to C1 then replicate it all the way down to C2000

 

[repomanbe]

who needs Google, just come here to get your IT questions answered

 

[E. Buygum]

who needs Google, just come here to get your IT questions answered

Did try for an hour on Google..answers either didn't work or I couldn't understand them...the range of skills and knowledge on this site never fails to amaze me...there's the occasional useful post on bikes, too

 

[E. Buygum]

Lookup requires the lookup table to be sorted and always returns a value, try

=IF(ISNA(MATCH(B1,A$1:A$500,0)),"",B1)

Copy the formula to C1 then replicate it all the way down to C2000

Don't understand it but that did the trick, Thank you.

 

[markoh]

Does the data have to be in two separate sheets?

 

[Paul Wakefield]

And just to prove there are (usually) multiple ways to get the same result in Excel, you could use Vlookup.

=IFERROR(VLOOKUP(sheet2!A1,Sheet1!$A$1:$A$500,1,FALSE),"")

IFERROR is only available from XL2007 onwards. In earlier versions, use an IF statement (which makes for a slightly longer formula). VLOOKUP is fractionally faster than MATCH (though, on the sizes here, the difference would not be noticeable).

If you want the data in a continuous list without gaps, again there are a number of ways such as:

On sheet 3, in the first column:

=IFERROR(MATCH(Sheet2!A1,Sheet1!$A$1:$A$500,0),"")

In the second column:

=IFERROR(OFFSET(Sheet1!$A$1,SMALL(A1:$A$500,ROW())-1,),"")

Copy both down to row 2000. The second column will give you a gap free list.