About Us

T. Valko

You're welcome. Thanks for the feedback!

Biff

"mikpits" wrote in message
...

You are a genius! That's exactly what I was looking for but couldn't
figure it out.

Thanks so much for your help and tips.

I'm incorporating it into my budget now.

again thanks.
---------

T. Valko Wrote:
Here's a sample file:

http://cjoint.com/?blxBoBJBrQ

sample_lookup.xls 13.5kb

This should get you started. There are some things to consider.

If you download the file you'll notice that cell A1 returns #N/A even
though
"Denny's" is listed as a payee. The difference is that in cell B1,
"Denny's"
contains an apostrophe and "Dennys" in the list of payees does not.

You'll also notice that cell A6 returns "Groceries" for "Costco" while
cell
B6 contains "Costco Gas". The reason that happens is because the
formula
stops when it finds the first match. In the Payee list Costco is listed

before Costco Gas so the formula stops when it matches Costco in cell
B6 to
Costco from the list. Where there are these types of "fuzzy" matches
list
Costco Gas before Costco:
-
Payees, Categories
Wal-mart , Household
Costco Gas, Groceries
Costco, Gasoline
Allstate, Insurance
Dennys, Eating out
albertsons, Groceries
Mervyns, Clothing-

The other #N/A's are due to no matches (incomplete payee list)

Biff

"mikpits" wrote in message
...-

Some of you folks out here are wizards so I thought I'd ask you.
I keep chipping away but this is going to take me weeks or months.

I'm trying to create a home budget

After downloading my statement from my bank I'd like to automatically
assign the category that the transactions belong to.

I have a list of payees that are assigned specific categories ie:
(payees!A1 Payees!B1)
Payees, Categories
Wal-mart , Household
Costco, Groceries
Costco Gas, Gasoline
Allstate, Insurance
Dennys, Eating out
albertsons, Groceries
Mervyns, Clothing
etc

The Transaction_detail comes in with a bunch of stuff ie:
(in worksheet January column B5)
DENNY'S #6739 SOUTH JORDAN UT Date 11/14/06 2433333432240004760575
5814
MERVYNS 00002949 SANDY UT Date 11/14/06 24333376288591142946824 5355
BURLINGTON COA00000794 MURRAY UT Date 11/14/06
24399006288320200161199
5651
JCPENNEY STORE 0231 SANDY UT Date 11/14/06 2433316628823423742798
5355
POS ALBERTSONS 370 E. 200 S. SALT LAKE CIT UT
POS COSTCO GAS #00487 11100 S. AUTO MALL
BLOCKBUSTER VIDEO #49057 DRAPER UT Date 10/17/06
24610436234232018594403 7841
REAMS FOOD STR.INC. SANDY UT Date 10/18/06 24433336291040001974168
5551

How do I take the payee, search the transaction detail and return the
category in the same row as the match of the payee in the transaction
detail?

For example I'd like:

Eating out, DENNY'S #6739 SOUTH JORDAN UT Date 11/14/06 ....
Clothing, MERVYNS 00002949 SANDY UT Date 11/14/06 ....
clothing, JCPENNEY STORE 0231 SANDY UT Date 11/14/06 ....
Groceries, POS ALBERTSONS 370 E. 200 S. SALT LAKE CIT UT
Gasoline, POS COSTCO GAS #00487 11100 S. AUTO MALL
entertain., BLOCKBUSTER VIDEO #49057 DRAPER UT Date 10/17/06 ....
etc.

I can do the vlookup, but I can't figure out the search in string
thing.

Thanks for your help

--
mikpits -

--
mikpits

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How do I get a Pivot Table to repeat the values in the columns?	Sunshine76	Excel Discussion (Misc queries)	4	March 20th 09 10:25 PM
Pivot table with more than 3 columns	Rasoul Khoshravan	Excel Worksheet Functions	1	January 26th 07 06:36 PM
Display the source for a pivot table page field	Gary Brown	Excel Worksheet Functions	4	November 8th 06 03:02 PM
How to search a column that is not the first of the table	Antonio	Excel Worksheet Functions	4	October 24th 06 09:24 PM
How do i build a search table in excel	Obi-Wan Kenobi	Excel Worksheet Functions	2	March 20th 06 03:20 PM

Menu

About Us