Compare data between 2 worksheets

[sharon2006]

Hi all,

I have 2 worksheet

1. Sheet2 - Source sheet
2. Sheet1

I need a macro that compare the value between this worksheets.
The condition will like: -

1. If sheet2 value Not exist in Sheet1, then add the new value to
column C and display the status in Column F, sheet1 as "New"

2. If sheet1 value Not exist in Sheet2, remain the value and display
the status in Column F as "Canceled" in sheet1.

3. I need have a input range that allow user to select the start row of
data that need to be compare.

4. Compare button will place in Sheet1.

EXAMPLE LAYOUT OF THE WORKSHEETS:

Sheet1:

_Row__----__Column_(C)__------Column_(F)_
21 ---------- AAAA --------
22 -----------BBBBB -------- Cancel
23 ---------- CCCCC -----------New


Sheet2:

_Row__----__Column_(C)___
2 ---------- AAAA
3 -----------CCCCC

*Row -- Indicate the start row to place the data


Anyone have an idea about it...???
Thanks!

Regards,
Sharon

 

[Guest]

Sharon,

Try this. I wasn't sure whether compare was sheet1 or sheet2
so you may have swap the "Set rng=....." statements.

HTH

Sub CompareSheets()

Dim ws1 As Worksheet, ws2 As Worksheet
Dim rng1 As Range, rng2 As Range
Dim startcell As Range
Dim lastrow As Long, Startrow As Long
Dim res As Variant

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

Do
Set startcell = Application.InputBox("Enter start cell in col C", Type:=8)
Loop Until startcell.Count = 1

Startrow = startcell.Row

With ws2
lastrow = .Cells(Rows.Count, 3).End(xlUp).Row
Set rng2 = .Range("c2:c" & lastrow)
End With

With ws1
lastrow = .Cells(Rows.Count, 3).End(xlUp).Row
Set rng1 = .Range("c" & Startrow & ":c" & lastrow)
End With
' Compare Sheet1 with Sheet2
For Each cell In rng1
res = Application.Match(cell, rng2, 0)
If IsError(res) Then
ws1.Cells(cell.Row, "F") = "Cancelled"
End If
Next cell

Nextrow = lastrow + 1
'Compare sheet2 with sheet1
For Each cell In rng2
res = Application.Match(cell, rng1, 0)
If IsError(res) Then ' add to end of Sheet1 list
ws1.Cells(Nextrow, "C") = cell.Value
ws1.Cells(Nextrow, "F") = "New"
nextrow=nextrow+1
End If
Next cell

 

[sharon2006]

Hi Toppers,

Thanks for your helps. Your code really very helpful to me.

Thanks & Regards,

Sharon