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.
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.
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.
If the reverse pair is not found in Column C, then the reverse values are placed at the bottom of Columns A & B.
Click Here Before Posting Data or VBA Code —> How To Post Data or Code.