Hi,
over the last few days, I have been working on assigning categories to transactions based on keywords in a keyword table. Overall, this is working through previous posts and help.
I now have a new challenge, I can't seem to work out and any help would be great.
* Sumif, SumIFS, and ByRow spill formulas in row 22 assign categories to transactions if they are found. I have 3 formulas, to learn new skills and better understand arrays.
* Currently, all transaction table rows are mapped through keywords.
* If one deletes the word Myer in the transaction table (last row 18 in transaction list), $99.50 will not be mapped. Consequently, the total of Sumif, Sumifs and byRow will go down by $99.50, which is correct.
Question:
* How do I create a spill formula to list only non-mapped transactions? This would allow a user to go through unmapped items and add a row to the mapping table, which would resolve the issue.
* I tried to add <> to the existing formulas and other attempts, but cant get it to work.
Note: don't worry about grouping etc. in the live data, this is all included so for this post, I'm just after a spill for unmapped transactions
Thanks for any help.
over the last few days, I have been working on assigning categories to transactions based on keywords in a keyword table. Overall, this is working through previous posts and help.
I now have a new challenge, I can't seem to work out and any help would be great.
* Sumif, SumIFS, and ByRow spill formulas in row 22 assign categories to transactions if they are found. I have 3 formulas, to learn new skills and better understand arrays.
* Currently, all transaction table rows are mapped through keywords.
* If one deletes the word Myer in the transaction table (last row 18 in transaction list), $99.50 will not be mapped. Consequently, the total of Sumif, Sumifs and byRow will go down by $99.50, which is correct.
Question:
* How do I create a spill formula to list only non-mapped transactions? This would allow a user to go through unmapped items and add a row to the mapping table, which would resolve the issue.
* I tried to add <> to the existing formulas and other attempts, but cant get it to work.
Note: don't worry about grouping etc. in the live data, this is all included so for this post, I'm just after a spill for unmapped transactions
Thanks for any help.
Test.xlsx | |||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|
AE | AF | AG | AH | AI | AJ | AK | AL | AM | |||
2 | Transactions | Mapping | |||||||||
3 | Transaction | Amount | Keyword | Cat | Sub | ||||||
4 | METRO PETROLEUM AVALON AVALON | 173.17 | Petroleum | Car | Fuel | ||||||
5 | DAVID JONES BURNLEY | -34.95 | Jones | Living | Fashion | ||||||
6 | PETSTOCK BELROSE BELROSE | 152.15 | Petstock | Pet | Pet Food | ||||||
7 | GUZMAN Y GOMEZ SURRY HILLS | 58.50 | Guzman | Living | Food | ||||||
8 | PETSTOCK MONA VALE MONA VALE | 50.95 | Mekong | Living | Food | ||||||
9 | MEKONG MERCHANT 46146 AVALON BEACH | 32.04 | Adobe | Eduction | Technology | ||||||
10 | ADOBE Adobe Systems SAGGART | 43.99 | icafe | Living | Coffee | ||||||
11 | LS ICAFE PTY LTD T/A C MONA VALE | 5.62 | Pocketpizz | Living | Dining Out | ||||||
12 | WWW.POCKETPIZZ* AVALON SYDNEY | 28.80 | WW Metro | Living | Food | ||||||
13 | WW METRO 8380 AVALON AVALON | 195.71 | Hungry | Living | Dining Out | ||||||
14 | HUNGRY JACKS PTY LTD WOOLLOOMOOLOO | 11.85 | Harbourfront | Living | Dining Out | ||||||
15 | HARBOURFRONT SEAFOOD RE THE ROCKS | 449.07 | Coles | Living | Food | ||||||
16 | COLES MONAVALE 884 MONA VALE | 278.06 | Harris | Living | Food | ||||||
17 | HARRIS FARM MARKETS MONA VALE | 206.50 | Myer | Living | Fashion | ||||||
18 | MYER SYDNEY CITY SYDNEY | 99.50 | |||||||||
19 | |||||||||||
20 | 1750.96 | ||||||||||
21 | |||||||||||
22 | SumIF | SumIFS | ByRow | ||||||||
23 | 173.17 | Fuel | 173.17 | Fuel | 173.17 | Fuel | |||||
24 | -34.95 | Fashion | -34.95 | Fashion | -34.95 | Fashion | |||||
25 | 203.1 | Pet Food | 203.1 | Pet Food | 203.1 | Pet Food | |||||
26 | 58.5 | Food | 58.5 | Food | 58.5 | Food | |||||
27 | 32.04 | Food | 32.04 | Food | 32.04 | Food | |||||
28 | 43.99 | Technology | 43.99 | Technology | 43.99 | Technology | |||||
29 | 5.62 | Coffee | 5.62 | Coffee | 5.62 | Coffee | |||||
30 | 28.8 | Dining Out | 28.8 | Dining Out | 28.8 | Dining Out | |||||
31 | 195.71 | Food | 195.71 | Food | 195.71 | Food | |||||
32 | 11.85 | Dining Out | 11.85 | Dining Out | 11.85 | Dining Out | |||||
33 | 449.07 | Dining Out | 449.07 | Dining Out | 449.07 | Dining Out | |||||
34 | 278.06 | Food | 278.06 | Food | 278.06 | Food | |||||
35 | 206.5 | Food | 206.5 | Food | 206.5 | Food | |||||
36 | 99.5 | Fashion | 99.5 | Fashion | 99.5 | Fashion | |||||
37 | |||||||||||
38 | 1750.96 | 1750.96 | 1750.96 | ||||||||
Expenses |
Cell Formulas | ||
---|---|---|
Range | Formula | |
AF20,AL38,AI38,AF38 | AF20 | =SUM(AF4:AF19) |
AF23:AG36 | AF23 | =LET( k,AJ4:AJ17, sub,AL4:AL17, trans,AE4:AE18, sumcol,AF4:AF18, r,BYROW(k,LAMBDA(keyrow,SUMIF(trans,"*"&keyrow&"*",sumcol))), HSTACK(r,sub) ) |
AI23:AJ36 | AI23 | =LET( k,AJ4:AJ17, sub, AL4:AL17, trans, AE4:AE18, sumcol, AF4:AF18, r,BYROW(k,LAMBDA(keyrow,SUMIFS(sumcol, trans,"*"&keyrow&"*",trans,"*"&keyrow&"*"))), HSTACK(r,sub) ) |
AL23:AM36 | AL23 | =HSTACK(BYROW(AJ4:AJ17,LAMBDA(keyrow,SUMIFS(AF4:AF18, AE4:AE18,"*"&keyrow&"*",AE4:AE18,"*"&keyrow&"*"))),AL4:AL17) |
Dynamic array formulas. |