Hi everyone!
Im trying to put together an expense tracker in Excel. The intention is to take an extract from online banking on a monthly basis which would give me a list of all my expenses. The extract includes the date the expense was incurred, a description of the expense, the amount incurred. I have then put a column next to these which indicates the categorisation of the expense, e.g. Utilities something like this:
<table>
<tr>
<th>Date incurred</th>
<th>Expense Amount</th>
<th>Expense Description</th>
<th>Category</th>
</tr>
<tr>
<td>2019/05/31</td>
<td>$xx</td>
<td>Direct Debit 0015928 Verizon CR CARD PMNT 934730506</td>
<td>Utilities - Internet</td>
</tr>
<tr>
<td>2019/05/31</td>
<td>$xx</td>
<td>[Supermarket name] J364 [Suburb]</td>
<td>Groceries</td>
</tr>
</table>
So heres the problem Im having: ideally Id like the categorisation to auto populate based on a separate table Im working on which basically has a unique term which appears in somewhere in the expense description with a second column providing a categorisation. The other table's format presently looks like this:
<table>
<tr>
<th>Search Term</th>
<th>Description</th>
<th>Category</th>
</tr>
<tr>
<td>Verizon</td>
<td>Verizon home internet bill</td>
<td>Utilities - Internet</td>
</tr>
</table>
Given that the merchants decide on the line item content and format, the placement of the vendor name is not often consistent. Also, some stores etc. tend to have the store name in the expense but might have other numbers etc. in the expense description to indicate the specific store location. Ideally I'd like to avoid having to do time-intensive pre-processing of the extract each month, so I want to take check if anything in my reference table has an exact partial match with each line item in the online bank extract. I've tried messing around with variants of LOOKUP and VLOOKUP, but using these results in the formula taking its best guess (which will always be wrong) in cases where the vendor has been seen before and isn't already on the list. Ideally, in these cases, I'd want the formula to inform me that no exact partial match was found which will allow me to manually add it to the table for future lookups.
Any ideas, guys? This one has me pretty stumped short of pulling out VB and working on some macros.
Thanks so much for your help!
Im trying to put together an expense tracker in Excel. The intention is to take an extract from online banking on a monthly basis which would give me a list of all my expenses. The extract includes the date the expense was incurred, a description of the expense, the amount incurred. I have then put a column next to these which indicates the categorisation of the expense, e.g. Utilities something like this:
<table>
<tr>
<th>Date incurred</th>
<th>Expense Amount</th>
<th>Expense Description</th>
<th>Category</th>
</tr>
<tr>
<td>2019/05/31</td>
<td>$xx</td>
<td>Direct Debit 0015928 Verizon CR CARD PMNT 934730506</td>
<td>Utilities - Internet</td>
</tr>
<tr>
<td>2019/05/31</td>
<td>$xx</td>
<td>[Supermarket name] J364 [Suburb]</td>
<td>Groceries</td>
</tr>
</table>
So heres the problem Im having: ideally Id like the categorisation to auto populate based on a separate table Im working on which basically has a unique term which appears in somewhere in the expense description with a second column providing a categorisation. The other table's format presently looks like this:
<table>
<tr>
<th>Search Term</th>
<th>Description</th>
<th>Category</th>
</tr>
<tr>
<td>Verizon</td>
<td>Verizon home internet bill</td>
<td>Utilities - Internet</td>
</tr>
</table>
Given that the merchants decide on the line item content and format, the placement of the vendor name is not often consistent. Also, some stores etc. tend to have the store name in the expense but might have other numbers etc. in the expense description to indicate the specific store location. Ideally I'd like to avoid having to do time-intensive pre-processing of the extract each month, so I want to take check if anything in my reference table has an exact partial match with each line item in the online bank extract. I've tried messing around with variants of LOOKUP and VLOOKUP, but using these results in the formula taking its best guess (which will always be wrong) in cases where the vendor has been seen before and isn't already on the list. Ideally, in these cases, I'd want the formula to inform me that no exact partial match was found which will allow me to manually add it to the table for future lookups.
Any ideas, guys? This one has me pretty stumped short of pulling out VB and working on some macros.
Thanks so much for your help!
