Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel List Comapre
I need to compare 2 list in Excel, and be able to generate a 3rd
worksheet which will have both the list side by side but will show blank cells in either of the two list where the cell don't match, in other words, the macro should move down the cells in either of the list which don't match. To make it clear, the following link has the excat macro that I want but its protected. Any help would be greatly appreciated. http://www.sharewareplaza.com/Excel-...oad_16416.html |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel List Comapre
I would add an extra colum to each table with the sheet number is the column
then use a pivot table with multiple consolidated ranges - create single page field to generate a matrix of which items are on each sheet Example Sheet1: col A Col B Item Sheet a 1 b 1 c 1 d 1 Example Sheet2: Item Sheet a 2 b 2 d 2 z 2 Pivot Result: Count of Value Page1 Row Item1 Item2 Grand Total a 1 1 2 b 1 1 2 c 1 1 d 1 1 2 z 1 1 Grand Total 4 4 8 good Luck -- Stewart Rogers DataSort Software, L.C. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel List Comapre
This will do the comparison. If you want more flexibility or a
UserForm, I leave it to you. Sub CompareLists() Dim iRow1 As Integer Dim iRow2 As Integer Dim iRow3 As Integer Dim iTest1 As Long Dim iTest2 As Long iRow1 = 2 iRow2 = 2 iRow3 = 2 Do If Sheet1.Cells(iRow1, 1) < Sheet1.Cells(iRow2, 6) Or _ Sheet1.Cells(iRow1, 2) < Sheet1.Cells(iRow2, 7) Then Sheet2.Cells(iRow3, 5) = "No Match" 'If one list is longer than the other, will compare a number & 'a blank cell. The following is a work-around for a blank cell. If Sheet1.Cells(iRow1, 1) = "" Then iTest1 = 100000000 Else iTest1 = Sheet1.Cells(iRow1, 1) End If If Sheet1.Cells(iRow2, 6) = "" Then iTest2 = 100000000 Else iTest2 = Sheet1.Cells(iRow2, 6) End If If iTest1 < iTest2 Then Sheet1.Range("A" & iRow1 & ":D" & iRow1).Copy Sheet2.Range("A" & iRow3) iRow1 = iRow1 + 1 Else Sheet1.Range("F" & iRow2 & ":I" & iRow2).Copy Sheet2.Range("F" & iRow3) iRow2 = iRow2 + 1 End If Else Sheet1.Range("A" & iRow1 & ":D" & iRow1).Copy Sheet2.Range("A" & iRow3) Sheet1.Range("F" & iRow2 & ":I" & iRow2).Copy Sheet2.Range("F" & iRow3) iRow1 = iRow1 + 1 iRow2 = iRow2 + 1 End If iRow3 = iRow3 + 1 Loop Until Sheet1.Range("A" & iRow1) = "" And Sheet1.Range("F" & iRow2) = "" End Sub Hth, Merjet |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro for comapre and copy | Excel Discussion (Misc queries) | |||
comapre two list of data & arranging in a same manner | New Users to Excel | |||
comapre 2 col. | Excel Programming | |||
comapre two lists | Excel Discussion (Misc queries) | |||
How do i comapre 2 columns | Excel Programming |