Excel Filtering Help

[bala06]

Dear Excel users

I would like to filter data my excel sheet accrodiung to particlular number & the number is "4904".

For example my data looks like

985-hydrogen-bond-frame.dat.c.d.lw 985-hydrogen-bond-frame.dat.c.d.pw [(41106, 'SPC', 'X4967G', 4922, 'UNK', 'Z 900B', 41107, 'SPC', 'X4967G', 3890, 'HIS', 'A 296B')]
1032-hydrogen-bond-frame.dat.c.d.lw 1032-hydrogen-bond-frame.dat.c.d.pw [(4904, 'UNK', 'Z 900B', 51812, 'SPC', 'X8535G', 2432, 'GLN', 'A 179B', 51810, 'SPC', 'X8535G')]
537-hydrogen-bond-frame.dat.c.d.lw 537-hydrogen-bond-frame.dat.c.d.pw [(47967, 'SPC', 'X7254G', 4947, 'UNK', 'Z 900B', 47968, 'SPC', 'X7254G', 1335, 'ASP', 'A 113B')]
794-hydrogen-bond-frame.dat.c.d.lw 794-hydrogen-bond-frame.dat.c.d.pw [(47967, 'SPC', 'X7254G', 4947, 'UNK', 'Z 900B', 47968, 'SPC', 'X7254G', 2705, 'TYR', 'A 199B')]
948-hydrogen-bond-frame.dat.c.d.lw 948-hydrogen-bond-frame.dat.c.d.pw [(4904, 'UNK', 'Z 900B', 51112, 'SPC', 'X8302G', 51113, 'SPC', 'X8302G', 3962, 'ASP', 'A 300B')]

And in my data the number "4904" lies at different positions.

From the above data, after filtering I should get like this.

1032-hydrogen-bond-frame.dat.c.d.lw 1032-hydrogen-bond-frame.dat.c.d.pw [(4904, 'UNK', 'Z 900B', 51812, 'SPC', 'X8535G', 2432, 'GLN', 'A 179B', 51810, 'SPC', 'X8535G')]

948-hydrogen-bond-frame.dat.c.d.lw 948-hydrogen-bond-frame.dat.c.d.pw [(4904, 'UNK', 'Z 900B', 51112, 'SPC', 'X8302G', 51113, 'SPC', 'X8302G', 3962, 'ASP', 'A 300B')]

I have attached my data for your reference.

Kindly advice.

Many Thanks
Balaji

 

[T. Valko]

Dear Excel users

I would like to filter data my excel sheet accrodiung to particlular number & the number is "4904".

For example my data looks like

985-hydrogen-bond-frame.dat.c.d.lw 985-hydrogen-bond-frame.dat.c.d.pw [(41106, 'SPC', 'X4967G', 4922, 'UNK', 'Z 900B', 41107, 'SPC', 'X4967G', 3890, 'HIS', 'A 296B')]
1032-hydrogen-bond-frame.dat.c.d.lw 1032-hydrogen-bond-frame.dat.c.d.pw [(4904, 'UNK', 'Z 900B', 51812, 'SPC', 'X8535G', 2432, 'GLN', 'A 179B', 51810, 'SPC', 'X8535G')]
537-hydrogen-bond-frame.dat.c.d.lw 537-hydrogen-bond-frame.dat.c.d.pw [(47967, 'SPC', 'X7254G', 4947, 'UNK', 'Z 900B', 47968, 'SPC', 'X7254G', 1335, 'ASP', 'A 113B')]
794-hydrogen-bond-frame.dat.c.d.lw 794-hydrogen-bond-frame.dat.c.d.pw [(47967, 'SPC', 'X7254G', 4947, 'UNK', 'Z 900B', 47968, 'SPC', 'X7254G', 2705, 'TYR', 'A 199B')]
948-hydrogen-bond-frame.dat.c.d.lw 948-hydrogen-bond-frame.dat.c.d.pw [(4904, 'UNK', 'Z 900B', 51112, 'SPC', 'X8302G', 51113, 'SPC', 'X8302G', 3962, 'ASP', 'A 300B')]

And in my data the number "4904" lies at different positions.

From the above data, after filtering I should get like this.

1032-hydrogen-bond-frame.dat.c.d.lw 1032-hydrogen-bond-frame.dat.c.d.pw [(4904, 'UNK', 'Z 900B', 51812, 'SPC', 'X8535G', 2432, 'GLN', 'A 179B', 51810, 'SPC', 'X8535G')]

948-hydrogen-bond-frame.dat.c.d.lw 948-hydrogen-bond-frame.dat.c.d.pw [(4904, 'UNK', 'Z 900B', 51112, 'SPC', 'X8302G', 51113, 'SPC', 'X8302G', 3962, 'ASP', 'A 300B')]

I have attached my data for your reference.

Kindly advice.

Many Thanks
Balaji

If you're using AutoFilter...

In the appropriate column...

Click the filter drop arrow
Select Custom
In the top left drop down select Contains
In the top right drop box enter 4904
OK

Or, you might want to use this criteria: (4904,

 

[bala06]

Dear Excel users

Since the value "4904" is in multiple columns I couldn't filter using autofilter.

Please help how to use criteria.

Kindly advice.

Many Thanks
Balaji

 

[ukmikeb]

Hi

In a separate "helper" column place the following -

=ISNUMBER(SEARCH(4904,$A2))

and copy down (adjust A2 accordingly)

Filter on the helper column for TRUE.

hth

 

[bala06]

Dear Mike

thanks a lot.

It works fine

Many Thanks
Balaji

 

[ukmikeb]

Balaji

Pleased to have helped solve your problem.

Thanks for the feedback.