Issue with multiple filter

[brandedadnan]

Dear All,
I was 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.

I posted this problem a long time ago and I found solutions using answer provided by benishiryo.

http://www.excelforum.com/excel-form...ml#post3182767

However since then, the no. of rows have increased and it became v tedius to remove all items that do not have 1001.

"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"

I am open to all kinds of solutions including vba.

Thanks
AT

[:) Sixthsense :)]

Your problem can be easily solvable, but need some clarification about it

Why you are marking 1001 for all these groups?

SA005
SA0010
AM022

Whereas 1001 is marked to AM023, AM019 individually

Please confirm the logic and at the same time what is the reason for allocating 1001 to these groups? Because of smallest value in the range?

[brandedadnan]

Dear Sixth Sense, thanks for your feedback,

sa005, sa0010, am022 are the sales item code, 1001 to 1500 whatever in between are the ingredients used in the selling this items.
So for example I want to know the total consumption of 1001 ingredient for a period.
I need to sort only those items, which have ingredients 1001, not all items. and then I can match with the sales and check consumption.


This is the solution.

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.

However, when I use the above method of benishriyo, I get all the items and i have to manually remove the items that does not contain the 1001,
So I need a formula, which will remove all the items that does not contain 1001,

I hope i was able to explain my issue and concerns.

[:) Sixthsense :)]

Refer the attached file for pivot solution

[sktneer]

Try this code........

Please Login or Register  to view this content.

Please find the attached sheet.
Is this what you want?

[brandedadnan]

Sorry Guys for late response, Year end a lot reporting issues.

Thanks sixthsense for your solution, I will work on it.

Thanks sktneer, I worked on ur solutions and results came immediately. It worked perfectly fine, However I have additional request. Instead of getting one column. can we increase it to three columns. in order to get not only the codes but also the name and qty consumed. so i can get the sales figures and get the total consumption.

I have added the same in file u sent and see the attachments.

Rgds
AT

[sktneer]

See the attachment.

[brandedadnan]

Hello sktneer,

Thanks for your solution.
But the problem with this solution is staying in the same example, If i were to remove 1001 from the sales code SA0010, then it will show SA0010, without the ing code 1001. So I have to go and remove manually all the codes that does not have 1001, thats v tedious task and I was getting that using is text and advanced filter before as well.

I need a code that will eliminate all the other codes that does not have 1001.

So in this case, sa0010 should not be part of the output.
I have highlighted the same in current output and Output needed.

Rgds
AT

[sktneer]

See this now.

[sktneer]

By mistake I uploaded the wrong sheet. I have edited my post and the correct sheet is Filter_Multiple_brandedadnan_2.xlsm

[brandedadnan]

Thanks Sktneer,
It worked perfectly fine. I am so thankful for all your efforts.

Thanks sixthsense, for your efforts as well.

Rgds
AT

[sktneer]

You're welcome. Glad to help you. Thanks for the feedback.