Function to seperate Partial name from cell

[Farooque]

Hi every one
I need help to seperate rows from a worksheet which contains partial names in column A


Sampl file is attched. I want to seperate all rows from Sheet-1 to Sheet-2 containing words as shown in sheet-2

Waiting your response with thanks

[Marcol]

This sounds like a case for "Advanced Filter"
See this link
Filter by using advanced criteria
I have set up and executed an example in "Sheet1 (2)"

With 2003 I think you might struggle with formula due to nesting limitations.
See if the Example on "Sheet2" is anything Like what you need, it only works for one condition.
Select from the dropdown in C3 to see the results.
Column A (Row) can be hidden with the Grouping Button.

Hope this helps.

[dangelor]

Possibly...

Please Login or Register  to view this content.

[Farooque]

Thanks you very much but I think that i could not tell you proper requriement

I want to select / copy / seperate only thoses rows which contains specific words like: CEFUROXIME, SODIUM, AXETILE and so on

Remaining rows are not required to copy / seperation

Thanks in advance

[Marcol]

My advice to you is to learn how Advanced Filter works, it's easy to give you a macro to solve your problem, but I feel you will not benefit from this.

For example with the workbook provided in Post #2
Add a new sheet ("Sheet3")
Then add and run this macro

Please Login or Register  to view this content.

However as "Sheet1 (2)" was set up this only filters for whole words in the strings.
e.g. in B3 we have

Please Login or Register  to view this content.

This returns =* SODIUM * this is what the advanced filter looks for, but you have entries that contain SODIUM:STERILE and they will be ignored.
To include these rows we would need
In B3

Please Login or Register  to view this content.

Then you have entries that contain CEFTRIAXONE:SODIUM:STERILE and they will be ignored.
So to include these ...
In B3

Please Login or Register  to view this content.

but this will include any entries that have DISODIUM in the string, would this be correct?

The point I am trying to make is, that often to get the final result that you need, the list will have to be filtered and copied several times and the best way is to use Advanced Filter and Auto filter.

[dangelor]

Is this better?

Please Login or Register  to view this content.

[Marcol]

@ dangelor

So what happens if say SODUIM is at the begining or end of the string?
You will need

Please Login or Register  to view this content.

now you'll miss SODIUM:STERILE and CEFTRIAXONE:SODIUM:STERILE .
VBa will become very messy!
See post #5 for reasons why, remember that this applies to all the searchwords