<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
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
