if ? ?

  • Thread starter Thread starter TQ
  • Start date Start date
T

TQ

Hi, anyone know the formula for below situation ?

1.I got two excel file => 1.xls & 2.xls
2.both of the file in column A have the customer name,but 2.xls got more few
customers name tat 1..xls dont have.
3.both of the file in column B have the price.

Now I wish to filter the 2.xls column A and leave those name which exactly
same with 1.xls column A,n i wish to get the price from 2.xls's(column B)
after filter's name, and let the answer shown in 1.xls coloumn c for
comparison.

Anybody can help ?

Thanks !
 
Assume that in 1.xls,
Sheet1 is the sheet with the cust/price data in cols A & B, from row2 down

To simplify matters, copy over the corresponding sheet in 2.xls over to
1.xls, rename the sheet as: Sheet2

In Sheet2,
Put in C2:
=IF(A2="","",ISNUMBER(MATCH(A2,Sheet1!A:A,0)))
Copy down to the last row of data in col A.
Do an autofilter on col C for TRUE.

The above would answer your 1st Q:
.. I wish to filter the 2.xls column A and
leave those name which exactly same with 1.xls column A
Click to expand...

In Sheet1,
Put in C2:
=IF(ISNA(MATCH(A2,Sheet2!A:A,0)),"",INDEX(Sheet2!B:B,MATCH(A2,Sheet2!A:A,0)))
Copy down to the last row of data in col A.

The above should answer your 2nd Q:
.. i wish to get the price from 2.xls's (column B) ..
and let the answer shown in 1.xls colomn c for comparison.
Click to expand...
(you actually don't need to do your 1st Q to arrive at your 2nd Q's results)

---
 
Back
Top