Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare data by Column?
I am not sure if this is possible.
I have a single workbook that has two columns in two worksheets. The data on sheet1 Column1 is a list of specs we use and Column2 is the Revision level. Sheet2 has the same layout/format except the list may be longer or shorter than Sheet1. Can I compare data on Sheet1 (Column1 then Column2) to Sheet2 (Column1 then Column2)? How can I flag a Zero Match condition and or Revision Level difference? Sheet1 Column1 Column2 GPS 1000-1 A GPS 1000-2 B GPS 1000-3 A GPS 1000-4 A Sheet2 Column1 Column2 GPS 1000-1 A GPS 1000-3 B GPS 1000-4 A GPS 1000-5 A -- Regards Rick XP Pro Office 2007 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare data by Column?
The code below performs a two way comparison. First it compares sheet 1 with
2, and the sheet 2 with 1. It highlight in yellow rows with different version letters on each sheet. It highlights in Red when one row is not found in the other sheet. Code is fully automatic. It check for the last row on each sheet and stop running when it reaches these rows. Sub checkrev() With Sheets("Sheet1") Sh1LastRow = .Cells(Rows.Count, "A").End(xlUp).Row Set Sh1Range = .Range("A1:A" & Sh1LastRow) End With With Sheets("Sheet2") Sh2LastRow = .Cells(Rows.Count, "A").End(xlUp).Row Set Sh2Range = .Range("A1:A" & Sh2LastRow) End With 'compare sheet 1 with sheet 2 For Each Sh1cell In Sh1Range Set c = Sh2Range.Find( _ what:=Sh1cell, LookIn:=xlValues) If c Is Nothing Then Sh1cell.Interior.ColorIndex = 3 Sh1cell.Offset(0, 1).Interior.ColorIndex = 3 Else If Sh1cell.Offset(0, 1) < c.Offset(0, 1) Then Sh1cell.Interior.ColorIndex = 6 Sh1cell.Offset(0, 1).Interior.ColorIndex = 6 End If End If Next Sh1cell 'compare sheet 2 with sheet 1 For Each Sh2cell In Sh2Range Set c = Sh1Range.Find( _ what:=Sh2cell, LookIn:=xlValues) If c Is Nothing Then Sh2cell.Interior.ColorIndex = 3 Sh2cell.Offset(0, 1).Interior.ColorIndex = 3 Else If Sh2cell.Offset(0, 1) < c.Offset(0, 1) Then Sh2cell.Interior.ColorIndex = 6 Sh2cell.Offset(0, 1).Interior.ColorIndex = 6 End If End If Next Sh2cell End Sub "Rick S." wrote: I am not sure if this is possible. I have a single workbook that has two columns in two worksheets. The data on sheet1 Column1 is a list of specs we use and Column2 is the Revision level. Sheet2 has the same layout/format except the list may be longer or shorter than Sheet1. Can I compare data on Sheet1 (Column1 then Column2) to Sheet2 (Column1 then Column2)? How can I flag a Zero Match condition and or Revision Level difference? Sheet1 Column1 Column2 GPS 1000-1 A GPS 1000-2 B GPS 1000-3 A GPS 1000-4 A Sheet2 Column1 Column2 GPS 1000-1 A GPS 1000-3 B GPS 1000-4 A GPS 1000-5 A -- Regards Rick XP Pro Office 2007 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare data by Column?
I would have worked on this for a week (Maybe more)!
I was working with MS's Compare.xla but all it did was comare row for row. Today I was going to disect the code and try to make it work for my situation. You saved me some hair! LOL Thanks, this works perfect! -- Regards Rick XP Pro Office 2007 "Joel" wrote: The code below performs a two way comparison. First it compares sheet 1 with 2, and the sheet 2 with 1. It highlight in yellow rows with different version letters on each sheet. It highlights in Red when one row is not found in the other sheet. Code is fully automatic. It check for the last row on each sheet and stop running when it reaches these rows. Sub checkrev() With Sheets("Sheet1") Sh1LastRow = .Cells(Rows.Count, "A").End(xlUp).Row Set Sh1Range = .Range("A1:A" & Sh1LastRow) End With With Sheets("Sheet2") Sh2LastRow = .Cells(Rows.Count, "A").End(xlUp).Row Set Sh2Range = .Range("A1:A" & Sh2LastRow) End With 'compare sheet 1 with sheet 2 For Each Sh1cell In Sh1Range Set c = Sh2Range.Find( _ what:=Sh1cell, LookIn:=xlValues) If c Is Nothing Then Sh1cell.Interior.ColorIndex = 3 Sh1cell.Offset(0, 1).Interior.ColorIndex = 3 Else If Sh1cell.Offset(0, 1) < c.Offset(0, 1) Then Sh1cell.Interior.ColorIndex = 6 Sh1cell.Offset(0, 1).Interior.ColorIndex = 6 End If End If Next Sh1cell 'compare sheet 2 with sheet 1 For Each Sh2cell In Sh2Range Set c = Sh1Range.Find( _ what:=Sh2cell, LookIn:=xlValues) If c Is Nothing Then Sh2cell.Interior.ColorIndex = 3 Sh2cell.Offset(0, 1).Interior.ColorIndex = 3 Else If Sh2cell.Offset(0, 1) < c.Offset(0, 1) Then Sh2cell.Interior.ColorIndex = 6 Sh2cell.Offset(0, 1).Interior.ColorIndex = 6 End If End If Next Sh2cell End Sub "Rick S." wrote: I am not sure if this is possible. I have a single workbook that has two columns in two worksheets. The data on sheet1 Column1 is a list of specs we use and Column2 is the Revision level. Sheet2 has the same layout/format except the list may be longer or shorter than Sheet1. Can I compare data on Sheet1 (Column1 then Column2) to Sheet2 (Column1 then Column2)? How can I flag a Zero Match condition and or Revision Level difference? Sheet1 Column1 Column2 GPS 1000-1 A GPS 1000-2 B GPS 1000-3 A GPS 1000-4 A Sheet2 Column1 Column2 GPS 1000-1 A GPS 1000-3 B GPS 1000-4 A GPS 1000-5 A -- Regards Rick XP Pro Office 2007 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare data by Column?
Thanks to Joel
I am now using this: '=================== Sub CheckRev_v2() '==================== 'Originating author: Joel from "microsoft.public.excel.programming" 10.09.07 '==================== Application.ScreenUpdating = False With Sheets("Sheet1") Sh1LastRow = .Cells(Rows.Count, "A").End(xlUp).Row Set Sh1Range = .Range("A1:A" & Sh1LastRow) End With With Sheets("Sheet2") Sh2LastRow = .Cells(Rows.Count, "A").End(xlUp).Row Set Sh2Range = .Range("A1:A" & Sh2LastRow) End With 'compare sheet 1 with sheet 2 For Each Sh1cell In Sh1Range Set c = Sh2Range.Find( _ what:=Sh1cell, LookIn:=xlValues) If c Is Nothing Then Sh1cell.Offset(0, 2).Value = "No Match Found!" Sh1cell.Offset(0, 2).Font.Color = -16776961 Sh1cell.Offset(0, 2).Font.Bold = True 'Sh1cell.Offset(0, 3).Value = Sh1cell.Offset(0, 1).Value 'enters Rev level Else If Sh1cell.Offset(0, 1) < c.Offset(0, 1) Then Sh1cell.Offset(0, 2).Font.Italic = True Sh1cell.Offset(0, 2).ColumnWidth = 25 Sh1cell.Offset(0, 2).Value = "Revision Level Change!" End If End If Next Sh1cell 'compare sheet 2 with sheet 1 For Each Sh2cell In Sh2Range Set c = Sh1Range.Find( _ what:=Sh2cell, LookIn:=xlValues) If c Is Nothing Then Sh2cell.Offset(0, 2).Value = "No Match Found!" Sh2cell.Offset(0, 2).Font.Color = -16776961 Sh2cell.Offset(0, 2).Font.Bold = True 'Sh2cell.Offset(0, 3).Value = Sh2cell.Offset(0, 1).Value 'enters Rev level Else If Sh2cell.Offset(0, 1) < c.Offset(0, 1) Then Sh2cell.Offset(0, 2).Font.Italic = True Sh2cell.Offset(0, 2).ColumnWidth = 25 Sh2cell.Offset(0, 2).Value = "Revision Level Change!" End If End If Next Sh2cell Application.ScreenUpdating = True End Sub '===================== -- Regards Rick XP Pro Office 2007 "Joel" wrote: The code below performs a two way comparison. First it compares sheet 1 with 2, and the sheet 2 with 1. It highlight in yellow rows with different version letters on each sheet. It highlights in Red when one row is not found in the other sheet. Code is fully automatic. It check for the last row on each sheet and stop running when it reaches these rows. Sub checkrev() With Sheets("Sheet1") Sh1LastRow = .Cells(Rows.Count, "A").End(xlUp).Row Set Sh1Range = .Range("A1:A" & Sh1LastRow) End With With Sheets("Sheet2") Sh2LastRow = .Cells(Rows.Count, "A").End(xlUp).Row Set Sh2Range = .Range("A1:A" & Sh2LastRow) End With 'compare sheet 1 with sheet 2 For Each Sh1cell In Sh1Range Set c = Sh2Range.Find( _ what:=Sh1cell, LookIn:=xlValues) If c Is Nothing Then Sh1cell.Interior.ColorIndex = 3 Sh1cell.Offset(0, 1).Interior.ColorIndex = 3 Else If Sh1cell.Offset(0, 1) < c.Offset(0, 1) Then Sh1cell.Interior.ColorIndex = 6 Sh1cell.Offset(0, 1).Interior.ColorIndex = 6 End If End If Next Sh1cell 'compare sheet 2 with sheet 1 For Each Sh2cell In Sh2Range Set c = Sh1Range.Find( _ what:=Sh2cell, LookIn:=xlValues) If c Is Nothing Then Sh2cell.Interior.ColorIndex = 3 Sh2cell.Offset(0, 1).Interior.ColorIndex = 3 Else If Sh2cell.Offset(0, 1) < c.Offset(0, 1) Then Sh2cell.Interior.ColorIndex = 6 Sh2cell.Offset(0, 1).Interior.ColorIndex = 6 End If End If Next Sh2cell End Sub "Rick S." wrote: I am not sure if this is possible. I have a single workbook that has two columns in two worksheets. The data on sheet1 Column1 is a list of specs we use and Column2 is the Revision level. Sheet2 has the same layout/format except the list may be longer or shorter than Sheet1. Can I compare data on Sheet1 (Column1 then Column2) to Sheet2 (Column1 then Column2)? How can I flag a Zero Match condition and or Revision Level difference? Sheet1 Column1 Column2 GPS 1000-1 A GPS 1000-2 B GPS 1000-3 A GPS 1000-4 A Sheet2 Column1 Column2 GPS 1000-1 A GPS 1000-3 B GPS 1000-4 A GPS 1000-5 A -- Regards Rick XP Pro Office 2007 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare data by Column?
In the following code, I am not understanding how to pass the variable value
to my other sheet (Sh2). =============== 'compare sheet 1 with sheet 2 For Each sh1cell In sh1range Set c = sh2range.Find( _ What:=sh1cell, LookIn:=xlValues, LookAt:=xlWhole)' Added "xlWhole" If c Is Nothing Then sh1cell.Offset(0, 2).Value = "No Match Found!" sh1cell.Offset(0, 2).Font.Color = -16776961 sh1cell.Offset(0, 2).Font.Bold = True Else If sh1cell.Offset(0, 1) < c.Offset(0, 1) Then sh1cell.Offset(0, 2).Font.Italic = True sh1cell.Offset(0, 2).ColumnWidth = 25 sh1cell.Offset(0, 2).Value = "Revision Level Change!" sSh1RevIs = sh1cell.Offset(0, 1) ' Capture Revision level of Sh1 if False MsgBox sSh1RevIs 'for testing...Show Rev from Sh1 End If End If Next sh1cell ==================== The variable "sSh1RevIs" captures the value I want (revision level of Sh1) but I cannot figure out how to place this value on the same row (column 3) on Sh2 (compared row of Sh1 and Sh2). -- Regards Rick XP Pro Office 2007 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Column Chart to compare data | Charts and Charting in Excel | |||
compare data in column A with column B to find duplicates | Excel Discussion (Misc queries) | |||
compare date to column data | Excel Worksheet Functions | |||
How do I compare data in two different spreadsheets i.e. (column . | Excel Discussion (Misc queries) | |||
compare data from one column with another and compare result to yet another | Excel Programming |