VBA advanced filter - uniqe items + conditions

neveu

Board Regular
Joined
Jan 27, 2009
Messages
225
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
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,887
Office Version
  1. 365
Platform
  1. Windows
This code provides for Part 1) of your request below:
Code:
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,413
Office Version
  1. 365
Platform
  1. Windows
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>
 
Last edited:

neveu

Board Regular
Joined
Jan 27, 2009
Messages
225
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

Well-known Member
Joined
Feb 3, 2010
Messages
9,887
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

neveu

Board Regular
Joined
Jan 27, 2009
Messages
225
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

Board Regular
Joined
Jan 27, 2009
Messages
225

ADVERTISEMENT

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

Well-known Member
Joined
Feb 3, 2010
Messages
9,887
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jan 27, 2009
Messages
225
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.

Code:
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

Board Regular
Joined
Jan 27, 2009
Messages
225
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
Code:
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
 

Forum statistics

Threads
1,136,275
Messages
5,674,782
Members
419,524
Latest member
helensesc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top