Excel 2007 - Looking to analyse raw bank data

Didn't find your answer?

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.

avatar
By JKnight
11th Nov 2016 12:51

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.

Thanks (1)
avatar
By Manchester_man
11th Nov 2016 12:54

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.

Thanks (1)
avatar
By Manchester_man
11th Nov 2016 13:03

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.

Thanks (3)
avatar
By paulwakefield1
11th Nov 2016 16:30

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)

Thanks (1)
avatar
By mjaaccounting
11th Nov 2016 17:10

Thanks for all of your suggestions, I will look at separating the data and using the index and match functions. Am thinking of new investment in IT for the new year so looking forward to the new Excel functions.

Thanks (0)
Brunel
By Brunel
11th Nov 2016 18:13

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.

Thanks (1)
FD4Cast
By James Power
24th Nov 2016 12:44

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

Thanks (1)
Replying to FD4Cast:
avatar
By mjaaccounting
25th Nov 2016 13:49

James, thank you for taking the time to look at this.

I am not sure exactly what sorcery you have got going on there but it certainly works.

I am intrigued and am starting a free VBA course forthwith!

Thanks (0)
Replying to mjaaccounting:
avatar
By FD4CAST FD4CAST
25th Nov 2016 14:48

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.

Thanks (0)
avatar
By ellzbellz
24th Nov 2016 12:18

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?

Thanks (1)