Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc,microsoft.public.excel
|
|||
|
|||
filtering
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 |
#2
Posted to microsoft.public.excel.misc,microsoft.public.excel
|
|||
|
|||
filtering
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 DataFilterAdvanced 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 wrote: 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 Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#3
Posted to microsoft.public.excel.misc,microsoft.public.excel
|
|||
|
|||
filtering
Dear Debra,
Are you sure this is working ? I don't understand about "--($A$2:$A$12=A2)" in your Sum Product formula Regards. "Debra Dalgleish" wrote in message ... 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 DataFilterAdvanced 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 wrote: 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 Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#4
Posted to microsoft.public.excel.misc,microsoft.public.excel
|
|||
|
|||
filtering
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 wrote: Dear Debra, Are you sure this is working ? I don't understand about "--($A$2:$A$12=A2)" in your Sum Product formula Regards. "Debra Dalgleish" wrote in message ... 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 DataFilterAdvanced 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 wrote: 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 Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#5
Posted to microsoft.public.excel.misc,microsoft.public.excel
|
|||
|
|||
filtering
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 "Debra Dalgleish" wrote in message ... 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 DataFilterAdvanced 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 wrote: 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 Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Row filtering based on input box entry (column heading) | Excel Worksheet Functions | |||
display count/results of filtering Excel in status bar | Excel Discussion (Misc queries) | |||
trouble filtering a list. Why isn't column filtering? | Excel Worksheet Functions | |||
avanced filtering for latest date | Excel Discussion (Misc queries) | |||
Using Filtering | Excel Discussion (Misc queries) |