Hi, Rick
Since my posted formulas don't use an array formulas or incrementally
expanding formulas (eg COUNTIF($A$1:$A2,"whatever") ), the performance hit
is minimal.
I tested the formulas in a 30,000 row range....then sorted the first column
ascending/descending. The recalcs each took less than 2 seconds.
A UDF might look more elegant, but they are usually relatively sluggish.
I'm not sure it could match that same performance level. Consequently, it may
not be worth having the annoying Macro Warning if that's the only code in the
workbook.
***********
Regards,
Ron
XL2002, WinXP
"Rick Rothstein (MVP -
VB)" wrote:
Try something like this:
Using Col_B as a "helper column":
B1:
=LEFT(A1,LEN(A1)-MAX(INDEX(COUNTIF(A1,"*"&$F$2:$F$13)*$G$2:$G$13,0) )+1)
Copy B1 down through Bxxx
This formula pulls the numbers from the end of those values:
C1: =IF(ISNUMBER(--MID(B1,MAX(FIND({"~","("},SUBSTITUTE(B1,"
","~",LEN(B1)-LEN(SUBSTITUTE(B1,"
","")))))+1,255)),--MID(B1,MAX(FIND({"~","("},SUBSTITUTE(B1,"
","~",LEN(B1)-LEN(SUBSTITUTE(B1," ","")))))+1,255),"na")
Copy C1 down through Cxxx
I am newly returned to Excel, so I am very rusty still... however, Jan said
he has 30,000 rows of data... wouldn't using a VBA macro be better than
loading up all those formulas into the spreadsheet directly?
Rick