Extract similar values

  • Thread starter Thread starter bb8
  • Start date Start date
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
 
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
 
Back
Top