Solved

Excel compare and move

Posted on 2012-03-22
2
169 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

ScreenConnect 6.0 Free Trial

Check out the updates in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI that improves session organization and overall user experience. See the enhancements for yourself!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Search field that goes to row 7 45
No visible Table Names in Excel 2013 4 46
Order table with macro 3 85
TT Auto Dashboard 13 92
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
: Microsoft Office Collaborate for free and online versions of Microsoft  Word, Excel, Powerpoint, OneNote, Onedrive , Email, Calendar etc. In short we can say that Microsoft office is a suite of servers, applications and services developed by  Micr…
Viewers will learn the basics of the new Quick Analysis feature in Excel 2013.
Viewers will learn how to share Excel data with others from desktop Excel, as well as Excel Online via OneDrive, and embed an Excel file on a website.

777 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