Solved

VBA code to keep or remove

Posted on 2014-04-29
4
196 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
4 Comments
 
LVL 68

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 32

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 49

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

911 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now