Solved Searching for matching pairs of data in Excel

DerbyDad03 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.

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.

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

DerbyDad03 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.

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!