Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare two columns in a different worksheet
Good day everyone,
With the following code, you compare data between two columns in a different worksheet, and the results are transferred to a third one. Sub test() Dim T(), A As Long Dim Rg As Range, Rg1 As Range With Worksheets("month") Set Rg = .Range("B1:B" & .Range("B65536").End(xlUp).Row) End With With Worksheets("Data received") Set Rg1 = .Range("A1:A" & .Range("A65536").End(xlUp).Row) End With For Each c In Rg If Application.CountIf(Rg1, c) = 0 Then ReDim Preserve T(A) T(A) = c.Value A = A + 1 End If Next Worksheets("Result").Range("A1").Resize(UBound(T) + 1) = _ Application.Transpose(T) End Sub However, I would like to have the entire row to show on the destination sheet rather than the cell value only. Anyone would know where I should modify the code ? Thanks for your time Denys |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare two columns in a different worksheet
Hi Denys
This will do the job for you. Originally you were placing the outcomeof Rg in Col A of the "Result" sheet. If you copy the entire Column of the Cells which do not match, the outcome will appear in Column b of the "Result" sheet. Not sure if this is what you want? Take Care Marcus Sub test() Dim T(), A As Long Dim Rg As Range, Rg1 As Range Dim lw as Long lw = Sheets("Result").Range("B" & Rows.Count).End(xlUp).Row + 1 With Worksheets("month") Set Rg = .Range("B1:B" & .Range("B65536").End(xlUp).Row) End With With Worksheets("Data received") Set Rg1 = .Range("A1:A" & .Range("A65536").End(xlUp).Row) End With For Each c In Rg If Application.CountIf(Rg1, c) = 0 Then ReDim Preserve T(A) 'T(A) = c.Value A = A + 1 c.EntireRow.Copy Sheets("Result").Range("A" & lw) lw = lw + 1 End If Next 'Worksheets("Result").Range("A1").Resize(UBound(T) + 1) = _ Application.Transpose(T) End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare two columns in a different worksheet
Hi Marcus,
Thank you very much....it's perfect !!! Wow !! Have a nice day Denys |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare two columns in a different worksheet
You already have an answer, but for more general Excel comparison you might
be interested in my Excel utility "Workbook Compare". It was conceived as a platform for regression testing, but can be used for virtually any Excel comparison needs whatsoever. -- Regards, Tim Zych http://www.higherdata.com Workbook Compare - Excel data comparison utility "Denys" wrote in message ... Good day everyone, With the following code, you compare data between two columns in a different worksheet, and the results are transferred to a third one. Sub test() Dim T(), A As Long Dim Rg As Range, Rg1 As Range With Worksheets("month") Set Rg = .Range("B1:B" & .Range("B65536").End(xlUp).Row) End With With Worksheets("Data received") Set Rg1 = .Range("A1:A" & .Range("A65536").End(xlUp).Row) End With For Each c In Rg If Application.CountIf(Rg1, c) = 0 Then ReDim Preserve T(A) T(A) = c.Value A = A + 1 End If Next Worksheets("Result").Range("A1").Resize(UBound(T) + 1) = _ Application.Transpose(T) End Sub However, I would like to have the entire row to show on the destination sheet rather than the cell value only. Anyone would know where I should modify the code ? Thanks for your time Denys |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
compare two columns in same worksheet for similarities | Excel Worksheet Functions | |||
compare excel worksheet and output difference to new worksheet | Excel Discussion (Misc queries) | |||
Compare 2 worksheets all 6 columns in each worksheet | Excel Worksheet Functions | |||
compare two columns within a worksheet, then highlight duplicates | Excel Worksheet Functions | |||
How do I Compare worksheet columns | Excel Worksheet Functions |