data filtering via vlookup or index/match/find or search withwildcard

[cupuacu2000]

I've been searching the internet and have yet to find a solution,
though it seems as though I've come close.

I would like to use excel to anaylze my account activity with pivot
tables. To do so I need to do some consolidation and I don't want to
manually filter all of my transactions every month.

During a month or year we frequent certain businesses for certain
purchases. For each business, the activity is identified with their
name and some other characters specific to the purchase. For example,
all our Chevron purchases are of the format *CHEVRON*, Target's are
*TARGET*, Wendy's are *WENDYS*, and local grocer are *LOCALGROCER* and
so on.

Ideally, I would like to make use of the vlookup function where I have
a wildcard/substring in the 1st column of the reference table like
*CHEVRON* and when the function finds a match with the lookup value
such as CA SAN JOSE CHEVRON/897983743 it would return my chosen column
index number value. From what I have seen, I don't think vlookup is
capable of recognizing the substring as a wildcard within the larger
lookup value string. From examples I have seen it looks like it could
do the opposite, meaning the reference table has the larger string and
the lookup value can be a substring with wildcard ("*"&R1&"*") as the
lookup_value.

I have also tried the index/match/find or search approach as well. My
difficulty with this approach is the find or search function. I have
attempted to enter the wildcard column of my reference table as an
array ($A$1:$A$5) as the find_text input; however, once my within_text
exceeds or passes the last row of the reference table, it returns
#VALUE!.

Any suggestions on how I could use the two approaches or other
solutions that would not require manual manipulation.

 

[Gary''s Student]

Just some things to think about:

1. AutoFilter
You can set criteria on each column. Using Custom will allow specifying
Contains, Begins With, Ends with, Does not Contain, etc. This allow viewing
entire rows that match criteria.

2. Formula autofiltering
You can get the equivalent of autofiltering by using formulas. Let's say
that in A1 thru C100 we have:

7 4/22/2008 cat
6 4/28/2008 dog
8 5/4/2008 mouse
1 5/10/2008 rat
1 5/16/2008 snail
6 5/22/2008 rabbit
4 5/28/2008 bird
9 6/3/2008 animal
2 6/9/2008 peacock
5 6/15/2008 fawn
10 6/21/2008 elephant
7 6/27/2008 gnu
3 7/3/2008 aa
8 7/9/2008 a1
6 7/15/2008 1a
4 7/21/2008 cat
9 7/27/2008 dog
9 8/2/2008 mouse
5 8/8/2008 rat
10 8/14/2008 snail
2 8/20/2008 rabbit
10 8/26/2008 bird
3 9/1/2008 animal
7 9/7/2008 peacock
5 9/13/2008 fawn
10 9/19/2008 elephant
7 9/25/2008 gnu
5 10/1/2008 aa
9 10/7/2008 a1
1 10/13/2008 1a
6 10/19/2008 cat
1 10/25/2008 dog
7 10/31/2008 mouse
10 11/6/2008 rat
1 11/12/2008 snail
4 11/18/2008 rabbit
6 11/24/2008 bird
6 11/30/2008 animal
4 12/6/2008 peacock
8 12/12/2008 fawn
8 12/18/2008 elephant
8 12/24/2008 gnu
9 12/30/2008 aa
1 1/5/2009 a1
8 1/11/2009 1a
8 1/17/2009 cat
10 1/23/2009 dog
3 1/29/2009 mouse
6 2/4/2009 rat
1 2/10/2009 snail
2 2/16/2009 rabbit
9 2/22/2009 bird
1 2/28/2009 animal
2 3/6/2009 peacock
5 3/12/2009 fawn
10 3/18/2009 elephant
7 3/24/2009 gnu
5 3/30/2009 aa
10 4/5/2009 a1
8 4/11/2009 1a
1 4/17/2009 cat
3 4/23/2009 dog
6 4/29/2009 mouse
5 5/5/2009 rat
4 5/11/2009 snail
2 5/17/2009 rabbit
10 5/23/2009 bird
7 5/29/2009 animal
3 6/4/2009 peacock
2 6/10/2009 fawn
10 6/16/2009 elephant
7 6/22/2009 gnu
10 6/28/2009 aa
3 7/4/2009 a1
1 7/10/2009 1a
4 7/16/2009 cat
6 7/22/2009 dog
9 7/28/2009 mouse
8 8/3/2009 rat
9 8/9/2009 snail
5 8/15/2009 rabbit
9 8/21/2009 bird
4 8/27/2009 animal
4 9/2/2009 peacock
2 9/8/2009 fawn
3 9/14/2009 elephant
7 9/20/2009 gnu
6 9/26/2009 aa
4 10/2/2009 a1
7 10/8/2009 1a
9 10/14/2009 cat
3 10/20/2009 dog
8 10/26/2009 mouse
4 11/1/2009 rat
8 11/7/2009 snail
8 11/13/2009 rabbit
5 11/19/2009 bird
10 11/25/2009 animal
7 12/1/2009 peacock
1 12/7/2009 fawn

and we wish to view all the data where column A has the value 7. In D1 we
put 7. In E1 we put:

