Hi
I am using 2 formulas to extract a top 10 list and need to exclude certain items from the results and can't seem o figure it out.
one of the formulas is:
=SORT(FILTER(A2:B191, B2:B191>=LARGE(B2:B191, 10)), 2, -1)
It does well and dutifully returns the proper results as far as the top 10 items however I want to exclude two text strings from the results. A wildcard for "vac*" and the word "stump". Also how would I exclude those items if I create a named range "_Top10" for A2:B191?
I am also using a combination of different formulas to get the same results,
In this one I am using a named range to get the values =LARGE(countsp,ROW(J2)-ROW(J$1)) and again I want to exclude the same values as above the values I want to exclude are in an adjacent column with a named range of "bnm".
Then I am using this formula in an adjacent column to get the match =INDEX($B$2:$B$226,SMALL(IF($C$2:$C$226=K2,ROW($C$2:$C$226)-ROW($C$1)),COUNTIF($K$2:K2,K2)))
It seems to me the first formula is more efficient but am I not sure and I don't know if it makes a difference.
I have pasted an example of my table below.
Help and advice appreciated.
Tim
| Vacant Planting Site | 2586 |
| Platanus x acerifolia | 2207 |
| Lagerstroemia indica | 1893 |
| Pinus brutia var. eldarica | 1015 |
| Liquidambar styraciflua | 781 |
| Pistacia chinensis | 778 |
| Pyrus calleryana | 737 |
| Cinnamomum camphora | 650 |
| Pinus canariensis | 619 |
| Stump | 609 |
Hi Tim,
The formula below will exclude text containing vac and stump based on the data you pasted into your question and pasted to cell A2:
=FILTER(A2:B11,(B2:B11>=LARGE(B2:B11,10))*(NOT(ISNUMBER(SEARCH("Vac*",A2:A11)))*(A2:A11<>"stump")))
It's always better to provide the data in a sample Excel file where you can also insert your formulas that relate to the cells in the file, rather than giving formula examples that reference a file we can't see.
Mynda
Hi Mynda,
Thank you for reply. I am almost there. Using the formula supplied while omitting the desired items works the formula returns the top 8 and not 10.
Secondly I would like the results returned from most to least as it now returns in in the order they are in relation to each other. (See Tab "Sorts")
Thanks again and sorry that I did not attach a file last time.
Thank you for your help. I have learned a great deal from your organization.
