comparing data and producing the result on another sheet

[Klix]

hi folks..

I'm trying to work out how to get a macro or module? to compare two excel sheet and then display all the data onto another sheet.

for example

Sheet1 contains a datalist of 10000 records.
Sheet2 contains a datalist of 2000 records (of which are listed in sheet1)

I want to produce another sheet that contains sheet1 - sheet2.

so it should be something like

compare (sheet1, sheet2)
if record match sheet2! = sheet1! remove record from sheet1!

show results = sheet3

I jsut don't know how to do this , all ideas welcome.

Thanks...

[duane]

just for a start, are the records single cells, and if so in column A?

[Klix]

The records headings are as follows(these being the headings in each sheet):

REF SALUTATION TITLE FORENAME INITS SURNAME DMCEMAIL JOB COMPANY BUILDING STREET SUBURB TOWN COUNTY POSTCODE TEL CTPS FAX EMAIL INTERNET EMPSITE EMPNATN TURNOVER CODE INDUSTRY MAIN_ACTIV CO_TYPE

both sheet1 and sheet2 have indentical headings. I was thinking of running the comparison on DMCEMAIL. Does that help?

Cell A being REF, B being SALUTATION, and so on

[duane]

ok - so you want to check on the entries in column G I guess and it looks liek 27 columns of data - I'll allow for 30, also allow for 12000 records on sheet1 and 3000 on sheet2
Note sheet names are sheet1, sheet2, and sheet3
I will assume the header row is row 1

I have not tested this

Sub Compare()
'
' Sort sheet1 and sheet2 so Find command will work
'
Sheets("sheet1").Select
Range("a1:ai12000").Select
Selection.Sort _
Key1:=Range("g1"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Sheets("sheet2").Select
Range("a1:ai3000").Select
Selection.Sort _
Key1:=Range("g1"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
'
' Define ranges
'
ActiveWorkbook.Names.Add Name:="record1", RefersToR1C1:="=sheet1!R1C7"
ActiveWorkbook.Names.Add Name:="record2", RefersToR1C1:="=sheet2!R1C7"
Range("record1").end(xldown).Name = "sheet1records"
Range("record2").end(xldown).Name = "sheet2records"
numrecord1 = Range("record1").End(xlDown).Row - Range("record1").Row
numrecord2 = Range("record2").End(xlDown).Row - Range("record2").Row
'
' Copy header row to sheet3
Sheets("sheet2").Select
sheets("sheet2").cells(1,1).entirerow.select
Selection.Copy Sheets("sheet3").cells(1 , 1)
'
' Search on sheet1 for records on sheet2 by column G and place on sheet2
' in row 2 and down
'
'i is row number for nect record to be placed on sheet3
i = 1
'j is row number of record on sheet2
For j = 2 to numrecord2+1
Sheets("sheet2").Select
thisrecord = Sheets("sheet2").Cells(j, 7).Text
Set rng = Sheets("sheet1").Range("sheet1records").Find(thisrecord)
If rng Is Nothing Then GoTo sheet3add
GoTo nextj
sheet3add:
i = i + 1
k = rng.row
sheets("sheet2").cells(k,1).entirerow.select
Selection.Copy Sheets("sheet3").cells(i , 1)
nextj:
Next j
End Sub

[Klix]

Thanks for that Duane

I've run it through

I get this error = Run-time 91, object variable or with block variable not set

and the debugger highlights this code:

k = rng.Row

any ideas?

The output to the sheet3 goes as far as entering the headings.

thanks again for all your help on this.

[duane]

sorry - I am just so stupid.....of course k is not defined because there is no rng - we need to copy unfound record (rng.row assumes record was found!)

Sub Compare()
'
' Sort sheet1 and sheet2 so Find command will work
'
Sheets("sheet1").Select
Range("a1:ai12000").Select
Selection.Sort _
Key1:=Range("g1"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Sheets("sheet2").Select
Range("a1:ai3000").Select
Selection.Sort _
Key1:=Range("g1"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
'
' Define ranges
'
ActiveWorkbook.Names.Add Name:="record1", RefersToR1C1:="=sheet1!R1C7"
ActiveWorkbook.Names.Add Name:="record2", RefersToR1C1:="=sheet2!R1C7"
Range("record1").end(xldown).Name = "sheet1records"
Range("record2").end(xldown).Name = "sheet2records"
numrecord1 = Range("record1").End(xlDown).Row - Range("record1").Row
numrecord2 = Range("record2").End(xlDown).Row - Range("record2").Row
'
' Copy header row to sheet3
Sheets("sheet2").Select
sheets("sheet2").cells(1,1).entirerow.select
Selection.Copy Sheets("sheet3").cells(1 , 1)
'
' Search on sheet1 for records on sheet2 by column G and place on sheet2
' in row 2 and down
'
'i is row number for nect record to be placed on sheet3
i = 1
'j is row number of record on sheet2
For j = 2 to numrecord2+1
Sheets("sheet2").Select
thisrecord = Sheets("sheet2").Cells(j, 7).Text
Set rng = Sheets("sheet1").Range("sheet1records").Find(thisrecord)
If rng Is Nothing Then GoTo sheet3add
GoTo nextj
sheet3add:
i = i + 1
'copies unfound record (row j) to sheet3
sheets("sheet2").cells(j,1).entirerow.select
Selection.Copy Sheets("sheet3").cells(i , 1)
nextj:
Next j
End Sub

[Klix]

well if you are stupid then I'm a walking missing link

code seem to run through now... it stops at output record 28 now. not sure why.. it should produce about 8000 records.

I'll play about with the sheet numbering.. I think I may have explained it backward..

[duane]

Yes - I think I must hace interpreted your post wrong - replace sectionof code with this - just flip sheet1 and sheet2 references

'j is row number of record on sheet1
For j = 2 to numrecord1+1
Sheets("sheet1").Select
thisrecord = Sheets("sheet1").Cells(j, 7).Text
Set rng = Sheets("sheet2").Range("sheet2records").Find(thisr ecord)
If rng Is Nothing Then GoTo sheet3add

[Klix]

It seemed to do a lot more work this time, as expected.

however at the end I got the following:

Run-time error '1004'
select method of range class failed

and the debug code was:

Sheets("sheet2").Cells(j, 1).EntireRow.Select

I'm lost again

once again the output was just the headers

[duane]

I should have changed this too since we are now copying record from sheet1!

sheet3add:
i = i + 1
k = rng.row
sheets("sheet1").cells(k,1).entirerow.select
Selection.Copy Sheets("sheet3").cells(i , 1)
nextj:
Next j