Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Wild card match and then count
Afternoon
I have a data set (50K+) with various product codes such as 618B TPR, 618B PGR, 13 VCR etc. Firstly I want to complete a count exercise using the above codes to see how often they match themselves within a description of the item. I somehow need to use wild cards because it will be impossible to trim the description down to just the production code. Happy for the solution to work on 80% of the data. Where it is a 1:1 match I will then aling these data records with each using a vlookup function. Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Wild card match and then count
Hi Pete
I'm not sure I am fully understanding your requirement, but one way (if there aren't too many product codes) would be to insert 2 new rows above your data. Assuming your data is in column A only. In B2 enter the first product code, C2 the next etc for each of the codes you want to find. In B3 enter =--(ISNUMBER(FIND(B$2,$A3))) Copy down for the 50,000 rows of data. Copy the whole of the column across for as many codes as you are looking for. In B1 enter =SUBTOTAL(9,B3:B50000) Copy across as far as required. You will have a count in B1 onward for your matches for each code. Highlight row 2DataAutofilter and use the dropdown to select 1 for any of the columns, and you will have a filtered list of the rows where matches occur. -- Regards Roger Govier "LITTLE PETE" wrote in message ... Afternoon I have a data set (50K+) with various product codes such as 618B TPR, 618B PGR, 13 VCR etc. Firstly I want to complete a count exercise using the above codes to see how often they match themselves within a description of the item. I somehow need to use wild cards because it will be impossible to trim the description down to just the production code. Happy for the solution to work on 80% of the data. Where it is a 1:1 match I will then aling these data records with each using a vlookup function. Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Wild card match and then count
If I understand your question first question correctly, consider the
following. If Column A contains your product codes and Column B contains your descriptions, the following formula will tell you how many times the product code is repeated in the description. =SUMPRODUCT(--ISNUMBER(SEARCH(A1,MID(B1,ROW(INDIRECT("1:"&LEN(B1 ))),LEN(A1))))) Here I have assumed your data starts in Row 1. If it start in another row, simply replace the 1's that are directly part of a cell reference to the number of that row (in other word, change all the 1's EXCEPT for the 1 following the quote mark). Rick "LITTLE PETE" wrote in message ... Afternoon I have a data set (50K+) with various product codes such as 618B TPR, 618B PGR, 13 VCR etc. Firstly I want to complete a count exercise using the above codes to see how often they match themselves within a description of the item. I somehow need to use wild cards because it will be impossible to trim the description down to just the production code. Happy for the solution to work on 80% of the data. Where it is a 1:1 match I will then aling these data records with each using a vlookup function. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match wild cards and then count | Excel Discussion (Misc queries) | |||
wild card in sumproduct | Excel Worksheet Functions | |||
Wild Card Search | Excel Worksheet Functions | |||
Wild card * | Excel Worksheet Functions | |||
Is it possible to use a wild card in a =COUNT(IF equation? | Excel Worksheet Functions |