intheblack
07-22-2008, 01:15 AM
Hi. this is my first post here, I really need help :-). I've been playing around with solutions to this problem for a while now and have decided to come to a forum to perhaps open my eyes. I've tried formulas (index, Match and the lookup functions) but don't seem to be getting anywhere. Not sure because I'm trying to match both text and numbers?
I'll explain the objective and see what you guys think
The DATA sheet is downloaded from our broker daily and has all the transactions for the day. What I am trying to do is match the data "automatically" with the clients. Up until now I have done it manually line by line, but sometimes over 200 transactions can drive one batty :banghead:
The match sheet is where the client details are entered. What needs to happen is that the cells O & P (unit number and unit price) need to be "pulled/matched" from the DATA sheet. There are 4 possible criteria to match with:
The account # (col A ) on the match sheet with Client No. (Col F) on the DATA sheet
BUY or Sell (Col E) on the match sheet with BUY/SELL (Col J) on the DATA sheet
ASX Code (col G) on the match sheet with STOCK (col K) on the DATA sheet
Client # (Col C) on the match Sheet with ORDER TAG (Col AB) on the DATA sheet
So if all match (all 4 have to match as there may be many buys/sells of the same stock)
QTY (Col L) from data sheet should be placed/filled in Unit Number (Col O) of the match sheet.
and
AVG PRICE (col M) from the data sheet should be placed/filled in Unit Price (Col P) of the match sheet.
I hope that makes some sense. I have attached a sample spreadsheet to try and give you a better understanding of what I am looking for.
Does anyone have a solution?
I'll explain the objective and see what you guys think
The DATA sheet is downloaded from our broker daily and has all the transactions for the day. What I am trying to do is match the data "automatically" with the clients. Up until now I have done it manually line by line, but sometimes over 200 transactions can drive one batty :banghead:
The match sheet is where the client details are entered. What needs to happen is that the cells O & P (unit number and unit price) need to be "pulled/matched" from the DATA sheet. There are 4 possible criteria to match with:
The account # (col A ) on the match sheet with Client No. (Col F) on the DATA sheet
BUY or Sell (Col E) on the match sheet with BUY/SELL (Col J) on the DATA sheet
ASX Code (col G) on the match sheet with STOCK (col K) on the DATA sheet
Client # (Col C) on the match Sheet with ORDER TAG (Col AB) on the DATA sheet
So if all match (all 4 have to match as there may be many buys/sells of the same stock)
QTY (Col L) from data sheet should be placed/filled in Unit Number (Col O) of the match sheet.
and
AVG PRICE (col M) from the data sheet should be placed/filled in Unit Price (Col P) of the match sheet.
I hope that makes some sense. I have attached a sample spreadsheet to try and give you a better understanding of what I am looking for.
Does anyone have a solution?