Formula challenge, need clever help!

hz79hc

New Member
Joined
Sep 16, 2009
Messages
27
<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Helvetica Neue'}p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Helvetica Neue'; min-height: 14.0px}</style>MrExcel gurus,
I'm trying to improve a workbook that helps manage spending well enough that it justifies the cost of Office365 for us.
The workbook has worksheets with
one Excel data table of checking account transactions and
a second “dictionary” table full of merchants where we routinely spend.
A “Description” value in the checking transactions table varies quite a bit so I have not been clever enough to make an automatic way to assign the transactions to categories of spending. Doing that manually for each record is no fun!


The workbook is simple:
tblTXN column A holds transaction description but the useful part doesn’t always start in position 1, plus the same merchant’s transactions do not always contain the same identifying characters so some merchants require more than one entry in this table
Column B shows the $ amount.


tblMERCH column A has an ID such as:
Column B shows the full merchant name,
C shows a location or web address, and
D holds the spending category to assign


HELP?
Could a formula be built that would use the text in the Description column to determine which routine merchant (if any) that each row in the checking data matches up with?
The merchant “dictionary” has an identification column that contains a substring commonly found in the checking transactions belonging to that specific merchant. Some merchants have transactions that make it necessary to have more than one dictionary record.


Learning if the transaction involved one of the usual merchants is not the goal. I want to find which merchant and then use a VLOOKUP or INDEX/MATCH or something? to grab the spending column’s value from the dictionary as this new formula’s result. A formula is desirable instead of a VBA or possibly even an array formula because in addition to me, my young adult kids could use & maintain for themselves too without much learning for the only workbook they ever use.

Thanks much for help or even ideas
Warren
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
The merchant “dictionary” has an identification column that contains a substring commonly found in the checking transactions belonging to that specific merchant.
can you give examples

Is a seperate sheet

Special Identification Substring (Column A) = Merchant (Column B)
BANDQ = B&Q
WKS = WICKES
HMBSE = HOMEBASE
JWN = JESONS

ON Another sheet you have transactions

Column A = ID
Column B = Merchant
Column C = Location/web
Column D = Spend

where is this substring , and is it unique

I'm assuming merchant may have
THE HOMEBSE Comp . or THE Homebase Ltd or Homebase or any variations of the name
Where would we find the unique id HMBSE

or have I miss understood
 
Upvote 0
Wayne,
This is probably simpler than I'm making it... The transactions table has entries with Descriptions column--here are some texts that look pretty typical of what is in that column. I am making bold the bit that should match with the entries in the example table of Merchants below. For me, the fact that the part that matches the dictionary entry is not in the same position within the Description text from row-to-row. Even transactions from the same merchant have the important matching text in different positions over time or from different point-of-sale terminals or simply differs from store-to-store.

[TABLE="width: 541"]
<tbody>[TR]
[TD]Wallings Fine C OH Debit Card Withdrawal: MASTERCARD DEBIT[/TD]
[/TR]
[TR]
[TD]APL*ITUNES.COM/BILL 800-275-2273 CA Debit Card Withdrawal[/TD]
[/TR]
[TR]
[TD]ALDI Debit Card Withdrawal: MASTERCARD DEBIT[/TD]
[/TR]
[TR]
[TD]CL MARTINS Debit Card Withdrawal: MASTERCARD DEBIT[/TD]
[/TR]
[TR]
[TD]Galena ATMOS Debit Card Withdrawal: MASTERCARD DEBIT[/TD]
[/TR]
[TR]
[TD]S0322 WAL-MART Debit Card Withdrawal: MASTERCARD DEBIT[/TD]
[/TR]
[TR]
[TD]Walmart 000021004356 Debit Card Withdrawal: MASTERCARD DEBIT[/TD]
[/TR]
[TR]
[TD]TEACHERS MEMBER 1115 N MADISON AVE ATM Withdrawal: #002278[/TD]
[/TR]
</tbody>[/TABLE]

