Excel macro to comparison of rows
I am trying to do below example, not able to do it.
Excel Data in Sheet1:
id Sysid option status
XYZ XT Open status_1
US XT Close Status_1
US XT Open Stauts_2
NJ XT Open Status_2
IND VX Close Status_1
BAN VX Open Status_1
CHN XY Open Status_1
YST xy Close Status_1
In above data Status_1 and Sysid defines unique record,Based on this condition i have to find out which records have open and close difference for that unique record combination. If options are different I have to copy that data to different sheet using excel macro.This should work only for id's US and CHN.Suppose if id has US ,it shou开发者_Go百科ld compare with matching record based on sysid and Status. Any help appreciated.
Output Like below:
XYZ XT Open status_1
US XT Close Status_1
CHN XY Open Status_1
YST xy Close Status_1
Edited to include test for "US" or "CHN"
Sub Tester()
Const COL_ID As Integer = 1
Const COL_SYSID As Integer = 2
Const COL_STATUS As Integer = 4
Const COL_OPTION As Integer = 3
Const VAL_DIFF As String = "XXdifferentXX"
Dim d As Object, sKey As String, id As String
Dim rw As Range, opt As String, rngData As Range
Dim rngCopy As Range, goodId As Boolean
Dim FirstPass As Boolean, arr
With Sheet1.Range("A1")
Set rngData = .CurrentRegion.Offset(1).Resize( _
.CurrentRegion.Rows.Count - 1)
End With
Set rngCopy = Sheet1.Range("F2")
Set d = CreateObject("scripting.dictionary")
FirstPass = True
redo:
For Each rw In rngData.Rows
sKey = rw.Cells(COL_SYSID).Value & "<>" & _
rw.Cells(COL_STATUS).Value
If FirstPass Then
'Figure out which combinations have different option values
' and at least one record with id=US or CHN
id = rw.Cells(COL_ID).Value
goodId = (id = "US" Or id = "CHN")
opt = rw.Cells(COL_OPTION).Value
If d.exists(sKey) Then
arr = d(sKey) 'can't modify the array in situ...
If arr(0) <> opt Then arr(0) = VAL_DIFF
If goodId Then arr(1) = True
d(sKey) = arr 'return [modified] array
Else
d.Add sKey, Array(opt, goodId)
End If
Else
'Second pass - copy only rows with varying options
' and id=US or CHN
If d(sKey)(0) = VAL_DIFF And d(sKey)(1) = True Then
rw.Copy rngCopy
Set rngCopy = rngCopy.Offset(1, 0)
End If
End If
Next rw
If FirstPass Then
FirstPass = False
GoTo redo
End If
End Sub
Use the macro recorder and do a unique advanced filter on the data then you can do your comparisons.
http://office.microsoft.com/en-us/excel-help/filter-for-unique-records-HP003073425.aspx?CTT=3
精彩评论