Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 211
  • Last Modified:

VBA code to keep or remove

I need to find BKIDQ in column 'I' then look in column 'H' and for every item in 'H' that is the same keep it. i.e.

H      I      X
94874      CLOVC      Keep
94874      BKIDQ      keep
93470      SU006      Remove
93470      CLOVC      Remove
50674      SUI6C      Remove
50674      CLOVC      Remove
74822      CLOVC      Remove
74822      BSMIL      Remove
16840      CLOVC      keep
16840      BKIDQ      keep
26468      BSMAD      Remove
26468      CLOVC      Remove
26520      BSMAD      Remove
26520      BSMIL      Remove
51972      BSMIL      keep
51972      CLOVC      keep
51972      CLOVC      keep
51972      BKIDQ      keep

This is part of a larger VBA code so I am looking for this to be done in VBA please.

Thanks
0
Jagwarman
Asked:
Jagwarman
1 Solution
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Assuming a [H:H] sort (descending or ascending), a more straightforward approach is to search downwards for the first occurance of BKIDQ, and if H is the same, keep, else remove.

With "remove", do you mean to physically remove the complete row?
0
 
Rob HensonFinance AnalystCommented:
Not sure about doing it in VBA but you could add a couple of helper columns which would then maybe be used as a comparison for the VBA script.

Concatenate H & I into a single string and then compare the contents of H with "BKIDQ" added to see if there is that combination, if found that row should be kept.

Helper column:
=H2&"-"&I2
includes a "-" in the middle but doesn't have to.

Column X, Keep or Remove:
=IF(ISERROR(MATCH(H2&"-"&"BKIDQ",$W$2:$W$19,0)),"Remove","Keep")

Where W2:W19 is range of concatenated strings.

Thanks
Rob H
0
 
Rgonzo1971Commented:
Hi,

pls try

Set myRange = Range(Range("X2"), Range("X" & Range("I1").End(xlDown).Row))
myRange.FormulaR1C1 = "=IF(COUNTIFS(C[-15]," & Chr(34) & "BKIDQ" & Chr(34) & ",C[-16],RC[-16])," & Chr(34) & "Keep" & Chr(34) & "," & Chr(34) & "Remove" & Chr(34) & ")"

Open in new window

Regards
0
 
JagwarmanAuthor Commented:
BRILLIANT thanks Rgonzo1971
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now