Help: Multiple filters with mutually exclusive items

[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

 

[Bernie Deitrick]

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]

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

 

[six50joe]

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

 

[Bernie Deitrick]

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]

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