Solved

VBA code to keep or remove

Posted on 2014-04-29
4
200 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 69

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 50

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Get Started with Digital Signatures 4 26
using vb script 5 52
Formula returning #N/A 9 35
Excel + % to Goal when number is negative 21 28
A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

697 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