Data Sort with CTRL+F & extracting required cells

[MaheshK5277]

Dear All,

I have an excel file which has 10 columns, out of which i need to work on 3rd column which has list of equipments ( around 5000 equipments of a plant). This column is written with sub-headings as Raw material Processing Area, Utilities Area, Packaging area & each sub heading has different types of equipments ( like pumps, fans, mixers, agitators & son on..).

Requirement:
1 need to extract & make a separate list of equipments which are classified as per their type (like a mentioned above -pumps, fans agitators). I am doing CRTL+F to find a word match for PUMP. The window pops up showing me some 388 cells found. Is there a way to extract these cells which have PUMP in their listing & copy to another column with the entire row data? Any formula please?

Any quick help is sincerely appreciated.

I am attaching a example spreadsheet for ready reference

[MarvinP]

Hi Mahesh,

This is an "Advanced Filter" problem. You will need to create a criteria range and put "*pump*" below your column C row head. Then you can filter the data in place or filter it to another location. With a little VBA you can simply type in a word and press enter and your rows will filter in place.

http://www.contextures.com/xladvfilter01.html for a web page showing how to do Advanced Filters.

Find attached an old example that uses VBA and advanced filters to find names.

[MaheshK5277]

Hi Mahesh,

This is an "Advanced Filter" problem. You will need to create a criteria range and put "*pump*" below your column C row head. Then you can filter the data in place or filter it to another location. With a little VBA you can simply type in a word and press enter and your rows will filter in place.

http://www.contextures.com/xladvfilter01.html for a web page showing how to do Advanced Filters.

Find attached an old example that uses VBA and advanced filters to find names.

Dear Marvin,

Many thanks for your response & solution. Can you please help me to provide my example sheet with filters applied. i am not able to get through.

Thanks,
Mahesh

[stasinek]

Or maybe the system data and applied PT ?

[MaheshK5277]

Hi Stasinek,

Thanks for your work. Can you help with advanced filter approach instead. i need to copy the entire row data by sorting thro column 3 & create a separate sheet which gives me only list of equipments (like pumps,fans,mixers & so on...) & the corresponding data on rows for those "sorts". After this, i need to compile this data where i have more than 1000 rows to work on.

Or you may help thro a different approach other than filters. BTW i am not so good with pivot's.

Thanks,
Mahesh

[stasinek]

Show the example of what you want to filter and according to what parameters and how they look after filtrwaniu data

[MaheshK5277]

I am out of office and wud b back on monday. Will connect with you then... Thanks for your support.

Show the example of what you want to filter and according to what parameters and how they look after filtrwaniu data

[MaheshK5277]

Hi Stasinek,
If i can just summarize, check my excel file attached in the first post.
I want to extract all rows with a specific condition, i.e. For e.g, i need to
extract all rows which contain the word "pump" in column no.3( equipment description). This way i need to extract data for various other equipments like motors, fans, mixers. These data once sorted wud have to be compiled on a separate sheet which gives me the equipments grouped together whereby i can filter and do my next jobs. Intent is to find energy consumption of various equipments which are currently listed in column no3; with their entire technical names and this column shows the equipments by departments or process area; against which i need to compile this data based on type of equipments irrespective of their departments.
Hope i am able to clarify your query.
Anyways... Happy weekend and do check if you can help me with the above objective.
Thanks again...!

Show the example of what you want to filter and according to what parameters and how they look after filtrwaniu data

[stasinek]

https://youtu.be/DFowDLuKtjY
See a short video instructions, which I recorded to aid

[shukla.ankur281190]

Mahesh

Please see the attached file. I did the same thing in your file according @Marvin post#

Check the attachment.

[MaheshK5277]

Hi Ankur,

Your solution works perfect..!! I think you have done this with a Macro... but now i am stuck on How to apply this to my actual worksheet. I did Alt+F11, copied the macro & went to my worksheet & inserted Module & then i paste it there. however, i am not able to get through to modify the macro for range F1:F2, also not unable to unhide your row no. 2. Can you help me please?

[MaheshK5277]

Many thanks...It works like a charm... can you post the excel in ENGLISH language please...( YOU TUBE aid video)

You & Ankur have really helped me through...

Thanks,
Mahesh

[shukla.ankur281190]

@Mahesh If you are unable to use this in your original file,please share once here with output where you need the data and ranges and all that.

[MaheshK5277]

Hi Ankur,

i have attached my file. Request you to please elaborate the method so that i can replicate to other such work.

Thanks...!

[shukla.ankur281190]

There is two codes one for sheet and second is module

In same sheet below code

Please Login or Register  to view this content.

Module code is

Please Login or Register  to view this content.

now for advance filter - copy the header name of column C and paste it on I1 cell and put I2 ="*"&K1&"*" .

Go to Advance filter select the list range ( from C column) and criteria range (I1:I2)

Now you can search in K1 column as it is linked with I2 cell.

Hope, Now you understood how it works.

Check the attached file.

[MaheshK5277]

Thanks Ankur...It was of great help to me...
Also Thanks Stasinek...Your approach also worked well...

Mahesh

[shukla.ankur281190]

Hppy to help you please mark thread solved and add the reputation if you liked my solutions.