Solved

Excel compare and move

Posted on 2012-03-22
2
167 Views
Last Modified: 2012-03-23
Hi All,

I need a bit of excel help. I'm trying to compare part numbers in Column B to part numbers in Column A, If part numbers in column B do NOT exist in A then move Cell B and C content to new sheet.

Row 1 has Labels for columns.

I would really appreciate some help.
0
Comment
Question by:bcontent
2 Comments
 
LVL 41

Accepted Solution

by:
dlmille earned 500 total points
ID: 37756322
App compares data in column B with column A using countif formula in temporary column IS.  When B doesn't match a, it is copied with column C to new sheet called "Output" and marked for deletion.  At the end, column B:C no match rows are deleted, shifting the cells up as part of the process:

Option Explicit

Sub comparePartsAndMove()
Dim wkb As Workbook
Dim wks As Worksheet
Dim wksOut As Worksheet
Dim r As Range
Dim chkRange As Range
Dim rngA As Range
Dim rngB As Range
Dim lastRow As Long
Dim i As Long
Dim vHeader As Variant
Dim rDelete As Range

Application.ScreenUpdating = False

    Set wkb = ThisWorkbook
    Set wks = wkb.ActiveSheet
    
    On Error Resume Next
    Set wksOut = wkb.Sheets("Output")
    If Err.Number = 0 Then
        wksOut.Cells.Clear
    Else
        Set wksOut = wkb.Sheets.Add(after:=wkb.Sheets(wks.Name))
        wksOut.Name = "Output"
    End If
    On Error GoTo 0
    
    vHeader = Application.Transpose(Application.Transpose(wks.Range("B1:C1")))
    
    wksOut.Range("A1").Resize(1, UBound(vHeader)).Value = vHeader
    
    lastRow = wks.Range("B" & wks.Rows.Count).End(xlUp).Row
    
    Set rngA = wks.Range("A2", wks.Range("A" & wks.Rows.Count).End(xlUp))
    Set rngB = wks.Range("B2", wks.Range("B" & wks.Rows.Count).End(xlUp))
    
    Set chkRange = wks.Range("IS2:IS" & lastRow) 'some column far to the right
    
    chkRange.Formula = "=COUNTIF(" & rngA.Address & ",$B2)"
        
    chkRange.Value = chkRange.Value
    
    i = 0
    For Each r In wks.Range("IS2:IS" & lastRow)
        If r.Value = 0 Then 'not found in column A
            If rDelete Is Nothing Then
                Set rDelete = wks.Range("B" & r.Row & ":C" & r.Row)
            Else
                Set rDelete = Union(rDelete, wks.Range("B" & r.Row & ":C" & r.Row))
            End If
            wks.Range("B" & r.Row & ":C" & r.Row).Copy
            wksOut.Range("A2").Offset(i, 0).PasteSpecial
            Application.CutCopyMode = False
            i = i + 1
        End If
    Next r
    
    'clean up
    wks.Range("IS:IS").ClearContents
        
    'delete the rows to complete the move
    If Not rDelete Is Nothing Then rDelete.Delete shift:=xlUp
    
    MsgBox "Process Complete, hit enter to see results", vbOKOnly, "Success!"
    wksOut.Activate
    
Application.ScreenUpdating = True

End Sub

Open in new window


See attached.

Enjoy!

Dave
partCompare-r2.xls
0
 

Author Closing Comment

by:bcontent
ID: 37758060
Hi dlmille,

Wow, you are amazing. Your solution worked Great. I was able to follow it clearly. The sample file really helped.

Thank you so much.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Excel 2010 very slow 13 234
Excel: if cell = x, add 4 40
excel formula for match and populate a cell 4 47
Excel Sheet Comparison Macros 16 52
Owning a franchise can be the dream of a lifetime. It provides a chance for economic growth. You can be as successful as you want.  To make your franchise successful, you need to market it successfully. Here are six of the best marketing strategies …
In this article, I show you step by step with screenshots to assist you - HOW TO: Deploy and Install the VMware vCenter Server Appliance 6.5 (VCSA 6.5), with some helpful tips along the way.
Viewers will learn how to create a PivotTable and make basic changes to it in Excel 2013.
Video by: Zack
Viewers will learn the basics of using Excel Tables, the benefits found with them, and some pitfalls.

760 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

17 Experts available now in Live!

Get 1:1 Help Now