Revise!! Excel VBA Code question?

[Javi]

I am totally failing at this one.

Column B has multiple duplicates, Column K has my 2nd criteria.

All matches in column B with only 0 in column K needs to be indicated in column 0 with a true or false or whatever anthing to sort by.

If a match single or group has a value other than 0 in column K it needs to be ignored or indicated with a different indicator so I may sort by.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
I would post what I have to this point however nothing is working.

 

[Biz]

You're welcome. Now that Biz has provided a macro solution for the formula you have the best of both worlds

Learning Vba from Excel gurus in Mr Excel.

Biz

 

[Javi]

Can you help me understand the formula? This is the part I'm not understanding. RC[-13])=1,RC[-4]=0


"=IF(AND(COUNTIF(R2C2:R40C2,RC[-13])=1,RC[-4]=0),""Keep"",""Delete"")"

 

[Trebor76]

RC[-13])=1,RC[-4]=0

As the formula was converted to a macro using the R1C1 reference style, this is how non absolute (non anchored, i.e. no $) references are shown.

The following maybe easier to follow:

Option Explicit
Sub Macro3()

    'http://www.mrexcel.com/forum/newreply.php?do=newreply&noquote=1&p=2876479

    Dim lngLastRow As Long
    
    lngLastRow = Range("B" & Rows.count).End(xlUp).Row 'Finds the last row from Col B.  Change to suit.

    With Range("O2:O" & lngLastRow)
        .Formula = "=IF(AND(COUNTIF($B$2:$B$" & lngLastRow & ",B2)=1,K2=0),""Keep"",""Delete"")"
        .Value = .Value
    End With

End Sub

This will also automatically adjust the formula to the last row in column B.

HTH

Robert

 

[Javi]

Thank you i will try as soon as excel gives me back my computer. Every time I fillter, autosave or select a column it takes 10 min to calculate. do you have code to change the calculation mode to manual and or automatic for this workbook?

 

[Biz]

As the formula was converted to a macro using the R1C1 reference style, this is how non absolute (non anchored, i.e. no $) references are shown.

The following maybe easier to follow:

Option Explicit
Sub Macro3()
 
    'http://www.mrexcel.com/forum/newreply.php?do=newreply&noquote=1&p=2876479
 
    Dim lngLastRow As Long
 
    lngLastRow = Range("B" & Rows.count).End(xlUp).Row 'Finds the last row from Col B.  Change to suit.
 
    With Range("O2:O" & lngLastRow)
        .Formula = "=IF(AND(COUNTIF($B$2:$B$" & lngLastRow & ",B2)=1,K2=0),""Keep"",""Delete"")"
        .Value = .Value
    End With
 
End Sub

This will also automatically adjust the formula to the last row in column B.

HTH

Robert

Hi Robert,

Like your variable version.

Biz

 

[Trebor76]

You can use the following to change the calculation method, but be aware that this could cause inaccuracies:

Option Explicit
Sub Macro4()

    Dim blnAutoCalc As Boolean
    
    'Ensure the calculation method is set to manual
    If Application.Calculation = xlCalculationAutomatic = True Then
        blnAutoCalcFlag = True
        Application.Calculation = xlCalculationManual
    End If
    
    '//Your code here//
    'Be aware though that uncalculated results can lead to incorrect assumptions!!
    
    'Switch the calculation method back to what it was prior to ensuring it is set to manual
    If blnAutoCalcFlag = True Then
        Application.Calculation = xlCalculationAutomatic
    End If
    
End Sub

I know Excel 2007 is resource hungry, but it seems strange it takes so long to calculate

Like your variable version.

Thanks Biz

 

[Javi]

The below link shows the data after I used the macro as well as the formula.

When I was validating the data it appears there some sort of issue.

Some lines with duplicate values in column B and 0 in column K are being marked delete instead of keep.

I did change the code\ formula to indicate the letter K or D to speed things up a little bit and it did help some.

I am way further than I was a day ago I think a little fine-tuning may get this. Thanks

http://www.megaupload.com/?d=A2HP4XZC

 

[LxQ]

So, the COUNTIF() returns more than 1 if the same values in B are listed more than once. This will result in FALSE on the COUNTIF()=1. Maybe a COUNTIF()>0 would work.

Edit: Actually, what do you want to do with a material that is listed twice but not all values in K are 0's? Maybe try this:
=IF(AND(COUNTIF($B$2:$B$40,B4)>0,SUMIF($B$2:$B$40,B4,$K$2:$K$40)=0),"K","D")

 

[Javi]

I get false "Delete" for all when I change to 0.

 

[Javi]

Mark as delete.


Edit: Actually, what do you want to do with a material that is listed twice but not all values in K are 0's? Maybe try this:
=IF(AND(COUNTIF($B$2:$B$40,B4)>0,SUMIF($B$2:$B$40,B4,$K$2:$K$40)=0),"K","D") <!-- / message --><!-- edit note -->