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:
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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.
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
Very close. Just the other way around. I need the row in Sheet 2 to be removed if it contains data from Sheet 1.
 
Upvote 0
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?
 
Upvote 0
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:
 
Upvote 0
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
 
Upvote 0
Help me out here. I copy and pasted what you had posted into a new macro. But when I run it, nothing happens.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,315
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top