Home/ Questions/Q 8385
Next
Answered
computing
  • 0
Asked: 2021-11-28T10:40:03+00:00 In:

Solved Searching For Matching Pairs Of Data In Excel

  • 0

I have thousands of matching pairs of data under Col A and Col B. I am trying achieve the following: for every pair of data, there will be an “exact opposite pair” somewhere below.
See example below:

A B
100 162*
300 159
268 465
162 100*

Note: In this case, the AB pair, marked with an asterisk (*), has an “exact opposite pair” below, also marked with an asterisk (*).

How can I search the entire list and ensure that for every AB pair, there is an “exact opposite pair” within the list? And if there isn’t, how can I add the “exact opposite pair” to the list in a quick and efficient way?

Much needed help.
Thank you.

Share

    1 Answer

    1. Best Answer
      Your second data set is very different from your first.

      In your first data set each value in Column A was unique, so “finding” it in Column B was either TRUE or FALSE.

      In your second data set, with it’s repeating values in Column A, the same value might appear multiple times in Column B, so a simple .Find no longer works since .Find will find the first occurrence and then stop searching.

      There was no way for me to know that…I can only work with the example data that is provided.

      In the following code I use a helper column (Column C) and place a “combined pair” next to each pair from Columns A and B.

      e.g.

            A      B       C
1    373    374    373374
2    374    218    374218
3    374    362    374362
4    374    373    374373

      Once the combined pair is created, the code will search Column C for a string created from the values in Column A and B, but reversed.

      e.g.

      B1 & A1 = 374373

      If the “reversed combined pair” in found in Column C, then that un-combined pair must exist in Column A and B.

      e.g.

            A      B       C
1    373    374   <----- 
2    374    218    
3    374    362    
4    374    373    374373 <----- B1 & A1 = 374373

      If the reverse pair is not found in Column C, then the reverse values are placed at the bottom of Columns A & B.

      Sub MatchPairs()
'Determine last Row with data Column A
  lastRw = Range("A" & Rows.Count).End(xlUp).Row
'Create reverse pair in Column C
   For rw = 1 To lastRw
    Range("C" & rw) = Range("A" & rw) & Range("B" & rw)
   Next
'Loop through Column C, looking for a matching reverse pair
   For rw = 1 To lastRw
    With Range("C1:C" & lastRw)
     Set m = .Find(Range("B" & rw) & Range("A" & rw))
      If m Is Nothing Then
'If matching reverse pair isn't found, determine next empty Row
       nxtRw = Range("A" & Rows.Count).End(xlUp).Row + 1
'Copy reverse pair into next Row
         Range("A" & nxtRw) = Range("B" & rw)
         Range("B" & nxtRw) = Range("A" & rw)
      End If
    End With
   Next
'Clear Column C
   Columns("C").ClearContents
End Sub

      Click Here Before Posting Data or VBA Code —> How To Post Data or Code.

      • 0