Solved Searching for matching pairs of data in Excel

Microsoft Excel 2010 - complete product...
April 10, 2013 at 13:01:26
Specs: Windows 7
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.


See More: Searching for matching pairs of data in Excel

Report •


✔ Best Answer
April 10, 2013 at 18:00:42
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.



#1
April 10, 2013 at 13:40:02
Assuming your pairs start in A1, this code should do what you asked for.

I suggest you try this in a backup copy of your workbook since macros can not easily be undone. I also suggest that you test it on a short list so that you can tell right away if it does what want.

It worked for the 4 rows of data in your example, so I assume it will work for a longer list.

Sub MatchPairs()
'Determine last Row with data Column A
  lastRw = Range("A" & Rows.Count).End(xlUp).Row
'Loop through Column A, looking for matching value in Column B
   For rw = 1 To lastRw
    With Range("B1:B" & lastRw)
     Set m = .Find(Range("A" & rw))
      If m Is Nothing Then
'If matching value 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
End Sub

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


Report •

#2
April 10, 2013 at 14:18:44
DerbyDad03,

Thank you for your reply. As I am not too familiar with creating Macros or running Macros on Excel, are you able to provide a simple step by step process of how I am able to begin Macro, incorporate the code you wrote, and finally being able to implement it with my data set.

Thank you very much.


Report •

#3
April 10, 2013 at 14:57:22
Hi DerbyDad03,

I managed to figure out how to enable Macro on Excel and run it with your code. Unfortunately, it does not provide the "exact opposite pair" for every AB pair.
Will you be able to figure out the code to generate "exact opposite pairs" for the example data set below?
My goal is to make it robust so that however many rows there are in the data set (even up to thousands), I will be able to check through every AB pair, and include "exact opposite pair" for those that do not have it in the list.
Thank you very much.

A B
373 374
374 218
374 362
374 373
374 375
374 389
375 203
375 374
375 376
376 173
376 375
376 377
377 35
377 50
377 360
377 376
377 378
378 8
378 377
379 18
379 380
380 159
380 379
380 381
381 121
381 380
381 382
382 381
382 383
383 366
383 382
383 384
383 400
384 33
384 401
385 369
385 384
385 386
385 402
386 370
386 385
386 387
386 403
387 371
387 386
387 388
387 404
388 34
388 49
388 387
388 389
388 405
389 374
389 388


Report •

Related Solutions

#4
April 10, 2013 at 18:00:42
✔ 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.


Report •

#5
April 11, 2013 at 09:41:04
Thousand thank you's, DerbyDad03! You have been a tremendous help!

The updated Macro code you provided performed exactly what I wanted. I should have given you a better example to begin with for you to have a clearer idea, apologize for that. Regardless, you truly are a master of Excel in my opinion.

Thanks again for everything!


Report •


Ask Question