| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Search table for string return next columns value
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Search table for string return next columns value
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Search table for string return next columns value
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I get a Pivot Table to repeat the values in the columns? | Excel Discussion (Misc queries) | |||
| Pivot table with more than 3 columns | Excel Worksheet Functions | |||
| Display the source for a pivot table page field | Excel Worksheet Functions | |||
| How to search a column that is not the first of the table | Excel Worksheet Functions | |||
| How do i build a search table in excel | Excel Worksheet Functions | |||
Linear Mode
