Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
data filtering via vlookup or index/match/find or search withwildcard
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
data filtering via vlookup or index/match/find or search with wild
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. -- Gary''s Student - gsnu200781 " wrote: 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
data filtering via vlookup or index/match/find or search withwild
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
data filtering via vlookup or index/match/find or search with wildcard
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 wrote in message ... 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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
data filtering via vlookup or index/match/find or search withwildcard
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. D1:D10) 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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
data filtering via vlookup or index/match/find or search withwildcard
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
data filtering via vlookup or index/match/find or search withwildcard
Excellent. Exactly what I was looking for. Thank you!
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
data filtering via vlookup or index/match/find or search with wildcard
=INDEX(List3,SUM(IF(ISNUMBER((IF(ISNUMBER(SEARCH(T RANSPOSE(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) -- Biff Microsoft Excel MVP "Herbert Seidenberg" wrote in message ... Here is a ready to use solution: Excel 2003 http://www.freefilehosting.net/download/3g6k9 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
data filtering via vlookup or index/match/find or search withwildcard
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
data filtering via vlookup or index/match/find or search with
or this formula...
=INDEX(List3,MATCH(TRUE,ISNUMBER(SEARCH(List2,A2)) ,0)) ctrl+shift+enter, not just enter "Herbert Seidenberg" wrote: Here is a ready to use solution: Excel 2003 http://www.freefilehosting.net/download/3g6k9 |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
data filtering via vlookup or index/match/find or search with
Thank you to all of you for the solutions. They work great!
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using Index & Match functions to find data on separate worksheet. | Excel Worksheet Functions | |||
VLOOKUP/Index&Match data format issue | Excel Worksheet Functions | |||
Using INDEX and MATCH to find data in 2 different sheets | Excel Worksheet Functions | |||
find all cells that match and use in an index/vlookup | Excel Discussion (Misc queries) | |||
Using Search with either vlookup or match and index | Excel Worksheet Functions |