finding what numbers are in a string

[David]

I have a column of numbers such as

A B C
1 002 015 102
2 034 002 008
3 015 048 120
4 076 005 008
5 002 048 076

What I would like to do is choose a number such as 002 and get a listing of
all numbers that are in the same column..

So if I put 002. I would get a return of 015, 102, 034, 008, 048, 076.
If I did 015 I would get a return of 002, 102, 048, 120.

I do not care if it comes back with duplicate entries or sorted. Any
assistance would be appreciated.

 

[David McRitchie]

Hi David,
you can use a filter for this
Select column A, Data, Filter, Auto Filter

choose 002 (or will it be 2) from the list

You can paste from the sheet that is filtered and it will not
include cells that have been hidden due to filtering.

More information on Filtering
http://www.contextures.com/tiptech.html

 

[Harlan Grove]

David wrote...

I have a column of numbers such as

A B C
1 002 015 102
2 034 002 008
3 015 048 120
4 076 005 008
5 002 048 076

....

I'll assume this table is named Tbl.

So if I put 002. I would get a return of 015, 102, 034, 008, 048, 076.
If I did 015 I would get a return of 002, 102, 048, 120.

....

If you enter the number to match in a cell named v, then you could use
the following array formula with the first result in cell E1.

E1 [array formula]:
=INDEX(Tbl,INT(SMALL(IF(COUNTIF(OFFSET(Tbl,ROW(Tbl)-CELL("Row",Tbl),0,1,),v)
*(Tbl<>v)>0,(ROW(Tbl)*100000+COLUMN(Tbl))),COLUMNS($E1:E1))/100000),
MOD(SMALL(IF(COUNTIF(OFFSET(Tbl,ROW(Tbl)-CELL("Row",Tbl),0,1,),v)*(Tbl<>v)>0,
(ROW(Tbl)*100000+COLUMN(Tbl))),COLUMNS($E1:E1)),100000))

Fill right as far as needed.

 

[David]

Hello Harlan,
Let's say the Worksheet is called "Numbers" and I wanted the information
on a separate sheet called "String" and start with row B1 and continue to B2,
B3, B4, etc.

So it would look like:
A B C D E F G
6 002 015 102 034 008 048 076

Would the formula look like
=INDEX(NUmbers!A1:C5,INT(SMALL(IF(COUNTIF(OFFSET(Numbers!A1:C5,ROW(Numbers!A1:C5)-CELL"Row",Numbers!A1:C5),0,1,),A6
*(Numbers!A1:C5<>A6)>0,(ROW(Numbers!A1:C5)*100000+COLUMN(Numbers!A1:C5))),COLUMNS($B1:B1))/100000),MOD(SMALL(IF(COUNTIF(OFFSET(Numbers!A1:C5,ROW(Numbers!A1:C5)-CELL("Row",Numbers!A1:C5),0,1,),A6)*(Numbers!A1:C5<>A6)>0,(ROW(NumbersA1:C5)*100000+COLUMN(Numbers!A1:C5))),COLUMNS($B1:B1)),100000))

Where A6 is the equal of v and the location "Numbers!A1:C5" is equal to Tbl?

Thank you for your assistance.

David wrote...

I have a column of numbers such as

A B C
1 002 015 102
2 034 002 008
3 015 048 120
4 076 005 008
5 002 048 076

....

I'll assume this table is named Tbl.

So if I put 002. I would get a return of 015, 102, 034, 008, 048, 076.
If I did 015 I would get a return of 002, 102, 048, 120.

....

If you enter the number to match in a cell named v, then you could use
the following array formula with the first result in cell E1.

E1 [array formula]:
=INDEX(Tbl,INT(SMALL(IF(COUNTIF(OFFSET(Tbl,ROW(Tbl)-CELL("Row",Tbl),0,1,),v)
*(Tbl<>v)>0,(ROW(Tbl)*100000+COLUMN(Tbl))),COLUMNS($E1:E1))/100000),
MOD(SMALL(IF(COUNTIF(OFFSET(Tbl,ROW(Tbl)-CELL("Row",Tbl),0,1,),v)*(Tbl<>v)>0,
(ROW(Tbl)*100000+COLUMN(Tbl))),COLUMNS($E1:E1)),100000))

Fill right as far as needed.