=SMALL(IF(A$1:A$100=$D$1,ROW(A$1:A$100)),ROW()) as an array formula and copy
down

In F1 we put:
=INDIRECT("B" & E1) and copy down

In G1 we put:
=INDIRECT("C" & E1) and copy down.

What we see in cols E & F & G is:

1 4/22/2008 cat
12 6/27/2008 gnu
24 9/7/2008 peacock
27 9/25/2008 gnu
33 10/31/2008 mouse
57 3/24/2009 gnu
68 5/29/2009 animal
72 6/22/2009 gnu
87 9/20/2009 gnu
90 10/8/2009 1a
99 12/1/2009 peacock

If we look at the values in column E, they are the row numbers where the 7's
are in column A. Kind of like MATCH() that finds ALL the values. Columns F
& G are the equivalent values for columns B & C.


Just remember that column E equations must be array entered.

 

[cupuacu2000]

I appreciate the suggestion. The capability I am looking for though
is the ability to clean/consolidate the data, meaning my data isn't
just a 7, it is *7* and I want search for *7* and return 7. If it was
just 7 to begin with I would not need to clean/consolidate and could
anaylze with pivot tables immediately or use vlookup to consolidate
further. Second, I am looking for something that is a little more
dynamic or encompassing and only occupies one column. For this reason
I am trying to find a way to make use of a reference table otherwise
after I find all the 7's I will need to move on to the 8's with
another set of columns and then consolidate the data. I am trying to
get to something that will allow my to consolidate the data as quickly
as possible. Right now it appears to be the autofilter option but
that requires manually adjusting the criteria and then manually
entering the common critieria in the corresponding row of another
column.

 

[Ashish Mathur]

Hi,

I am not sure that I understood your question correctly, but this is my
understanding. Assume that your data is arranged in the following manner.
Assume that the Data below is in range B5:C9

Fruits Amount

Apples 100
Apples and Oranges 200
Oranges 300
Bananas 400

The task is to sum up all instances of Oranges. To accomplish this, enter
Oranges in cell B10 and enter the following array formula (confirmed by
Ctrl+Shift+Enter) in cell C10

SUM(IF(ISNUMBER(FIND(B10,$B$5:$B$8)),$C$5:$C$8))

Please let me know how this works for you.

--
Regards,

Ashish Mathur
www.ashishmathur.com
http://www.linkedin.com/in/excelenthusiasts

 

[cupuacu2000]

This is close. And it may be what I do instead of using a pivot
table. If I am unable to find a nice way to clean/filter my data I
may choose to use it.

What I am looking for is something more robust and able to utilize a
reference table.

I can export my checking activity in csv format and import into
excel. One of the columns or fields is "Name". The Name identifies
the business where the transaction was completed. Each entry contains
the name of the business plus some other data that identifies each
unique transaction. For example, the data in the column (i.e. D110)
would be something like:

CA SAN JOSE CHEVRON/123456
75483 TRADER JOE 43ASD
CA SACRAM TEXACO/499324
EXXON FG D7832S
WENDY'S 4GY57687
CA SAN FRAN CHEVRON/1D9823
M0 TRADER JOE 434QW
34 WENDY'S 809324DE
565 FRYS 689AHJKIEW
HU7 FRYS EFQFD8923

What I would like to be able to utilize is a reference table (i.e.
B1:C6) similar to the following that I can update over time:

CHEVRON GAS
EXXON GAS
TEXACO GAS
WENDY'S FAST FOOD
TRADER JOE GROCERY
FRYS ELECTRONICS

I would like to be able to place a function in column E that would be
able to look at the information entered in column D, compare it
against the reference table and when it found the substring it would
return the more generic type of purchase. For example, for the first
entry in column C, I would want it to be able to identify the
substring CHEVRON and return GAS. I could then use column E to
conduct analysis with a pivot table and look at our gas, fast food,
grocery, etc purchases. In the current format the data is so unique
that it doesn't lend itself to larger analysis. I have chosen to
utilize or try to find a way to utilize vlookup or match/index because
once I have a reference table established, all I have to do is copy
the function down the column and I am done.

 

[Herbert Seidenberg]

Here is a ready to use solution:
Excel 2003
http://www.freefilehosting.net/download/3g6k9

 

[ksessions]

Excellent. Exactly what I was looking for. Thank you!

 

[T. Valko]

=INDEX(List3,SUM(IF(ISNUMBER((IF(ISNUMBER(SEARCH(TRANSPOSE(List2),List1
2:2)),SEARCH(TRANSPOSE(List2),List1
2:2)))),TRANSPOSE(ROW(List2)-MIN(ROW(List2))+1))))

You can replace that formula in column C with this much shorter formula:

=LOOKUP(2,1/SEARCH(List2,A2),List3)

 

[Herbert Seidenberg]

Incorporated Biff's shorter formula:
http://www.freefilehosting.net/download/3g6m3

 

[Teethless mama]

or this formula...

=INDEX(List3,MATCH(TRUE,ISNUMBER(SEARCH(List2,A2)),0))

ctrl+shift+enter, not just enter

 

[ksessions]

Thank you to all of you for the solutions. They work great!