Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Help: Multiple filters with mutually exclusive items
I'm creating a spreadsheet from my online bank statement. All of the
transactions are imported so that the descriptions are in one column, the amounts in another. What I'd like to do is create filters that contain transaction descriptions and amounts broken down into categories. For example: Let's say this is the imported data A B Description Amount Joe's Supermarket $14 Exxon $23 Con Edison $40 Jane's Grocery $19 Mobil $28 Mobil Market $16 Whole Foods $17 I'd like to create filters that copy data to other columns: A B Description Amount *Supermarket* *Grocery* *Market* Description Amount *Exxon* *Mobil* Description Amount *Con Edison* Description Amount Joe's Supermarket $14 Exxon $23 Con Edison $40 Jane's Grocery $19 Mobil $28 Mobil Market $16 Whole Foods $17 When I use an advanced filter to copy the data, the results a C D Food Description Amount Joe's Supermarket $14 Jane's Grocery $19 Mobil Market $16 E F Gas Description Amount Exxon $23 Mobil $28 Mobil Market $16 G H Description Amount Electric Con Edison $40 This would work fine, but there are two problems: 1. The 'Mobil Market' item appears in both the Food and Gas lists. It should not have been available for the Gas filter after being included in the Food filter. 2. The 'Whole Foods' item is really a food item but is missed by the filter criterion. It needs to stand out somehow so it won't be missed. Both of these prevent accurate budgeting. Can anyone offer advice to address these two problems? My appreciation in advance, Joe |
#2
|
|||
|
|||
Change your criteria to be more specific. For example,
*Mobil* should be *Mobil As for items left out, perhaps you could use an extra column of formulas to assign items to various categories, and use "Other" as your default if no other categories have picked it up. HTH, Bernie MS Excel MVP "six50joe" wrote in message ups.com... I'm creating a spreadsheet from my online bank statement. All of the transactions are imported so that the descriptions are in one column, the amounts in another. What I'd like to do is create filters that contain transaction descriptions and amounts broken down into categories. For example: Let's say this is the imported data A B Description Amount Joe's Supermarket $14 Exxon $23 Con Edison $40 Jane's Grocery $19 Mobil $28 Mobil Market $16 Whole Foods $17 I'd like to create filters that copy data to other columns: A B Description Amount *Supermarket* *Grocery* *Market* Description Amount *Exxon* *Mobil* Description Amount *Con Edison* Description Amount Joe's Supermarket $14 Exxon $23 Con Edison $40 Jane's Grocery $19 Mobil $28 Mobil Market $16 Whole Foods $17 When I use an advanced filter to copy the data, the results a C D Food Description Amount Joe's Supermarket $14 Jane's Grocery $19 Mobil Market $16 E F Gas Description Amount Exxon $23 Mobil $28 Mobil Market $16 G H Description Amount Electric Con Edison $40 This would work fine, but there are two problems: 1. The 'Mobil Market' item appears in both the Food and Gas lists. It should not have been available for the Gas filter after being included in the Food filter. 2. The 'Whole Foods' item is really a food item but is missed by the filter criterion. It needs to stand out somehow so it won't be missed. Both of these prevent accurate budgeting. Can anyone offer advice to address these two problems? My appreciation in advance, Joe |
#3
|
|||
|
|||
Thanks for your reply Bernie- VLOOKUP seems to do the trick for using
formulas. In fact, formulas may be a better way to approach my problem altogether than filters. Joe |
#4
|
|||
|
|||
I've encountered one problem trying to use VLOOKUP-
My category lookup table looks something like this: Category Transaction Food Jane's Grocery Gas Mobil Food Joe's Supermarket My transaction list has items like this: Jane's Grocery #473 $16 Allentown Mobil $18 etc. -so the problem is that values in the lookup table are a substring of the transaction description, not the other way around. Is there a simple way to reverse the way the lookup works? If not, a method to solve this problem would be excecuting a search/replace whereby every item (substring) in the lookup table replaces the cell value every item in the transaction list where it is a substring, but I'm not sure how I could automate this to do so for every item in the lookup table, and I understand that the order of items in the lookup table impacts this method. Any further advice is much appreciated. Thanks, Joe |
#5
|
|||
|
|||
Joe,
The "Jane's Grocery #473" problem: Switch your columns in your lookup table so that Transaction comes first, and sort ascending on the Transaction Column. Let's say that your example table is in A1:B4 . Then, with Jane's Grocery #473 in cell E7, use the formula =VLOOKUP(E7,$A$1:$B$4,2) The key is sorting based on the first column, and leaving out the fourth argument of the VLOOKUP function (or set it to True, the default value). The "Allentown Mobil" Problem: You will need to put that as a separate entry into your category lookup table. HTH, Bernie MS Excel MVP "six50joe" wrote in message ups.com... I've encountered one problem trying to use VLOOKUP- My category lookup table looks something like this: Category Transaction Food Jane's Grocery Gas Mobil Food Joe's Supermarket My transaction list has items like this: Jane's Grocery #473 $16 Allentown Mobil $18 etc. -so the problem is that values in the lookup table are a substring of the transaction description, not the other way around. Is there a simple way to reverse the way the lookup works? If not, a method to solve this problem would be excecuting a search/replace whereby every item (substring) in the lookup table replaces the cell value every item in the transaction list where it is a substring, but I'm not sure how I could automate this to do so for every item in the lookup table, and I understand that the order of items in the lookup table impacts this method. Any further advice is much appreciated. Thanks, Joe |
#6
|
|||
|
|||
Bernie, thanks for your suggestion. I tried it, and it seems to solve
the problem for the most part, except that the lookup seems to work based on sort order rather than finding the transaction table entry as a substring in the transaction text. I've already thought of one way I can work around this though, but haven't tried it yet. Thanks again, Joe |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
view multiple files in multiple windows on multiple screens. | Excel Discussion (Misc queries) | |||
How do i auto create multiple files from 1 with multiple sheets | Excel Worksheet Functions | |||
Adding multiple worksheets | Excel Worksheet Functions | |||
Joining Multiple filters with OR rather than AND | Excel Worksheet Functions | |||
XML / parent with multiple children and with multiple children | Excel Discussion (Misc queries) |