| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
i'm missing something
im trying to simplify my personal bookkeeping. i import my data from
the bank as a .csv and want to transfer it to a second sheet without all the jibberish. its set up with a vlookup etc. but what i can't figure out is say my entry from shopping at wal-mart has POS PURCHASE - WAL-MART #0822 WAL-MART but i want to transfer it and say WAL-MART i tried if(a1="*WAL-MART*","Wal-Mart",0) as a test but i guess it didn't like the wildcards.. also what would be the best way to convert multiple entries, example if i went to home depot, not wal-mart. would using or with if be the best solution? thanks in advance, jeff |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
i'm missing something
=IF(ISNUMBER(SEARCH("WAL-MART",A1)),"WAL-MART",0)
" wrote: im trying to simplify my personal bookkeeping. i import my data from the bank as a .csv and want to transfer it to a second sheet without all the jibberish. its set up with a vlookup etc. but what i can't figure out is say my entry from shopping at wal-mart has POS PURCHASE - WAL-MART #0822 WAL-MART but i want to transfer it and say WAL-MART i tried if(a1="*WAL-MART*","Wal-Mart",0) as a test but i guess it didn't like the wildcards.. also what would be the best way to convert multiple entries, example if i went to home depot, not wal-mart. would using or with if be the best solution? thanks in advance, jeff |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
i'm missing something
that worked!! (except it didn't like the "-") thanks you've got me
headed in the right direction!! On Feb 12, 8:46 pm, Teethless mama wrote: =IF(ISNUMBER(SEARCH("WAL-MART",A1)),"WAL-MART",0) " wrote: im trying to simplify my personal bookkeeping. i import my data from the bank as a .csv and want to transfer it to a second sheet without all the jibberish. its set up with a vlookup etc. but what i can't figure out is say my entry from shopping at wal-mart has POS PURCHASE - WAL-MART #0822 WAL-MART but i want to transfer it and say WAL-MART i tried if(a1="*WAL-MART*","Wal-Mart",0) as a test but i guess it didn't like the wildcards.. also what would be the best way to convert multiple entries, example if i went to home depot, not wal-mart. would using or with if be the best solution? thanks in advance, jeff- Hide quoted text - - Show quoted text - |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
i'm missing something
One way:
Make a list of your frequent transaction parties. As an example: Wal-mart Costco Allstate Dennys Albertsons Mervyns Then, your imported data may look like this: DENNYS #6739 SOUTH JORDAN UT Date 11/14/06 MERVYNS 00002949 SANDY UT Date 11/14/06 BURLINGTON COA00000794 MURRAY UT Date 11/14/06 JCPENNEY STORE 0231 SANDY UT Date 11/14/06 POS ALBERTSONS 370 E. 200 S. SALT LAKE CIT UT POS COSTCO #00487 11100 S. AUTO MALL BLOCKBUSTER VIDEO #49057 DRAPER UT Date 10/17/06 REAMS FOOD STR.INC. SANDY UT Date 10/18/06 assume the above data is in the range A2:A9 The list of frequent transaction parties is in J2:J8 Enter this formula in B2 to extract the transaction party: =LOOKUP(2,1/(ISNUMBER(SEARCH(J$2:J$8,A2))),J$2:J$8) Copy down as needed. Biff wrote in message oups.com... im trying to simplify my personal bookkeeping. i import my data from the bank as a .csv and want to transfer it to a second sheet without all the jibberish. its set up with a vlookup etc. but what i can't figure out is say my entry from shopping at wal-mart has POS PURCHASE - WAL-MART #0822 WAL-MART but i want to transfer it and say WAL-MART i tried if(a1="*WAL-MART*","Wal-Mart",0) as a test but i guess it didn't like the wildcards.. also what would be the best way to convert multiple entries, example if i went to home depot, not wal-mart. would using or with if be the best solution? thanks in advance, jeff |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
i'm missing something
this is what i ultimately want... i tried the formula but got 0, why
is the lookup_value 2? sorry i'm a newbie here but last year i used excel just to enter info into the cells, and didn't know what functions were. thanks On Feb 12, 8:54 pm, "T. Valko" wrote: One way: Make a list of your frequent transaction parties. As an example: Wal-mart Costco Allstate Dennys Albertsons Mervyns Then, your imported data may look like this: DENNYS #6739 SOUTH JORDAN UT Date 11/14/06 MERVYNS 00002949 SANDY UT Date 11/14/06 BURLINGTON COA00000794 MURRAY UT Date 11/14/06 JCPENNEY STORE 0231 SANDY UT Date 11/14/06 POS ALBERTSONS 370 E. 200 S. SALT LAKE CIT UT POS COSTCO #00487 11100 S. AUTO MALL BLOCKBUSTER VIDEO #49057 DRAPER UT Date 10/17/06 REAMS FOOD STR.INC. SANDY UT Date 10/18/06 assume the above data is in the range A2:A9 The list of frequent transaction parties is in J2:J8 Enter this formula in B2 to extract the transaction party: =LOOKUP(2,1/(ISNUMBER(SEARCH(J$2:J$8,A2))),J$2:J$8) Copy down as needed. Biff wrote in message oups.com... im trying to simplify my personal bookkeeping. i import my data from the bank as a .csv and want to transfer it to a second sheet without all the jibberish. its set up with a vlookup etc. but what i can't figure out is say my entry from shopping at wal-mart has POS PURCHASE - WAL-MART #0822 WAL-MART but i want to transfer it and say WAL-MART i tried if(a1="*WAL-MART*","Wal-Mart",0) as a test but i guess it didn't like the wildcards.. also what would be the best way to convert multiple entries, example if i went to home depot, not wal-mart. would using or with if be the best solution? thanks in advance, jeff- Hide quoted text - - Show quoted text - |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
i'm missing something
Hmmm....
Based on the sample I posted a result of 0 is impossible! The formula will return either the name of the party (if found) or #N/A (meaning no match was found in the party list) Hard to tell why you got that result without knowing any of the details. Want me to post a sample file that demonstrates this? Biff wrote in message ps.com... this is what i ultimately want... i tried the formula but got 0, why is the lookup_value 2? sorry i'm a newbie here but last year i used excel just to enter info into the cells, and didn't know what functions were. thanks On Feb 12, 8:54 pm, "T. Valko" wrote: One way: Make a list of your frequent transaction parties. As an example: Wal-mart Costco Allstate Dennys Albertsons Mervyns Then, your imported data may look like this: DENNYS #6739 SOUTH JORDAN UT Date 11/14/06 MERVYNS 00002949 SANDY UT Date 11/14/06 BURLINGTON COA00000794 MURRAY UT Date 11/14/06 JCPENNEY STORE 0231 SANDY UT Date 11/14/06 POS ALBERTSONS 370 E. 200 S. SALT LAKE CIT UT POS COSTCO #00487 11100 S. AUTO MALL BLOCKBUSTER VIDEO #49057 DRAPER UT Date 10/17/06 REAMS FOOD STR.INC. SANDY UT Date 10/18/06 assume the above data is in the range A2:A9 The list of frequent transaction parties is in J2:J8 Enter this formula in B2 to extract the transaction party: =LOOKUP(2,1/(ISNUMBER(SEARCH(J$2:J$8,A2))),J$2:J$8) Copy down as needed. Biff wrote in message oups.com... im trying to simplify my personal bookkeeping. i import my data from the bank as a .csv and want to transfer it to a second sheet without all the jibberish. its set up with a vlookup etc. but what i can't figure out is say my entry from shopping at wal-mart has POS PURCHASE - WAL-MART #0822 WAL-MART but i want to transfer it and say WAL-MART i tried if(a1="*WAL-MART*","Wal-Mart",0) as a test but i guess it didn't like the wildcards.. also what would be the best way to convert multiple entries, example if i went to home depot, not wal-mart. would using or with if be the best solution? thanks in advance, jeff- Hide quoted text - - Show quoted text - |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
i'm missing something
On Feb 13, 9:36 pm, "T. Valko" wrote:
Hmmm.... Based on the sample I posted a result of 0 is impossible! The formula will return either the name of the party (if found) or #N/A (meaning no match was found in the party list) Hard to tell why you got that result without knowing any of the details. Want me to post a sample file that demonstrates this? Biff wrote in message ps.com... this is what i ultimately want... i tried the formula but got 0, why is the lookup_value 2? sorry i'm a newbie here but last year i used excel just to enter info into the cells, and didn't know what functions were. thanks On Feb 12, 8:54 pm, "T. Valko" wrote: One way: Make a list of your frequent transaction parties. As an example: Wal-mart Costco Allstate Dennys Albertsons Mervyns Then, your imported data may look like this: DENNYS #6739 SOUTH JORDAN UT Date 11/14/06 MERVYNS 00002949 SANDY UT Date 11/14/06 BURLINGTON COA00000794 MURRAY UT Date 11/14/06 JCPENNEY STORE 0231 SANDY UT Date 11/14/06 POS ALBERTSONS 370 E. 200 S. SALT LAKE CIT UT POS COSTCO #00487 11100 S. AUTO MALL BLOCKBUSTER VIDEO #49057 DRAPER UT Date 10/17/06 REAMS FOOD STR.INC. SANDY UT Date 10/18/06 assume the above data is in the range A2:A9 The list of frequent transaction parties is in J2:J8 Enter this formula in B2 to extract the transaction party: =LOOKUP(2,1/(ISNUMBER(SEARCH(J$2:J$8,A2))),J$2:J$8) Copy down as needed. Biff wrote in message groups.com... im trying to simplify my personal bookkeeping. i import my data from the bank as a .csv and want to transfer it to a second sheet without all the jibberish. its set up with a vlookup etc. but what i can't figure out is say my entry from shopping at wal-mart has POS PURCHASE - WAL-MART #0822 WAL-MART but i want to transfer it and say WAL-MART i tried if(a1="*WAL-MART*","Wal-Mart",0) as a test but i guess it didn't like the wildcards.. also what would be the best way to convert multiple entries, example if i went to home depot, not wal-mart. would using or with if be the best solution? thanks in advance, jeff- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - if you could please, maybe that will help me understand exactly what the formula is doing. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
i'm missing something
Here's a sample file:
Lookup3.xls 13.5kb http://cjoint.com/?cpxQP8QghF Biff wrote in message oups.com... On Feb 13, 9:36 pm, "T. Valko" wrote: Hmmm.... Based on the sample I posted a result of 0 is impossible! The formula will return either the name of the party (if found) or #N/A (meaning no match was found in the party list) Hard to tell why you got that result without knowing any of the details. Want me to post a sample file that demonstrates this? Biff wrote in message ps.com... this is what i ultimately want... i tried the formula but got 0, why is the lookup_value 2? sorry i'm a newbie here but last year i used excel just to enter info into the cells, and didn't know what functions were. thanks On Feb 12, 8:54 pm, "T. Valko" wrote: One way: Make a list of your frequent transaction parties. As an example: Wal-mart Costco Allstate Dennys Albertsons Mervyns Then, your imported data may look like this: DENNYS #6739 SOUTH JORDAN UT Date 11/14/06 MERVYNS 00002949 SANDY UT Date 11/14/06 BURLINGTON COA00000794 MURRAY UT Date 11/14/06 JCPENNEY STORE 0231 SANDY UT Date 11/14/06 POS ALBERTSONS 370 E. 200 S. SALT LAKE CIT UT POS COSTCO #00487 11100 S. AUTO MALL BLOCKBUSTER VIDEO #49057 DRAPER UT Date 10/17/06 REAMS FOOD STR.INC. SANDY UT Date 10/18/06 assume the above data is in the range A2:A9 The list of frequent transaction parties is in J2:J8 Enter this formula in B2 to extract the transaction party: =LOOKUP(2,1/(ISNUMBER(SEARCH(J$2:J$8,A2))),J$2:J$8) Copy down as needed. Biff wrote in message groups.com... im trying to simplify my personal bookkeeping. i import my data from the bank as a .csv and want to transfer it to a second sheet without all the jibberish. its set up with a vlookup etc. but what i can't figure out is say my entry from shopping at wal-mart has POS PURCHASE - WAL-MART #0822 WAL-MART but i want to transfer it and say WAL-MART i tried if(a1="*WAL-MART*","Wal-Mart",0) as a test but i guess it didn't like the wildcards.. also what would be the best way to convert multiple entries, example if i went to home depot, not wal-mart. would using or with if be the best solution? thanks in advance, jeff- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - if you could please, maybe that will help me understand exactly what the formula is doing. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Who is missing ? | Excel Discussion (Misc queries) | |||
| add-ins missing | Excel Worksheet Functions | |||
| Toolbars Missing, And option to Add Missing | Excel Discussion (Misc queries) | |||
| Missing row | Excel Discussion (Misc queries) | |||
| Missing Row | Excel Worksheet Functions | |||
Hybrid Mode
