I have a somewhat similar question as the previous user. I have two lists of
companies that is sorted by an ID and Company name and volumes of a product;
however, one list has 2000 companies and the other list has 300, I want to
combine the two worksheets into one master worksheet that combines all the
data but, for the same company it only shows once. This is what I have right
now

Workskeet A Worksheet B
Name ID Product 1 Name ID Product 2
ABC 1 25 ABC 1 12
BBC 2 23 BBC 2 14
DDD 3 21

This is what I want

Worksheet A
Name ID Product 1 Product 2
ABC 1 25 12
BBC 2 23 14
DDD 3 21 0

Can someone help me with this?

Say your List is in A2 to C100 on *both* sheets.

Enter this in D2 of SheetA:

=IF(ISNA(MATCH(A2,SheetB!$A$2:$A$100,0)),0,INDEX(S heetB!$C$2:$C$100,MATCH(A2
,SheetB!$A$2:$A$100,0)))

And copy down.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"worduser" wrote in message
...
I have a somewhat similar question as the previous user. I have two lists
of
companies that is sorted by an ID and Company name and volumes of a
product;
however, one list has 2000 companies and the other list has 300, I want to
combine the two worksheets into one master worksheet that combines all the
data but, for the same company it only shows once. This is what I have
right
now

Workskeet A Worksheet B
Name ID Product 1 Name ID Product 2
ABC 1 25 ABC 1 12
BBC 2 23 BBC 2 14
DDD 3 21

This is what I want

Worksheet A
Name ID Product 1 Product 2
ABC 1 25 12
BBC 2 23 14
DDD 3 21 0

Can someone help me with this?

Add the heading "Product 2" to C1 of Worksheet A, and in C2 enter this
formula:

=IF(ISNA(VLOOKUP(A2,'Worksheet B'!A$2:C$300,3,0),0,
VLOOKUP(A2,'Worksheet B'!A$2:C$300,3,0))

(all one formula). Then copy down to C2000 by double clicking the fill
handle with C2 selected (the fill handle is the small black square in
the bottom right corner of the cursor).

Hope this helps.

Pete

Just a further question, does Worksheet A contain all the companies
listed in Worksheet B, as in B is a subset of A, or does B have
companies that A does not have.

If all the companies in Worksheet B are in A, you could use this
VLOOKUP Function.

Just create a heading Product 2 in D1, and and in D2 use this formula

=IF(ISNA(VLOOKUP(A2,Sheet2!$A$2:$C$3,3,FALSE)),0,V LOOKUP(A2,Sheet2!$A$2:$C$3,3,FALSE))

replace the 0 with any null value you need, like "Nil" or something
else.

Drag down the formula to all the companies to achieve the desired
result.


--
KellTainer
------------------------------------------------------------------------
KellTainer's Profile: http://www.excelforum.com/member.php...o&userid=34322
View this thread: http://www.excelforum.com/showthread...hreadid=546442

Yes, all the companies in B are in A, but I tried your formula with no luck.
Maybe I should be more specific.

I have two worksheet with the following numbers and headings:

Cheques (I renamed this worksheet)
Current Account Name CIS Code SIC Code Chq Volume
There are 2746 rows
4 columns

Cert Cheques (renamed)
Currenct Account Name CIS Code SIC Code Cert Chq Vol
There are 1093 rows
4 columns

I would like to organize them into 1 work sheet by CIS code, so that I can
see each company with the same CIS code of there cheque and certified cheque
volumes.

"KellTainer" wrote:


Just a further question, does Worksheet A contain all the companies
listed in Worksheet B, as in B is a subset of A, or does B have
companies that A does not have.

If all the companies in Worksheet B are in A, you could use this
VLOOKUP Function.

Just create a heading Product 2 in D1, and and in D2 use this formula

=IF(ISNA(VLOOKUP(A2,Sheet2!$A$2:$C$3,3,FALSE)),0,V LOOKUP(A2,Sheet2!$A$2:$C$3,3,FALSE))

replace the 0 with any null value you need, like "Nil" or something
else.

Drag down the formula to all the companies to achieve the desired
result.


--
KellTainer
------------------------------------------------------------------------
KellTainer's Profile: http://www.excelforum.com/member.php...o&userid=34322
View this thread: http://www.excelforum.com/showthread...hreadid=546442

This time-lag between posts being visible on the "excelforum" site can be
exasperating ! ! !
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"worduser" wrote in message
...
Yes, all the companies in B are in A, but I tried your formula with no
luck.
Maybe I should be more specific.

I have two worksheet with the following numbers and headings:

Cheques (I renamed this worksheet)
Current Account Name CIS Code SIC Code Chq Volume
There are 2746 rows
4 columns

Cert Cheques (renamed)
Currenct Account Name CIS Code SIC Code Cert Chq Vol
There are 1093 rows
4 columns

I would like to organize them into 1 work sheet by CIS code, so that I can
see each company with the same CIS code of there cheque and certified
cheque
volumes.

"KellTainer" wrote:


Just a further question, does Worksheet A contain all the companies
listed in Worksheet B, as in B is a subset of A, or does B have
companies that A does not have.

If all the companies in Worksheet B are in A, you could use this
VLOOKUP Function.

Just create a heading Product 2 in D1, and and in D2 use this formula


=IF(ISNA(VLOOKUP(A2,Sheet2!$A$2:$C$3,3,FALSE)),0,V LOOKUP(A2,Sheet2!$A$2:$C$3
,3,FALSE))

replace the 0 with any null value you need, like "Nil" or something
else.

Drag down the formula to all the companies to achieve the desired
result.


--
KellTainer
------------------------------------------------------------------------
KellTainer's Profile:
http://www.excelforum.com/member.php...o&userid=34322
View this thread:
http://www.excelforum.com/showthread...hreadid=546442