Problem with Multiple Filter

**brandedadnan** · 04-02-2013, 10:41 AM

Dear All,
I am facing a problem in the excel file with multiple filter.

My problem is, As shown in the attached excel file.
In one column of the file, I have ingredients code no and selling item code number,
Ingredient code starts with 1001 to 1500
selling item starting alphabets and the whole code like sa005, am009, am023 etc.

now I want a filter which will select all the selling item code with only one ingredient, so that I can find out the total consumption of this ingredient.

Currently I select all items in the columns and then manually go and deselect all 500 ingredients and then get the output as shown in the output.

I am convinced that there has to be a better way to solve this.
Kindly assist
Thanks

**benishiryo** · 04-02-2013, 10:50 AM

hi there. what do you mean 'selling item code with only one ingredient"? i assume it's all those in text & 1001? in Sheet2, type in this formula somewhere say in D3:
=OR(ISTEXT(A3),A3=1001)

go to Data -> Advanced:
List Range: $A$3:$A$97
Criteria Range: $D$2:$D$3
the criteria range has to have a blank cell above it.

**brandedadnan** · 04-02-2013, 01:39 PM

thanks for your reply.
the selling item code is sa001, sa005, tm08 and be008 and so on...
however 1001 to 1500 are the raw material code.

So i want to arrive at consumption of only selected raw material code like 1001 and 1002 and like wise.
suppose 1001 is used in say 10 items. than i can find out those items sales through v look up and arrive at the total consumption of 1001.

So answering question i want all the selling item that has raw material 1001 through advanced filter or some formula.

If there are any doubts pls ask me..

thanks

and repeat the process for any raw material needed.

**brandedadnan** · 04-03-2013, 02:59 AM

Thanks for your help. it worked beautifully, surprisingly it did not work well yesterday. may be a mistake here or there by me.

Is it possible to have only those records come where there is 1001 raw material meaning, those selling item that does not have 1001 raw material code. will not appear in advanced filter.

thanks once again.

Rgds