I'm writing a spreadsheet for my finances and need a little help. I have a sheet of transactions and want to be able to automate the account that each transaction is assigned to via a set of rules on another sheet.
My transactions for example look like this: 1/04/16 -5.9 TEA'S ME accnum 1/04/16 -8.5 CAFE 101 accnum 1/04/16 -4.8 HOT WOK accnum I want to be able to have a set of rules like this in a lookup table: Backblaze 275 Countdown 300 Dropbox 275 Hot 300 Z 387 Pizzahut 300 Graham 184 Where if my transaction name contains something from the lookup table, the account number is looked up and placed in the cell where accnum currently is. I've tried using a vlookup function like so, but to no avail. =IF(C2=', ', VLOOKUP('.' &C20&'.' ,'Chart Rules'!$A$2:$C$1001,2,0)) I realise that the above function does a fuzzy match on the lookup value, not the table that it's looking for the values in. I'd appreciate any suggestions in how to do this! Here is a VBA solution which implements a rather weak fuzzy lookup.
Re: Removing partial duplicates in a large data set in excel for mac Can we assume you don't have addresses or zip codes or other info that can help ID duplicates? Clicking the Add Reputation star below any helpful posts is a great way to show your appreciation.
Maybe it will work on a Mac, maybe not (I've had mixed experiences when a colleague who has a Mac has tried to run some of my programs): Function FLOOKUP(pat As String, arr As Variant, ColNum As Long, Optional CaseSensitive = True) As Variant 'does a linear search of first column of array or range arr until it finds a 'string which is a fuzzy match for pat, returning the corresponding 'entry in column ColNum of arr. If no match is found NA is returned Dim A As Variant, i As Long, s As String, p As String, pStar As String p = IIf(CaseSensitive, pat, UCase(pat)) pStar = '.' & p & '.'
If TypeName(arr) = 'Range' Then A = arr.Value Else A = arr End If For i = LBound(A) To UBound(A) s = A(i, 1) If Not CaseSensitive Then s = UCase(s) If p Like '.' & s & '.' Or s Like pStar Then FLOOKUP = A(i, ColNum) Exit Function End If Next i FLOOKUP = CVErr(xlErrNA) End Function In this code s is considered to be a fuzzy match of t if and only if either s Like '.'
& t & '.' or t Like '.' & s & '.' . In other words, if either s is a substring of t or vice-versa. If Excel VBA for the Mac doesn't know about xlErrNA, experimentation suggests that this is just 2042. You could always return e.g.
False rather than an error code if no match is found. The linear nature of the search implies that it probably won't scale very well, but perhaps it will work for your application.