Hi,
over last few days I worked on arrays and with help from this forum I could go through bank transactions and assign categories and than group transactions by date, categories and sub-categories. Last help was from @Peter_SSs, who could assist in allowing me to get a table, for any transactions, that were not mapped. I put this all into live data and it works great. So very thankful for everyone's help.
I would now like to add what I call a reverse report for analytics:
* user can pick a sub-category from a drop-down field
* that should list all transactions that were mapped to that sub
I got maybe halfway there. In AP68 is my current status of transactions, based on user entry in cell AQ67 (no dropdown in this mock-up). I want to add to that array in AP68#:
* transaction date
* original description
I tried to add dates etc. but then the result array has endless rows, which has obviously to do with me not fully understanding this.
Thanks for any help.
over last few days I worked on arrays and with help from this forum I could go through bank transactions and assign categories and than group transactions by date, categories and sub-categories. Last help was from @Peter_SSs, who could assist in allowing me to get a table, for any transactions, that were not mapped. I put this all into live data and it works great. So very thankful for everyone's help.
I would now like to add what I call a reverse report for analytics:
* user can pick a sub-category from a drop-down field
* that should list all transactions that were mapped to that sub
I got maybe halfway there. In AP68 is my current status of transactions, based on user entry in cell AQ67 (no dropdown in this mock-up). I want to add to that array in AP68#:
* transaction date
* original description
I tried to add dates etc. but then the result array has endless rows, which has obviously to do with me not fully understanding this.
Thanks for any help.
| Arrays - Find Partial String and Apply Category.xlsx | |||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|
| AO | AP | AQ | AR | AS | AT | AU | AV | AW | |||
| 5 | Transactions | Mapping | |||||||||
| 6 | TransDate | Transaction | Amount | Keyword | Cat | Sub | |||||
| 7 | 2/1/25 | METRO PETROLEUM AVALON AVALON | 173.17 | Petroleum | Car | Fuel | |||||
| 8 | 2/2/25 | DAVID JONES BURNLEY | -34.95 | Jones | Living | Fashion | |||||
| 9 | 2/3/25 | PETSTOCK BELROSE BELROSE | 152.15 | Petstock | Pet | Pet Food | |||||
| 10 | 2/4/25 | GUZMAN Y GOMEZ SURRY HILLS | 58.50 | Guzman | Living | Food | |||||
| 11 | 2/5/25 | PETSTOCK MONA VALE MONA VALE | 50.95 | Mekong | Living | Food | |||||
| 12 | 2/6/25 | MEKONG MERCHANT 46146 AVALON BEACH | 32.04 | Adobe | Eduction | Technology | |||||
| 13 | 2/7/25 | ADOBE Adobe Systems SAGGART | 43.99 | icafe | Living | Coffee | |||||
| 14 | 2/8/25 | LS ICAFE PTY LTD T/A C MONA VALE | 5.62 | Pocketpizz | Living | Dining Out | |||||
| 15 | 2/9/25 | WWW.POCKETPIZZ* AVALON SYDNEY | 28.80 | WW Metro | Living | Food | |||||
| 16 | 2/10/25 | WW METRO 8380 AVALON AVALON | 195.71 | Hungry | Living | Dining Out | |||||
| 17 | 2/11/25 | HUNGRY JACKS PTY LTD WOOLLOOMOOLOO | 11.85 | Harbourfront | Living | Dining Out | |||||
| 18 | 2/12/25 | HARBOURFRONT SEAFOOD RE THE ROCKS | 449.07 | Coles | Living | Food | |||||
| 19 | 2/13/25 | COLES MONAVALE 884 MONA VALE | 278.06 | Harris | Living | Food | |||||
| 20 | 2/14/25 | HARRIS FARM MARKETS MONA VALE | 206.50 | Myer | Living | Fashion | |||||
| 21 | 2/15/25 | SYDNEY CITY SYDNEY | 99.50 | Chambers Cellar | Food | Alcohol | |||||
| 22 | Chargrill | Lifestyle | Restaurants | ||||||||
| 23 | 1750.96 | Chef Rebeka | Lifestyle | Restaurants | |||||||
| 24 | Chemis | Health | Pharmacy | ||||||||
| 25 | Chill Bar | Lifestyle | Restaurants | ||||||||
| 26 | |||||||||||
| 49 | |||||||||||
| 50 | |||||||||||
| 51 | |||||||||||
| 52 | Not Mapped - Option 1 | ||||||||||
| 53 | SYDNEY CITY SYDNEY | 99.5 | |||||||||
| 54 | |||||||||||
| 55 | |||||||||||
| 56 | |||||||||||
| 57 | |||||||||||
| 58 | |||||||||||
| 59 | Not Mapped - Option 2 | ||||||||||
| 60 | SYDNEY CITY SYDNEY | 99.5 | |||||||||
| 61 | |||||||||||
| 62 | |||||||||||
| 63 | |||||||||||
| 64 | |||||||||||
| 65 | |||||||||||
| 66 | |||||||||||
| 67 | Reverse Report | Food | |||||||||
| 68 | 58.5 | Food | |||||||||
| 69 | 32.04 | Food | |||||||||
| 70 | 195.71 | Food | |||||||||
| 71 | 278.06 | Food | |||||||||
| 72 | 206.5 | Food | |||||||||
Expenses | |||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| AQ23 | AQ23 | =SUM(AQ7:AQ22) |
| AP53:AQ53 | AP53 | =FILTER(AP7:AQ21,NOT(BYROW(AP7:AQ21,LAMBDA(r,COUNT(SEARCH(AU7:AU25,r))))),"all mapped") |
| AP60:AQ60 | AP60 | =FILTER(AP7:AQ21,BYROW(AP7:AQ21,LAMBDA(r,COUNT(SEARCH(AU7:AU25,r))=0)),"all mapped") |
| AP68:AQ72 | AP68 | =LET( check,AQ67, fil,FILTER(UNIQUE(AU7:AW25),AW7:AW25=check), k,INDEX(fil,,1), sub, INDEX(fil,,3), trans, AP7:AP21, sumcol, AQ7:AQ21, r,BYROW(k,LAMBDA(keyrow,SUMIFS(sumcol, trans,"*"&keyrow&"*",trans,"*"&keyrow&"*"))), HSTACK(r,sub) ) |
| Dynamic array formulas. | ||
).
