filtering

[magix]

Dear Guru,

I have problem with filtering process in term of coding in Excel VBA.

Here is my scenario, To make it simple, let say I have this record, and I
would like to have list of customer
who has Apple from New York, provided that customer will not have Orange
from New York.

Cust Fruit Place
===================
111 Apple NewYork
111 Banana NewYork
222 Apple NewYork
222 Banana NewYork
222 Orange NewYork
333 Apple NewYork
555 Banana NewYork
555 Orange NewYork
666 Apple NewYork
777 Orange NewYork
777 Apple NewYork

The output I want is:

111 Apple NewYork
333 Apple NewYork
666 Apple NewYork


Note: For Apple in Customer 222 and 777 are NOT selected because this
customer has ORANGE

So, what is the suitable coding to search through multiple rows for same
customer , and do filtering there based on same customer number ?

I hope you got what I mean.

Thanks.

Regards, Magix

 

[Debra Dalgleish]

You can do this with an Advanced Filter.
Create a criteria range, with information in the following cells:

F1: Fruit
G1: Place
H1: leave this cell blank
F2: Apple
G2: NewYork
H2:
=SUMPRODUCT(--($B$2:$B$12="orange"),--($C$2:$C$12=$G$2),--($A$2:$A$12=A2))=0

Choose Data>Filter>Advanced Filter
Select Copy to another location
Select your list for the list range
Select cells F1:H2 as the criteria range
Select a cell as the Copy to starting cell
Click OK

 

[magix]

Dear Debra,

Are you sure this is working ?
I don't understand about "--($A$2:$A$12=A2)" in your Sum Product formula

Regards.

 

[Roger Govier]

Hi

It works just fine. Try it.

"--($A$2:$A$12=A2)" is just testing the Customer number against the range of
Customer numbers as part of the overall Sumproduct formula
=SUMPRODUCT(--($B$2:$B$12="orange"),--($C$2:$C$12=$G$2),--($A$2:$A$12=A2))=0

The formula returns a series of True's or False's for each part, and these
are coerced to 1 or 2 by the double unary minus signs "--".
So with your data and for customer 111 (value in A2) it would return
False * True * True which is coerced to 0 * 1 * 1 = 0 for the first row
The second row would be 0 * 1 * 1 = 0
When it gets to the fifth row (Customer 222), it is 1 * 1 * 1 = 1, so this
would fail the test of being = 0, hence Customer 222 would be excluded from
the list exactly as required, for whilst he is in New York and does sell
apples, he also sells Oranges.

Regards

Roger Govier

 

[magix]

Hi Debra,

I tried, but it's not working. In addition, I don't understand why we need
"--($A$2:$A$12=A2)".

Regards, Magix