Comparing two spreadsheets

Bob Loblaw

New Member
Joined
Apr 1, 2011
Messages
10
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!:confused:
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Alphacsulb

Active Member
Joined
Mar 20, 2008
Messages
413
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

New Member
Joined
Apr 1, 2011
Messages
10
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

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,154
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

New Member
Joined
Apr 1, 2011
Messages
10

ADVERTISEMENT

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

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,154
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

New Member
Joined
Apr 1, 2011
Messages
10

ADVERTISEMENT

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. :eek:
 

al_b_cnu

Well-known Member
Joined
Jul 18, 2003
Messages
4,494
Hi,

will this macro do it (save before executing!)
Code:
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

New Member
Joined
Apr 1, 2011
Messages
10
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

Well-known Member
Joined
Jul 18, 2003
Messages
4,494
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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,040
Messages
5,526,412
Members
409,701
Latest member
nitmani

This Week's Hot Topics

Top