VBA advanced filter - uniqe items + conditions

[neveu]

hello forum,

i would need your help with the folowwing issue:

i have list of vendors with the folowing format:

col A - list of vendor code
col K - the corespondign amnt

there can be multiples lines with the same vendor code in list A.

what i would like to do is the folowing:
to extract from the whole list the list of unique vendors code that meet the folowing criterias:
1) the vendor code appears in the list more than 1 time
2) the amnts in the col K have to have both pozitive and negative amnts for the same vendor code [ in other words the extracted list of vendors code should exclude those vendors code for which there are either only pozitive or either only negative amnts


for ex:

COL A col K
1 -100
1 -100
2 2453
3 100
3 200
4 -100
4 45
5 -98
6 -1776
6 45
6 67

for this case the result shoul be displayed in col N as such:
vendor code:
4
6


thank you in advance

 

[Peter_SSs]

Valentin

I'm a bit confused too, but I usually just put that down to my age.

Is this latest check in addition to the other checks? In other words do you want a unique list of vendors that:
- appear more than once in the list, AND
- col K has both positive and negative numbers, AND
- col D begins with 74 OR 75 OR 36

I'm also not quite sure why your latest formula refers to row 3 (is that the first row of actual data?) or about the K3<0 condition?

Could you try to describe the full set of requirements that you now want?

Is is possible to give a fairly small set of smaple data and expected results that demonstrates these conditions as you want them applied?

 

[JackDanIce]

Peter, I put it down to the vagueness and unspecification of the question being asked and of course, the lack of any pyschic powers or abilities of the reader. However, I do apportion some of this blame to my lack of X-Men gene mutation, curse my parents.

 

[neveu]

hello Peter,
i'm teribly sorry for the confusion.

i confirm that what i want ot do is what you stated:

Is this latest check in addition to the other checks? In other words do you want a unique list of vendors that:
- appear more than once in the list, AND
- col K has both positive and negative numbers, AND
- col D begins with 74 OR 75 OR 36 and the coresponding amnt from column K is negative


please find below some data set:

Uploaded with ImageShack.us

 

[Peter_SSs]

The image is not much use to me:
- It is too small to read.
- I am not able to copy the data from the image to my test sheet. (For the future, it is much better if you can post a small screen shot directly in your post. My signature block has three methods that can be used to do that.)

However, your image did tell me that you are using Excel 2007.

Not sure if I am missing something simple but I have not been able to get the extra conditions to work directly with Advanced Filter but see it this alternative is any use. It still uses column Z as a helper column.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Vendor_List_2()<br>    <SPAN style="color:#00007F">Dim</SPAN> LR <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    <SPAN style="color:#00007F">Const</SPAN> fBase <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "=IF(AND(" & _<br>                            "COUNTIF($A$1:$A$#,A1)>1," & _<br>                            "COUNTIFS($A$1:$A$#,A1,$K$1:$K$#,"">0"")>0," & _<br>                            "COUNTIFS($A$1:$A$#,A1,$K$1:$K$#,""<0"")>0," & _<br>                            "K1<0," & _<br>                            "OR(LEFT(D1,2)={""74"",""75"",""36""})" & _<br>                            "),A1,"""")"<br>    <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    LR = Range("A" & Rows.Count).End(xlUp).Row<br>    <SPAN style="color:#00007F">With</SPAN> Range("Z1:Z" & LR)<br>        .Formula = Replace(fBase, "#", LR)<br>        .Value = .Value<br>        .Cells(1, 1).Value = Range("A1").Value<br>        <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN><br>        .SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp<br>        <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0<br>        .AdvancedFilter Action:=xlFilterCopy, _<br>            CopyToRange:=Range("N1"), Unique:=<SPAN style="color:#00007F">True</SPAN><br>        .ClearContents<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>    </FONT>