Solved

Excel 2007 Routine Requested to Delete Matching Records

Posted on 2013-10-29
8
189 Views
Last Modified: 2013-12-19
All,

  I have a list of servers in Column A. In columns B and more; I have a list of applications on each server (with the servers named in each 'entry' [row]).
 
  What code/procedure can be constructed to search all then entries in column B for each server (or every item [one word per row]) in Column A against every row in ColumnB; and remove the server(name) when there is any match; only leaving Column A with names not found in Column B data?

  This 'process' compares both colums. If any of the servernames (column A) match any 'records' in column B; delete that servername (from Column A). Repeat for all elements in Column A.


Any ideas?


Marty Nickison
0
Comment
Question by:afro4cboss
[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
  • 4
8 Comments
 
LVL 12

Expert Comment

by:Harry Lee
ID: 39610075
Can you check if the following macro would work for you?

PLEASE make a backup before testing.

Sub CleanUpRecord()
Dim i As Long, ws As Worksheet, ChkRng As Range, Chk As Range, ChkRnglr As Long

Application.ScreenUpdating = False
Set ws = ActiveSheet
ChkRnglr = ws.Cells(Rows.Count, 2).End(xlUp).Row
Set ChkRng = ws.Range(Cells(1, 2), Cells(ChkRnglr, 2))

For i = ws.Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
    For Each Chk In ChkRng
        If ws.Cells(i, 1).Value = Chk.Value Then
            ws.Cells(i, 1).Delete shift:=xlUp
        End If
    Next
Next
Application.ScreenUpdating = True
End Sub

Open in new window

0
 

Author Comment

by:afro4cboss
ID: 39611553
Is there anyway to use a nested IFxxx... or MATCH statement to do this?
0
 
LVL 12

Expert Comment

by:Harry Lee
ID: 39611955
You would end up with a new column, and a lot of blank rows if you want to use If and Match statement to do so.

Is that what you want?
0
RoboForm Secure Password Management System

RoboForm Everywhere - Superb Browser Support
Windows / Apple / IOS / Android / Linux / Chrome OS
Use different complex passwords everywhere
Best Secure Password Management by far
Synchronize all of your devices instantly
Safe, Secure & Highly Recommended!

 

Author Comment

by:afro4cboss
ID: 39612248
Yes, having a new column would not hurt in any way.
0
 
LVL 12

Expert Comment

by:Harry Lee
ID: 39612403
In that case,

1) Add a column between Column A and Column B

2) In the newly added Column (the new Column B) B2, type in the following formula
  =IF(ISERROR(MATCH($A2,$C:$C,0)),A2,"")

3) In the newly added Column B3, type in the following formula
  =IF(AND(ISERROR(MATCH($A3,$C:$C,0)),ISERROR(MATCH($A3,$A$2:A2,0))),$A3,"")

5) Copy the formula in B3, all the way down your list.

That would force each server name to show up only once in the newly added column.

If you don't care if the server name shows up only once in the new column, instead of copying the formula in B3 all the way down, copy the formula in B2 all the way down.

The different between the two formulas is B2 formula does not check if the same server name exist in column B; while the B3 formula checks if the server name has already exist above.

When you are done, feel free to Cut and Paste the newly added column anywhere you want.
0
 

Author Comment

by:afro4cboss
ID: 39612738
This will work, but only finds the exact match for the entire cell contents. That's part of the data set I want 'flagged'. But, for example:


  A                      C                 D

1  Dog                                    Dog
2  Cat                                     Cat Power
3  Whale                                I am a Whale


In your code above, the first search for A1 would find Dog. However, the code above would not find Cat Power in the B1 search...and I would need this flagged as well.



Any thoughts...we are almost there !!
0
 
LVL 12

Accepted Solution

by:
Harry Lee earned 500 total points
ID: 39612834
Well,

Try this on B2 all the way down the list.

=IF(AND(ISERROR(MATCH(A2,C:C,0)),ISERROR(MATCH("* "&A2,C:C,0)),ISERROR(MATCH(A2&" *",C:C,0)),ISERROR(MATCH(A2,$B$1:B1,0)),ISERROR(MATCH("* "&A2,$B$1:B1,0)),ISERROR(MATCH(A2&" *",$B$1:B1,0))),A2,"")

Open in new window


I assume you have Header Row on your sheet.
0
 

Author Closing Comment

by:afro4cboss
ID: 39729621
Thanks for all the help. I created multiple tables and then setup a a query per 5 letters of the alphabet for the keywords to target. Then, I did an import to a 'final results' table of all the query results.
0

Featured Post

Industry Leaders: 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

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.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

732 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