Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare data between 2 worksheets
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 -- sharon2006 ------------------------------------------------------------------------ sharon2006's Profile: http://www.excelforum.com/member.php...o&userid=30173 View this thread: http://www.excelforum.com/showthread...hreadid=499259 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare data between 2 worksheets
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" wrote: 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 -- sharon2006 ------------------------------------------------------------------------ sharon2006's Profile: http://www.excelforum.com/member.php...o&userid=30173 View this thread: http://www.excelforum.com/showthread...hreadid=499259 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare data between 2 worksheets
Hi Toppers, Thanks for your helps. Your code really very helpful to me. Thanks & Regards, Sharon -- sharon2006 ------------------------------------------------------------------------ sharon2006's Profile: http://www.excelforum.com/member.php...o&userid=30173 View this thread: http://www.excelforum.com/showthread...hreadid=499259 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Compare data in two worksheets | Excel Worksheet Functions | |||
Compare data in two worksheets | Excel Worksheet Functions | |||
Compare Data from two Worksheets | Excel Discussion (Misc queries) | |||
Compare data in 2 worksheets | Excel Discussion (Misc queries) | |||
compare worksheets data | Excel Discussion (Misc queries) |