Probably missing something obvious here.
Code needs to filter Col C to show only entries with "2" value (which means there are two records with the same combination in Col B).
What I cannot see is how to then "find" each pair in Col B? Because if I don't know what row number will be the first filtered record, I won't be able to set the "Find" function to look for whatever is in Col B of that row, then cycle to the next visible row.
Having found the pair, the Code will then compare Col A. If both values match, the records go to Col H - J, and if they are different the records go to Col M, as shown on the attached.
Option Explicit
Dim a As Integer
Dim rng As Range
Sub SETPAIRS()
With ActiveSheet
a = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
'Select rows where Column C is 2:
ActiveSheet.Range("A1:C" & a).AutoFilter Field:=3, Criteria1:="2"
'Find "pairs" in Col B:
'Loop through Column B, looking for matching values
Set rng = Range("B2", Range("B").End(xlDown)).Cells.SpecialCells(xlCellTypeVisible)
Const WHAT_TO_FIND As String = "??" 'How do I know the first filtered row number, or what it will say in Col B?
'First thoughts on how to then check whether Col A is identical where a pair is found (Code incomplete)
Set m = ws.Range("B:B").Find(What:=WHAT_TO_FIND) 'This will be whatever is in Col B of the first filtered row
If Not m Is Nothing Then
For Each cell In rng
Set m = .Find(Range("A" & rw))
If m Is Nothing Then
Next
End Sub
All suggestions, pointers and solutions accepted gratefully, as ever
Ochimus
Bookmarks