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]

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.

Can you post a sample file using megaupload

Alternatively ,try using Excel jeanie, MrExcel HTML Maker or Borders-Copy-Paste to post your sample.

Biz

 

[Javi]

A small amount of the data is at the link below. Thanks for taking your time to look at.

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

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

 

[Trebor76]

Hi Javi,

You don't need a macro to do this, i.e. put this formula...

=IF(AND(COUNTIF($B$2:$B$40,B2)=1,K2=0),"Keep","Delete")

...into cell O2 and then fill down from O3 to O40.

HTH

Robert

 

[Javi]

No sure whats up but it's merging cells in column O. 2 rows at a time.

 

[Javi]

Sorry that was my error with the merge. The formula is very slow with 85K rows would vba work any better?

 

[Trebor76]

The formula is very slow with 85K rows would vba work any better?

No. Macros are generally less efficient than native excel formulas.

Make sure you're not referencing entire columns and try converting the formulas to values (though this will mean you'll have to re-enter the formula when rows are added and/or the data changes).

 

[Javi]

Thank you very much!! It just finished about 15 min total calcauation time. I will validate the data now.. Thanks again.

 

[Biz]

Try code below. It creates formula and then paste special values.

Sub Test()
    Dim LR As Long
    LR = Range("A" & Rows.Count).End(xlUp).Row
    With Range("O2:O" & LR)
        .FormulaR1C1 = "=IF(AND(COUNTIF(R2C2:R40C2,RC[-13])=1,RC[-4]=0),""Keep"",""Delete"")"
        .Value = .Value
    End With
End Sub

Does this help?

Biz

 

[Trebor76]

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