So, the dictionary table has rows identified in column A with values such as these:
[TABLE="width: 135"]
<tbody>[TR]
[TD]ALDI[/TD]
[/TR]
[TR]
[TD]AMAZON[/TD]
[/TR]
[TR]
[TD]AMEX[/TD]
[/TR]
[TR]
[TD]AMZ[/TD]
[/TR]
[TR]
[TD]ATM WITH[/TD]
[/TR]
[TR]
[TD]ATMOS[/TD]
[/TR]
[TR]
[TD]ITUNES[/TD]
[/TR]
[TR]
[TD]MARTINS[/TD]
[/TR]
[TR]
[TD]VERIZON[/TD]
[/TR]
[TR]
[TD]VZ[/TD]
[/TR]
[TR]
[TD]WAL-MART[/TD]
[/TR]
[TR]
[TD]WALL[/TD]
[/TR]
[TR]
[TD]WALMART[/TD]
[/TR]
</tbody>[/TABLE]

I'm using Office365 too.
Thanks!
Warren
 
Upvote 0
So, the text that will help match-up to the Merchant isn't in the same predictable location and there isn't anything else available to help find which merchant row may be the matching one. Further, it may be none will match.

For the don't-match transactions, FALSE or <blank> or N/A would be an acceptable formula result.

For the successful match case, another column in the Merchant table holds the Category that should become the value result of this formula...

Regards,
Warren
 
Upvote 0
where you have for example WALL and VZ - no other wording in a description would contain Wall or VZ
for example if you had
WALMart
OFF the WALL marketing
WALLACE and sons
Wallings Fine C OH Debit Card Withdrawal: MASTERCARD DEBIT

searching for a MATCH using the code WAL will not work

So providing you are 100% sure these are unique

search() is not case sensitive
 
Upvote 0
Hello Wayne, thanks for hanging in there with me on this.
Yes, "search" is a presumed part of a formula solution and if only needing to evaluate transactions for that one merchant, you can get where needed.

However, the issue is that I was hoping there could be a solution that is able to find which merchant if any matches a given record.
If I presume to be looking for "Wal" or whatever, I'll only find the records that have "Wal" within their Description.
Was hoping to find a formula approached that would evaluate the Description and by "accessing" the merchant table, each transaction would be checked against each merchant and the formula's result would indicate which merchant was relevant and, if none, tell me something like FALSE or whatever.
 
Upvote 0
So a VBA approach could step through, one merchant at a time, and each pass through the transactions would evaluate and identify/mark the ones with WAL when that was the vendor in a looping VBA routine and would identify/mark the ones with a different merchant each "pass" or loop through the checking transactions table

Still wondering if a single formula can cause the same net effect.
 
Upvote 0
That a fairly easy things to do, unfortunatly you cannot do it through macro but vba code or a well designed power query or data link.

The vba txt function to identify a string within s string don t exist in macro ( here it s to use str or instr vba code).

Regarding the vba code, i will put later on the reference , i already something similar to create flag on my bank statment.
For the excel 365, well, remember that with the license you got also the desktop version, you can still run the vba through that.
 
Upvote 0
you could use a reverse lookup to find the text in your list , and if it exists in the name

= IFERROR(LOOKUP(1E+100,SEARCH(Sheet1!$A$2:$A$14,sheet2!A1),Sheet1!$A$2:$A$14),””)

your lookup list of values is in Sheet1 A2 to A14

so if Atmos exists in cell A1 - it would return Atmos, ,
However in your example the value in A1 has WALL in and so you get WALL returned - use this as a helper column
now that you have the reference value returned which matches the long text
you can use a normal lookup to lookup the value in the helper column and return the column beside that , which will be your reference name you want to us e

heres a dropbox link to an example sheet
https://www.dropbox.com/s/6hiawe343wtoxwv/LOOKUP_ETAF.xlsx?dl=0
 
Upvote 0

Forum statistics

Threads
1,222,383
Messages
6,165,665
Members
451,983
Latest member
Raph24

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top