# 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 B100 162*300 159268 465162 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

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 = 374373If 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 B373 374374 218374 362374 373374 375374 389375 203375 374375 376376 173376 375376 377377 35377 50377 360377 376377 378378 8378 377379 18379 380380 159380 379380 381381 121381 380381 382382 381382 383383 366383 382383 384383 400384 33384 401385 369385 384385 386385 402386 370386 385386 387386 403387 371387 386387 388387 404388 34388 49388 387388 389388 405389 374389 388

Report •

Related Solutions

#4
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 = 374373If 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.