Searching tables for similar entries

  • Thread starter Thread starter cablesg
  • Start date Start date
C

cablesg

Hi, I have this customer table and a transaction table on another file.
Is it possible to search the transaction table and calculate values on
the transaction table and put it in the customer table?

e.g
Customer table
--------------------
customer name accountno
abc ltd 1238
bcd corp 3456

Trans table
--------------------
accountno price qty
1234 00* 10 5
3456 00* 20 1
1234 00* 2 3

*The file i received have this formating 1234<afewspace>00 and I was
unable to use vlookup to match the tables. Thanks in advance.
 
Hi
please some more details :-)
- Is your account number always 4 digits
- do you want all information transferred (price, qty)

If yes you may try the following formula in your customer table
=INDEX('trans'!$B$1:$B$100,MATCH($B1,LEFT('trans'!$A$1:$A$100,4),0))
and enter this as array formula with CTRL+SHIFT+ENTER
 
HI Frank, the account number are random, not limited to 4 digits. I need
the price and qty to compute total value of transactions by the
customer.

Michael
 
Hi Michael
try adding a helper column in your 'Trans' table. e.g. in column D
enter hte following formula in D1
=--LEFT(A1,FIND(" ",A1)-1)
and copy this down
assumptions:
- account number always starts at the beginning
- account number is a number

After this you can use the following formula on your customer table to
compute the total value of transactions
=SUMPRODUCT(--('trans'!$D$1:$D$100=B1),'trans'!$B$B100,'trans'!$C$1:$C$
100)
and copy this down

you may also consider using a pivot table for this. See:
http://www.cpearson.com/excel/pivots.htm
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html
http://www.ozgrid.com/Excel/excel-pivot-tables.htm
 
Back
Top