Solved

VBA code to keep or remove

Posted on 2014-04-29
4
201 Views
Last Modified: 2014-04-29
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
Comment
Question by:Jagwarman
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 70

Expert Comment

by:Qlemo
ID: 40029463
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
 
LVL 33

Expert Comment

by:Rob Henson
ID: 40029473
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
 
LVL 51

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 40029483
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
 

Author Closing Comment

by:Jagwarman
ID: 40029671
BRILLIANT thanks Rgonzo1971
0

Featured Post

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question