Hi All,
I'm relatively new to this but this problem really has me stumped. I'll try and describe it best I can and any help would be greatly appreciated.
Search a large data range in a worksheet and if any cell matches a specific combination of upper and lower letters, then output the contents of the cell adjacent to each. The combination of letters will always be 3 upper case letters (~24 combinations) followed by one or more lower case letters in any combination (a to t). Currently, I’m using the following formulas as arrays to extract the row numbers for each matching cell found, followed by the associated column numbers.
In this example, the first three upper case letters to search are in cell A1 and the lower case letters are in B1 which have been "ANDed" together in the formulas below, and the data to search is in the range A2 to W100.
To get the rows that match:
{=SMALL(IF($A$2:$W$100=$A$1&$B$1,ROW($A$2:$W$100)),ROWS($BA$2:BA2))}
To get the columns:
{=SMALL(IF($A$2:$W$100=$A$1&$B$1,IF(ROW($A$2:$W$100)=BB2,COLUMN($A$2:$W$100))),COUNTIF($BB$4:BB4,BB4))}
and then using an indirect function with an offset copied down to output the results.
=INDIRECT(ADDRESS(BA2,BB2+1))
This works ok but I need $A$1&$B$1 in the above to be variable where it will look for 3 upper case letter and ANY combination of lower case letter.
I.e. some cells contain: PJMa, PJMab, PJMbc, SIEa, SIEb, SIEabc, PJMac, PJMb. So searching for "PJM" and "a" will find PJMa, PJMab, and PJMac. Hope that make sense..
Thanks
Bookmarks