I am using Excel 2007 version to try and analyse some data by looking for key words or characters and then return a value contained in the adjacent cells of the key words. So as an example cells A1:C3 contain:
Salary Bank Payment Wages Control
Adobe Supplier Payment AD001
Cash Transfer Petty Cash Account
Ideally the next cell will be in another worksheet but it could be on the same sheet if required.
So lets say in cell A5 is the text "Cash LonVic ATM 08.32"
What I would like to do is have a formula in Cells B5 & C5 that would scan the text for any key words (in this case "Cash" and return "Transfer" and "Petty Cash Account" in cells B5 & C5.
I have looked at the Vlookup function but that is not sufficient in this case.
I may be trying to reinvent the wheel here but if it is possible I could see some time saving being achieved when working with raw bank data.
Any help would be much appreciated.
Replies (10)
Please login or register to join the discussion.
Have you tried the Data> Text to Columns feature in Excel? This would spread the contains of A5 over four columns. And using the 'fixed width' file type the number of output columns can be modified to two, one for text and one for the number.
I think your answer could be found by tweaking =IF(ISNUMBER(SEARCH("Cash",A2)),"Transfer", "Other")
This formula combines IF with Search.
No doubt somebody will be along to expand.
With bank CSV files I usually use text to columns to get rid of cell bloat, then sort into alphabetical order, which groups all items of the same kind all together. Then I add a column and call this "account". Then I add an appropriate account for each grouped transaction type (I. E. Telephone for all the BT payments. Then I insert a pivot table and the bank analysis is complete.
Before sorting into alphabetical order, I add a "sort" column and number each row starting at 1, then, after sorting, if you need to move back to the original order, you can simply sort by this column.
Shame it's Excel 2007 and not a later version! It's crying out for Power Query (aka "Get and transform").
The following all assume that the keyword is unioque.
I would set up your options as in A1 to C3 as a table with headings (called, say, "OptionsTable"), split the text into columns as suggested by others and then use Index and Match along the lines of =Index(OptionsTable,Match(keyword,OptionsTableColumn1,0),2) and ",3)" as appropriate*.
You may wish to make the second term a Match operation as well rather than hard wiring a number.
An alternative could be to use OFFSET and MATCH which makes for a shorter formula but I wouldn't favour it as, amongst other reasons, it is a volatile function and can be quite demanding of the processor if there are a lot of transactions.
VLOOKUP should also work.
If you don't use the Text to data feature and if the keyword is always the first and single word in the narrative, you could extract it with =LEFT(narrative reference,FIND(" ",narrative reference)-1)
Use Excel VBA with a regular expression:
http://stackoverflow.com/questions/22542834/how-to-use-regular-expressio...
Creating a custom VBA formula would be inefficient for this task, a macro would be better.
Sub AnalyseData()
'======================================================================
'Author: FD4Cast Mob: 07967 883568 E: [email protected]
'======================================================================
Dim rngTransactions As Range
Dim rngTemp As Range
Dim strTemp As String
' Enable Error Handler
On Error GoTo Err_Handler
With ActiveSheet
Set rngTransactions = .Range(.Cells(1, 1), .Cells(.UsedRange.Rows.Count, 1))
For Each rngTemp In rngTransactions
strTemp = rngTemp.Value
Select Case True
Case InStr(1, strTemp, "Salary") <> 0
rngTemp.Offset(0, 1).Value = "Bank Payment"
rngTemp.Offset(0, 2).Value = "Wages Control"
Case InStr(1, strTemp, "Adobe") <> 0
rngTemp.Offset(0, 1).Value = "Supplier Payment"
rngTemp.Offset(0, 2).Value = "AD001"
Case InStr(1, strTemp, "Cash") <> 0
rngTemp.Offset(0, 1).Value = "Transfer"
rngTemp.Offset(0, 2).Value = "Petty Cash"
End Select
Next rngTemp
.Columns("A:C").EntireColumn.AutoFit
End With
Call MsgBox("Procedure Finished Successfully!", vbOKOnly + vbInformation, "Analyse Data")
Exit_Proc:
'===============================================================================
On Error Resume Next
Set rngTemp = Nothing
Set rngTransactions = Nothing
'===============================================================================
Exit Sub
Err_Handler:
Call MsgBox("Error - Speak to Technical Support", vbOKOnly + vbCritical, "Technical Support")
Stop
Resume
End Sub
Happy to customise further for you if you wish, but essentially you just need to add new 'Case' statements for each type of transaction you need to analyse. Ideally I would build a table/ matrix / look-up list in an 'Admin' sheet in Excel with a list of string search options that you can look for which return the analysed values. That way you just add the new values to the table rather than having to adjust the VBA code every time.
Hi,
I think you may need an If statement...so the formula would be 'if in cell a5 there is cash, give me transfer and petty cash; if not give me zero'...would that be sufficient for you?