Comparing two spreadsheets

[Bob Loblaw]

I have spreadsheet 1 with 915 different "item numbers" listed in column A.
I have spreadsheet 2 with 8000 different "item numbers" listed in column A.

Spreadsheet 1 part numbers are dismantled Spreadsheet 2 numbers. Example:

Spreadsheet 1 item number: 123
Spreadsheet 2 item number: ABC, 123

How do I compare the two lists and in Spreadsheet 2 delete the entire row containing data from Spreadsheet 1? Each item number is unique in Spreadsheet 2.

Does this make any sense? I'm so confused I couldn't even figure out a title for what I'm trying to do. Help!

 

[Alphacsulb]

This sounds like a job for the Duplicate Master. It allows you to compare a range, and either give you a list of unique cells or values, or highlight them.

Can you give us a little bit more sample data? Excel Jeanie works well for this.

 

[Bob Loblaw]

The ranges look similar to this:


Sheet 1
<TABLE style="WIDTH: 65pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=87><COLGROUP><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3181" width=87><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 65pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 width=87 align=right>70071097755</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>70071097748</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=20 align=right>70071425931</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=20 align=right>60455035026</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=20 align=right>60455030944</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=20 align=right>60455030951</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=20 align=right>60455030969</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=20 align=right>70071376399</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=20 align=right>70071425592</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=20 align=right>70070446847</TD></TR></TBODY></TABLE>


Sheet 2
<TABLE style="WIDTH: 137pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=182><COLGROUP><COL style="WIDTH: 137pt; mso-width-source: userset; mso-width-alt: 6656" width=182><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 137pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl69 height=17 width=182>001, 60455030969 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl69 height=17>002, 70071169216 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl69 height=17>003, 70071214806 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl69 height=17>0038-2, 70071422912 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl69 height=17>004, 70071097755 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl69 height=17>005, 70071227378 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl69 height=17>006C, 60455035026 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl69 height=17>007-4, 70071131646 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl69 height=17>008, 70071425931 </TD></TR></TBODY></TABLE>

I need to compare Sheet 1 to Sheet 2 so that the entire row of a cell in Sheet 2 conatining data from Sheet 1 is removed.

 

[GTO]

Are you saying that "001, 60455030969" is in one cell, and that you'd like to see if just the number right of the comma (60455030969) is in sheet 1 column A?

 

[Bob Loblaw]

Very close. Just the other way around. I need the row in Sheet 2 to be removed if it contains data from Sheet 1.

 

[GTO]

Okay, I was not being clear. In the second workbook, the "numbers" are listed like "001, 604550309069", correct?

Assuming yes for the moment, can we count on the seperator being the only (or last) comma in the number?

What is the name of the worksheet in the first workbook and the worksheet in the second workbook?

Finally, are you okay with a vba solution?

 

[Bob Loblaw]

I'm sorry. Yes, you are correct. And the comma then space is the only separator in the cell. Right now they are truely called sheet1 and sheet2.
Forgive my naïveté, but what is a vba solution? Honestly, I dont care how it happens. Im just in a pickle.

 

[al_b_cnu]

Hi,

will this macro do it (save before executing!)

Option Explicit

Sub RemoveData()
Dim lPtr As Long
Dim oEntries As Object
Dim rCur As Range
Dim sKey As String, saKey() As String
Dim vData As Variant
Dim ws1 As Worksheet, ws2 As Worksheet

Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")

Set oEntries = Nothing
Set oEntries = CreateObject("Scripting.Dictionary")
For Each rCur In Intersect(ws1.Columns("A"), ws1.UsedRange)
    sKey = Trim$(CStr(rCur.Value))
    If sKey <> "" Then
        On Error Resume Next
        oEntries.Add key:=sKey, Item:=rCur.Row
    End If
Next rCur

vData = Intersect(ws2.Columns("A"), ws2.UsedRange).Value
For lPtr = 1 To UBound(vData, 1)
    saKey = Split("," & CStr(vData(lPtr, 1)), ",")
    If UBound(saKey) > 1 Then
        sKey = Trim$(saKey(2))
        If sKey <> "" Then
            If oEntries.exists(sKey) Then ws2.Rows(lPtr).Delete shift:=xlUp
        End If
    End If
Next lPtr
End Sub

 

[Bob Loblaw]

Help me out here. I copy and pasted what you had posted into a new macro. But when I run it, nothing happens.

 

[al_b_cnu]

Hi Bob,

did you insert the macro as follows:
1) [Alt-F11] to get to VB Editor
2) From the menu nsert / Module
3) Paste the supplied code into the code window?

The macro assumes that that the data is in column A