Hi there,
I have made a small mock-up of a conception challenge I can't seem to solve:
* each month I want to append bank/credit card data in the left table
* I then potentially update the righ-hand mapping table with keywords and the corresponding categories and sub
* pending the start and end date I pick I would like to get a report shown in the middle, that shows spending by category and sub
* it would be great to also have another table, that lists transactions, that have not been categorised, which allows the user to create more mappings to classify as much as possible
I played around with search, byrow etc. but cant seem to get this properly working.
any help would be greatly appreciated.
Thanks very much.
I have made a small mock-up of a conception challenge I can't seem to solve:
* each month I want to append bank/credit card data in the left table
* I then potentially update the righ-hand mapping table with keywords and the corresponding categories and sub
* pending the start and end date I pick I would like to get a report shown in the middle, that shows spending by category and sub
* it would be great to also have another table, that lists transactions, that have not been categorised, which allows the user to create more mappings to classify as much as possible
I played around with search, byrow etc. but cant seem to get this properly working.
test.xlsx | |||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
AI | AJ | AK | AL | AM | AN | AO | AP | AQ | AR | AS | AT | AU | |||
3 | from: | 12/1/24 | to: | 12/31/24 | |||||||||||
4 | |||||||||||||||
5 | Bank Transaction | Desired Report | Mapping Table | ||||||||||||
6 | Date | Description | Amount | Date | Category | Sub | Amount | Keyword | Category | Sub | |||||
7 | 12/12/24 | bla bla petroleum | 125 | Dec-24 | Car | Fuel | 300 | petroleum | Car | Fuel | |||||
8 | 12/15/24 | bla bla pet | 124 | Dec-24 | Living | Clothing | 640 | pet | Pets | pet food | |||||
9 | 12/16/24 | bla bla David Jones | 425 | David Jones | Living | Clothing | |||||||||
10 | 12/22/24 | bla bla petroleum | 175 | Zara | Living | Clothing | |||||||||
11 | 12/26/24 | bla bla Zara | 215 | ||||||||||||
Expenses |
Cell Formulas | ||
---|---|---|
Range | Formula | |
AP7 | AP7 | =+AK7+AK10 |
AP8 | AP8 | =+AK9+AK11 |
any help would be greatly appreciated.
Thanks very much.