little jimmy
New Member
- Joined
- Mar 21, 2013
- Messages
- 13
Hi,
I am trying to automate a way of searching through my bank statement and returning a reference number based on the narrative that appears on the statement (sample data attached). We get hundreds of items every day and the narrative that is quoted on the bank statement is always different (often due to different invoice number being quoted), BUT there will always be certain "key" text that is always quoted.
eg BMW will always be quoted, but the statement line may state "BMW motors" or "BMW july" payment or "Invoice for BMW"
I have a list of the "key" text and the relevant reference number that is needed for our Ledger system.
I need a formula that will look at each bank statement line, search the list of key text and return the relevant ref number.
If I were to write out the formula it would read something like "look at the first line on the bank statement (cell A2), if the narrative quoted contains a key word from column A of the the named range, then return the customer ref from column B of the named range into cell C2, if the narrative does not contain a key word then leave C2 blank. Repeat this for every statement line.
I have created a simplified mock up in excel but not sure how to attach a screen shot to this thread.
I have tried using =if(isnumber(search(.... but cannot figure it out.
Many thanks.
I am trying to automate a way of searching through my bank statement and returning a reference number based on the narrative that appears on the statement (sample data attached). We get hundreds of items every day and the narrative that is quoted on the bank statement is always different (often due to different invoice number being quoted), BUT there will always be certain "key" text that is always quoted.
eg BMW will always be quoted, but the statement line may state "BMW motors" or "BMW july" payment or "Invoice for BMW"
I have a list of the "key" text and the relevant reference number that is needed for our Ledger system.
I need a formula that will look at each bank statement line, search the list of key text and return the relevant ref number.
If I were to write out the formula it would read something like "look at the first line on the bank statement (cell A2), if the narrative quoted contains a key word from column A of the the named range, then return the customer ref from column B of the named range into cell C2, if the narrative does not contain a key word then leave C2 blank. Repeat this for every statement line.
I have created a simplified mock up in excel but not sure how to attach a screen shot to this thread.
I have tried using =if(isnumber(search(.... but cannot figure it out.
Many thanks.
