Req: How to create multiple mutually exclusive filters

[six50joe]

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 are:

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

 

[Ron Coderre]

Could you create a separate lookup list, perhaps on another sheet that
matches vendors with categories:

Vendor.......................Category
•••••••.......................••••••••••
Joe's Supermarket.......Food
Exxon.........................Gas
Con Edison..................Electric
Jane's Grocery.............Food
Mobil..........................Gas
Mobil Market...............Grocery
Whole Foods...............Grocery

Then you could use a helper column to asign categories to vendors:
C2: =VLOOKUP(A2,LU_Categories,2,0)

That formula would lookup Joe's Supermarket in the list and return "Food"

Also, instead of copying to other columns, you might consider one of these
options:

•Put formulas in the other columns that return the expense amount if the
asigned category matches the column heading

Example for D1: FOOD
D2: IF($C2 = $D$1,$B2,"")

•Use SUMIF formulas to calculate category totals. (Then Data Filter to
answer questions)

•Use SUMPRODUCT to return category totals where the criteria is more
complicated.

•Use the SUBTOTAL function above the data range to return the total of
visible filtered data.

Does that help?

Regards,
Ron

 

[six50joe]

Ron,
This was extremely helpful and should suit my needs. It took me a
little while to try this out because I'm still a novice at advanced
Excel functionality. VLOOKUP solves my problem nicely. I'm going to
have to experiment with the lookup table values to see how much of a
task it will be to maintain it. My mock example is really simple but
the acutal vendor descriptions in my bank statements may make
categorizing tricky, due to slight variations on similarly named items.
As long as my table can default to an "OTHER" category for exceptions,
managing this task should be doable.

My next step is creating macro(s) that will intelligently set up the
above upon importing my statement data.

Thanks,
Joe