Extract similar values

B

bb8

I have a worksheet named "Height" where my Column A consists o
different persons' height measurement for example, 123, 178 etc. I
another worksheet named "Summary", let's say in cell A1 I keyed-in 182
Are there any formula that allow me to retrieve or check my "Height
worksheet for all cells that consist of 1, 8 and 2? For example, 182
821, 218, 128 etc.? Thanks
 
P

Peo Sjoblom

You could use the advanced filter, assume that the header is in A1 and the
data starts in A2,
in an empty cell preferably in row 2 somewhere put

=AND(ISNUMBER(FIND(1,A2)),ISNUMBER(FIND(2,A2)),ISNUMBER(FIND(8,A2)))

let's say H2, leave H1 empty

now use data>filter>advanced filter, copy to another location, criteria
range is

$H$1:$H$2

click OK, will give you this list, note that it won't return 12 or 82 or 1
or 8, just
3 digits that contains 1, 2 and 8, Otherwise you have to add to the formula
 
Top