i'm missing something

[jpurl77]

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

 

[Guest]

=IF(ISNUMBER(SEARCH("WAL-MART",A1)),"WAL-MART",0)

 

[T. Valko]

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

 

[jpurl77]

that worked!! (except it didn't like the "-") thanks you've got me
headed in the right direction!!

 

[jpurl77]

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

 

[T. Valko]

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

 

[jpurl77]

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







- Show quoted text -

if you could please, maybe that will help me understand exactly what
the formula is doing.

 

[T. Valko]

Here's a sample file:

Lookup3.xls 13.5kb

http://cjoint.com/?cpxQP8QghF

Biff

 

[jpurl77]

Here's a sample file:

Lookup3.xls 13.5kb

http://cjoint.com/?cpxQP8QghF

Biff







- Show quoted text -

?? ok i copied the formula from your spread sheet and still got 0.
then i copied a payee of mine to your spread sheet and it worked!? can
you think of any setting in the spread sheet or excel (well different
results from different sheets, most likely the sheet) that could be
doing this? im running excel 2000 if that makes any difference.
thanks again for the help

 

[T. Valko]

Are you adjusting the references properly to match your layout?

That's about the only thing I can think of that would cause a return of 0.

Biff

 

[jpurl77]

Are you adjusting the references properly to match your layout?

That's about the only thing I can think of that would cause a return of 0.

Biff







- Show quoted text -

yea thats what i found out trying different things out today. i first
set up 5 names, but the range covered 10 for future use. now it works
like it should!! thanks for all the help!!!

 

[T. Valko]

i first set up 5 names, but the range covered 10 for future use.

You can do that if you use a dynamic named range.

See this for instructions:

http://contextures.com/xlNames01.html#Dynamic

Biff