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

 

[JackDanIce]

This code provides for Part 1) of your request below:

Sub mnew()
 
Dim i As Long, j As Long
 
With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With
 
j = Range("A" & Rows.Count).End(xlUp).Row
Range("A1:A" & j).AdvancedFilter Action:=xlFilterCopy, _
    CopyToRange:=Range("N1"), Unique:=True
 
For i = Range("N" & Rows.Count).End(xlUp).Row To 2 Step -1
    With Range("O" & i)
        .Value = Application.CountIf(Range("A2:A" & j), Range("N" & i))
        If .Value < 2 Then
            Range("N" & i & ":O" & i).Delete shift:=xlUp
        Else
            .ClearContents
        End If
    End With
Next i

With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With
            
End Sub

I'll try to do the second part when I come back from lunch unless someone else on here answers sooner

 

[Peter_SSs]

Test this in a copy of your workbook. It assumes Z1:Z2 are clear and available to use as helper cells.

This also assumes you are using Excel 2007+ and that the list has a heading.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Vendor_List()<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> = "=AND(COUNTIF($A$2:$A$#,A2)>1," & _<br>                            "COUNTIFS($A$2:$A$#,A2,$K$2:$K$#,"">0"")>0," & _<br>                            "COUNTIFS($A$2:$A$#,A2,$K$2:$K$#,""<0"")>0)"<br>    <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    LR = Range("A" & Rows.Count).End(xlUp).Row<br>    Range("Z2").Formula = Replace(fBase, "#", LR)<br>    Range("A1:A" & LR).AdvancedFilter Action:=xlFilterCopy, _<br>        CriteriaRange:=Range("Z1:Z2"), CopyToRange:=Range("N1"), _<br>        Unique:=<SPAN style="color:#00007F">True</SPAN><br>    Range("Z2").ClearContents<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>



If you are using Excel 2003-, replace the relevant line with this:

<font face=Courier New><SPAN style="color:#00007F">Const</SPAN> fBase <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "=AND(COUNTIF($A$2:$A$#,A2)>1," & _<br>                        "SUMPRODUCT(--($A$2:$A$#=A2),--($K$2:$K$#>0))>0," & _<br>                        "SUMPRODUCT(--($A$2:$A$#=A2),--($K$2:$K$#<0))>0)"</FONT>

 

[neveu]

Hi Jack,
your macro works great but it doesn't exclude from the result list the vendors code that have just pozitive amnts or just negative amnts.

do you think you could tune it up?
otherwise, it works great

thank you

 

[JackDanIce]

Please see the last line I typed after the code I posted and then read Peter's reply

 

[neveu]

Hi Jack,
sorry for missing out that piece of information.

Peter,
thank you very much for your help.
I've tested the code and works perfect.

thank you

 

[neveu]

Jack,

could you please give me a hint how to modify the macro to extract now the list of accounts that contains only negative amnts?\

thank you

 

[JackDanIce]

Reorder your data by the amounts column, split it apart into two sheets of non-negative and negative values and then run my macro on the sheet with the negative values only

 

[neveu]

Hello Peter,

i would need your help to add another condition to the above formula.
i manged to do it with the excel formula but i would need it to incorporate it in the VBA code you've sent.

basically, the formula checks on column D if the first 3 leters are BEZ or Db. if this condition is met than it's ok.

OR(LEFT(TRIM(D51),2)="db",LEFT(TRIM(D51),3)="BEZ")

could you please help me included in the VBA code?

thank you,
Valentin

 

[neveu]

sorry,
please ignore the above request - I've been confused.

here is what i would need to do:

Hello Peter,

i would need your help to add another condition to the above formula.
i manged to do it with the excel formula but i would need it to incorporate it in the VBA code you've sent.

basically, the formula checks on column D if the first 2 characters are 74 or 75 or 36

AND(K3<0,OR(LEFT(TRIM(D3),2)="74",LEFT(TRIM(D3),2)="75",LEFT(TRIM(D3),2)="36"))

could you please help me included in the VBA code above?

thank you,
Valentin