sorting help

[Guest]

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?

 

[Ragdyer]

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(SheetB!$C$2:$C$100,MATCH(A2
,SheetB!$A$2:$A$100,0)))

And copy down.

 

[Pete_UK]

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

 

[KellTainer]

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

If all the companies in Worksheet B are in A, you could use thi
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,VLOOKUP(A2,Sheet2!$A$2:$C$3,3,FALSE))

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

Drag down the formula to all the companies to achieve the desire
result

 

[Guest]

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.

 

[Ragdyer]

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