return value if cell contains specific text

[little jimmy]

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.

 

[tyija1995]

Hey,

How about COUNTIF using wildcards?

E.g. =COUNTIF($A$1:$A$10,"*BMW*") will return how many cells in the range A1:A10 that CONTAIN "BMW"

 

[little jimmy]

Sorry but that does not give me the result I need.

 

[tyija1995]

OK,

Is the reference number always numeric? I.e. =ISNUMBER(references) always returns TRUE?

 

[DataBlake]

try

=IF(ISNUMBER(SEARCH("bmw",A2)),B2,"")

how many narratives are there?
and do you want to search for the narratives at the same time?

 

[little jimmy]

We get about 150 statement lines every day, each is a payment from a customer. The list of key words is 2000+, one for each of our 2000+customers.

I need to see if any of the 2000+ key words appears in the statement narrative, if it does then I need to return a customer reference associated with that key word.

So for line 1 on the statement I need to look at the 2000+key words and see if any of them appear in the narrative (and return the relevant cust ref if it does)
Then for line 2 on the statement I need to look at the 2000+key words and see if any of them appear in the narrative (and return the relevant cust ref if it does)
then for line 3 on the statement I need to look at the 2000+key words and see if any of them appear in the narrative (and return the relevant cust ref if it does)
and so on...

If there is a way to add screen shots or an attachment that might help as they say a picture is worth a thousand words.

 

[DataBlake]

If there is a way to add screen shots or an attachment that might help as they say a picture is worth a thousand words.

yes i think i get the idea but you can use HTML Maker and other tools to attach what you're talking about.
but as i understand you need an array formula that says if ANY of these 2000+ keywords are found, return the value for column B (the customer ref number)? I would suggest these things:

1. make the keywords a named range on another sheet
2. post your screenshot of what you currently have
3. post your screenshot of what you expect it to look like

& then i'll make a formula that meets what you need, but i don't want to start and it end up being not what you're explicitly asking for.

 

[tyija1995]

I believe it can be done with SUMPRODUCT if the cust ref number is numerical only, otherwise a different approach is required.

Are the reference numbers numerical or alphanumerical?

[TABLE="class: grid, width: 750"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Invoice for Vauxhall[/TD]
[TD]123[/TD]
[TD][/TD]
[TD]BMW[/TD]
[TD]1738[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Audi Repair[/TD]
[TD]9215[/TD]
[TD][/TD]
[TD]Audi[/TD]
[TD]9215[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]BMW Repairs[/TD]
[TD]1738[/TD]
[TD][/TD]
[TD]Jaguar[/TD]
[TD]1983[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Jaguar Repairs[/TD]
[TD]1983[/TD]
[TD][/TD]
[TD]Vauxhall[/TD]
[TD]123[/TD]
[/TR]
</tbody>[/TABLE]


If numerical you can get this using formula in B1 and dragged down:

SUMPRODUCT((ISNUMBER((SEARCH($D$1:$D$4,A1))))*($E$1:$E$4))

 

[little jimmy]

Apologies for the length of the reply before we begin.

I have created some sample data below. Each table is on a different tab in the same workbook.
The first table shows the list of key words and the corresponding customer reference. I have named the range A1:B9 as "key".

[TABLE="width: 200"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Key word[/TD]
[TD]Cust ref[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Alfa[/TD]
[TD]CUS-001[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Aston[/TD]
[TD]CUS-002[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]BMW[/TD]
[TD]CUS-003[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Ford[/TD]
[TD]CUS-004[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Honda[/TD]
[TD]CUS-005[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Mercedes[/TD]
[TD]CUS-006[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Nissan[/TD]
[TD]CUS-007[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Vauxhall[/TD]
[TD]CUS-008[/TD]
[/TR]
</tbody>[/TABLE]

The second table shows the narrative that appears on the bank statement (column A).

The formula needs to go in column B and I have put the answer I would expect to receive in this column. Any blanks are where there is no match to a key word (ie there is no "A Romero", no "Merc" but there is "Mercedes", no "vaux" but there is "vauxhall", no "Nisan")



[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Statement narrative[/TD]
[TD]Customer ref[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]BMW motors uk ltd[/TD]
[TD]CUS-003[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Ford motor company[/TD]
[TD]CUS-004[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]general motors (vauxhall)[/TD]
[TD]CUS-008[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Nissan (Feb invoice)[/TD]
[TD]CUS-007[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Aston Martin Motor co[/TD]
[TD]CUS-002[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]A Romeo[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Merc Benz[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Ford (inv ref 5927)[/TD]
[TD]CUS-004[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Nissan Motors[/TD]
[TD]CUS-007[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]GM Motors (vaux)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]BMW uk[/TD]
[TD]CUS-003[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]Nissan motors[/TD]
[TD]CUS-007[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]Mercedes Benz ref 2330d[/TD]
[TD]CUS-006[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]Ford (USA) Inc[/TD]
[TD]CUS-004[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]Vauxhall vehicle sales[/TD]
[TD]CUS-008[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]Honda - Japan[/TD]
[TD]CUS-005[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]Ford (inv ref 3009)[/TD]
[TD]CUS-004[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]Nisan motors[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]BMW (July 2019)[/TD]
[TD]CUS-003[/TD]
[/TR]
</tbody>[/TABLE]


The formula would read something like:
lookup the statement narrative in cell A1 (BMW motors uk Ltd), if any of the key words in the named range "key" appear in cell A1 (BMW does appear), then return the cust ref for that key word (in this case CUS-003).

the next line would read
lookup the statement narrative in cell A2 (Ford motor company), if any of the key words in the named range "key" appear in cell A2 (Ford does appear), then return the cust ref for that key word (in this case CUS-004).

 

[little jimmy]

I'm not sure if I would be able to use an INDEX and MATCH formula to do this within an array?