Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
comparing 2 columns of numbers
what would be the best way to compare 2 columns of numbers?
the master list is downloaded every day. numbers may be added or deleted. i would need to find just the ones that were added or deleted in the master list. i wrote a routine that sorts and compares side by side numbers and if it finds one, it changes the offset to the next row and continues on to the next row. this seems to work fine for numbers that were added. i just wondered if there was a better way to filter them out. For Each cell In ws2.Range("A1:A" & lastrow3) cell.Value = cell.Value * 1 'in case any are stored as text cell.Offset(0, 1).Value = cell.Offset(0, 1).Value * 1 'in case any are stored as text If cell.Value < cell.Offset(0 - Cntr, 1).Value Then Debug.Print cell.Address Cntr = Cntr + 1 .Range("C" & Cntr) = cell.Value End If Next -- Gary |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
comparing 2 columns of numbers
from a previous post by KeepItCool:
http://groups.google.com/group/micro...e=source&hl=en Try following. It's a general routine that's very fast and convenient (the input arrays must contain unique ID's.) Be aware that the returned arrays are 0 based. ubound = -1 when empty. Sub DemoMatchCols() Dim vMatches vMatches = ArrayMatcher(Range("a:a"), Range("b:b")) If UBound(vMatches(0)) -1 Then Range("d1").Resize(1 + UBound(vMatches(0))) = _ Application.Transpose(vMatches(0)) End If If UBound(vMatches(1)) -1 Then Range("e1").Resize(1 + UBound(vMatches(1))) = _ Application.Transpose(vMatches(1)) End If If UBound(vMatches(2)) -1 Then Range("f1").Resize(1 + UBound(vMatches(2))) = _ Application.Transpose(vMatches(2)) End If End Sub Function ArrayMatcher(ByVal List1 As Variant, _ ByVal List2 As Variant, _ Optional bIgnoreCase As Boolean = True) 'compares the values from 2 arrays 'and returns an array of 3 arrays of 'unique items(items left, items both, items right) ' 'author keepITcool excel.programming aug 9th,2005 'requires a reference to Microsoft Scripting Runtime Dim dic(3) As Scripting.Dictionary Dim itm, key, res Dim i As Integer For i = 0 To 3 Set dic(i) = New Dictionary dic(i).CompareMode = IIf(bIgnoreCase, TextCompare, BinaryCompare) Next If Not IsArray(List1) Then Exit Function If Not IsArray(List2) Then Exit Function If Not IsArray(List1) Then Exit Function If Not IsArray(List2) Then Exit Function If TypeName(List1) = "Range" Then List1 = _ Intersect(List2.Parent.UsedRange, List1).Value If TypeName(List2) = "Range" Then List2 = _ Intersect(List2.Parent.UsedRange, List2).Value On Error Resume Next 'loop List1 and add all unique items to dic(3) 'dic(3) will be discarded later For Each itm In List1 dic(3).Add CStr(itm), itm Next 'loop List2: 'If found in dic(3) then add to dic(1) else add to dic(2) For Each itm In List2 If dic(3).Exists(CStr(itm)) Then dic(1).Add CStr(itm), itm Else dic(2).Add CStr(itm), itm End If Next 'loop dic(3): 'if not found add to dic(0) For Each key In dic(3) If Not dic(2).Exists(key) Then dic(0).Add key, dic(3)(key) End If Next Set dic(3) = Nothing dic(2).Remove (vbNullString) dic(1).Remove (vbNullString) dic(0).Remove (vbNullString) ReDim res(2) res(0) = dic(0).Items res(1) = dic(1).Items res(2) = dic(2).Items ArrayMatcher = res End Function -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam -- Regards, Tom Ogilvy "Gary Keramidas" wrote: what would be the best way to compare 2 columns of numbers? the master list is downloaded every day. numbers may be added or deleted. i would need to find just the ones that were added or deleted in the master list. i wrote a routine that sorts and compares side by side numbers and if it finds one, it changes the offset to the next row and continues on to the next row. this seems to work fine for numbers that were added. i just wondered if there was a better way to filter them out. For Each cell In ws2.Range("A1:A" & lastrow3) cell.Value = cell.Value * 1 'in case any are stored as text cell.Offset(0, 1).Value = cell.Offset(0, 1).Value * 1 'in case any are stored as text If cell.Value < cell.Offset(0 - Cntr, 1).Value Then Debug.Print cell.Address Cntr = Cntr + 1 .Range("C" & Cntr) = cell.Value End If Next -- Gary |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
comparing 2 columns of numbers
thanks tom. i'll give it a try
-- Gary "Tom Ogilvy" wrote in message ... from a previous post by KeepItCool: http://groups.google.com/group/micro...e=source&hl=en Try following. It's a general routine that's very fast and convenient (the input arrays must contain unique ID's.) Be aware that the returned arrays are 0 based. ubound = -1 when empty. Sub DemoMatchCols() Dim vMatches vMatches = ArrayMatcher(Range("a:a"), Range("b:b")) If UBound(vMatches(0)) -1 Then Range("d1").Resize(1 + UBound(vMatches(0))) = _ Application.Transpose(vMatches(0)) End If If UBound(vMatches(1)) -1 Then Range("e1").Resize(1 + UBound(vMatches(1))) = _ Application.Transpose(vMatches(1)) End If If UBound(vMatches(2)) -1 Then Range("f1").Resize(1 + UBound(vMatches(2))) = _ Application.Transpose(vMatches(2)) End If End Sub Function ArrayMatcher(ByVal List1 As Variant, _ ByVal List2 As Variant, _ Optional bIgnoreCase As Boolean = True) 'compares the values from 2 arrays 'and returns an array of 3 arrays of 'unique items(items left, items both, items right) ' 'author keepITcool excel.programming aug 9th,2005 'requires a reference to Microsoft Scripting Runtime Dim dic(3) As Scripting.Dictionary Dim itm, key, res Dim i As Integer For i = 0 To 3 Set dic(i) = New Dictionary dic(i).CompareMode = IIf(bIgnoreCase, TextCompare, BinaryCompare) Next If Not IsArray(List1) Then Exit Function If Not IsArray(List2) Then Exit Function If Not IsArray(List1) Then Exit Function If Not IsArray(List2) Then Exit Function If TypeName(List1) = "Range" Then List1 = _ Intersect(List2.Parent.UsedRange, List1).Value If TypeName(List2) = "Range" Then List2 = _ Intersect(List2.Parent.UsedRange, List2).Value On Error Resume Next 'loop List1 and add all unique items to dic(3) 'dic(3) will be discarded later For Each itm In List1 dic(3).Add CStr(itm), itm Next 'loop List2: 'If found in dic(3) then add to dic(1) else add to dic(2) For Each itm In List2 If dic(3).Exists(CStr(itm)) Then dic(1).Add CStr(itm), itm Else dic(2).Add CStr(itm), itm End If Next 'loop dic(3): 'if not found add to dic(0) For Each key In dic(3) If Not dic(2).Exists(key) Then dic(0).Add key, dic(3)(key) End If Next Set dic(3) = Nothing dic(2).Remove (vbNullString) dic(1).Remove (vbNullString) dic(0).Remove (vbNullString) ReDim res(2) res(0) = dic(0).Items res(1) = dic(1).Items res(2) = dic(2).Items ArrayMatcher = res End Function -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam -- Regards, Tom Ogilvy "Gary Keramidas" wrote: what would be the best way to compare 2 columns of numbers? the master list is downloaded every day. numbers may be added or deleted. i would need to find just the ones that were added or deleted in the master list. i wrote a routine that sorts and compares side by side numbers and if it finds one, it changes the offset to the next row and continues on to the next row. this seems to work fine for numbers that were added. i just wondered if there was a better way to filter them out. For Each cell In ws2.Range("A1:A" & lastrow3) cell.Value = cell.Value * 1 'in case any are stored as text cell.Offset(0, 1).Value = cell.Offset(0, 1).Value * 1 'in case any are stored as text If cell.Value < cell.Offset(0 - Cntr, 1).Value Then Debug.Print cell.Address Cntr = Cntr + 1 .Range("C" & Cntr) = cell.Value End If Next -- Gary |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need help comparing 2 columns of number to find unique numbers | Excel Worksheet Functions | |||
Comparing 3 numbers | Excel Discussion (Misc queries) | |||
Comparing two columns of numbers | Excel Worksheet Functions | |||
comparing numbers in a sheet | Excel Discussion (Misc queries) | |||
Comparing two columns of information with 2 new columns of informa | Excel Discussion (Misc queries) |