Microsoft Excel 2010 - complete product...

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.

✔ 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 374373Once 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 = 374373If 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.

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

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 374373Once 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 = 374373If 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!

Ask Your Question

Weekly Poll

Do you think Google should sell budget phones in the US?

Discuss in The Lounge

Poll History