Check if a pair exists in column

bsweet0us

New Member
Joined
Apr 12, 2008
Messages
38
Office Version
  1. 365
Platform
  1. Windows
I think this is fairly straightforward, but I'm not versed in VBA enough to figure out the best way to do it.

Here's the scenario. I have values in Sheet1 that run from M6 down the column an indeterminate amount. I have another set of values beginning in N6 and running down the same number of rows as those in column M. The values in the corresponding row in M and N make up a pair. (M6 and N6 make a pair. M7 and N7 make a pair. Etc.)

On Sheet2, beginning in cell C5 and continuing down an indeterminate number of rows a list of values. The values in adjacent rows make a pair and the first member of the pair is always in an odd row. (C5 and C6 are a pair. C7 and C8 are a pair. Etc.)

I need to determine if ANY of the pairs in columns M & N match a pair in column C on the other sheet. The pairs are in no particular order but could be placed in alphabetical order without issue as long as the pairs stay in adjacent cells. All the values are names of people, so they are all strings. Values are first and last names in the same cell.

I can provide additional details as needed and I look forward to the magic this community comes up with for me!
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
I'm not versed in VBA enough to figure out the best way to do it.
So, perhaps a formula approach?

ABCDEF
1Data1MatchesData2Matches
2qwer1Blah 
3tyui0Blah 
4opas0Blah 
5dfgh0Blah 
6jklz2Blah 
7xcvb0Blah 
8jk5
9lz 
10Blah 
11Blah 
12qw1
13er 
14Blah 
15Blah 
16jk5
17lz 
18Blah 
19Blah 
20Blah 
21Blah 
Sheet3
Cell Formulas
RangeFormula
C2:C7C2=COUNTIF(F$2:F$21,ROWS(C$2:C2))
F2:F21F2=IFERROR(IF(MOD(ROWS(E$2:E2),2)=1,MATCH(E2&"|"&E3,INDEX(Data1,,1)&"|"&INDEX(Data1,,2),),""),"")
Named Ranges
NameRefers ToCells
Data1=Sheet3!$A$2:$B$7F2:F21
Data2=Sheet3!$E$2:$E$20F2:F21


Good luck. There are umpteen ways that first and last name data won't match exactly.
 
Upvote 0
So, perhaps a formula approach?

ABCDEF
1Data1MatchesData2Matches
2qwer1Blah 
3tyui0Blah 
4opas0Blah 
5dfgh0Blah 
6jklz2Blah 
7xcvb0Blah 
8jk5
9lz 
10Blah 
11Blah 
12qw1
13er 
14Blah 
15Blah 
16jk5
17lz 
18Blah 
19Blah 
20Blah 
21Blah 
Sheet3
Cell Formulas
RangeFormula
C2:C7C2=COUNTIF(F$2:F$21,ROWS(C$2:C2))
F2:F21F2=IFERROR(IF(MOD(ROWS(E$2:E2),2)=1,MATCH(E2&"|"&E3,INDEX(Data1,,1)&"|"&INDEX(Data1,,2),),""),"")
Named Ranges
NameRefers ToCells
Data1=Sheet3!$A$2:$B$7F2:F21
Data2=Sheet3!$E$2:$E$20F2:F21


Good luck. There are umpteen ways that first and last name data won't match exactly.

Thanks for the help, but this isn't exactly what I'm looking for.
So, perhaps a formula approach?

ABCDEF
1Data1MatchesData2Matches
2qwer1Blah 
3tyui0Blah 
4opas0Blah 
5dfgh0Blah 
6jklz2Blah 
7xcvb0Blah 
8jk5
9lz 
10Blah 
11Blah 
12qw1
13er 
14Blah 
15Blah 
16jk5
17lz 
18Blah 
19Blah 
20Blah 
21Blah 
Sheet3
Cell Formulas
RangeFormula
C2:C7C2=COUNTIF(F$2:F$21,ROWS(C$2:C2))
F2:F21F2=IFERROR(IF(MOD(ROWS(E$2:E2),2)=1,MATCH(E2&"|"&E3,INDEX(Data1,,1)&"|"&INDEX(Data1,,2),),""),"")
Named Ranges
NameRefers ToCells
Data1=Sheet3!$A$2:$B$7F2:F21
Data2=Sheet3!$E$2:$E$20F2:F21


Good luck. There are umpteen ways that first and last name data won't match exact
This seems to give me the result I need, but I think it's going to have to be coded.

Here's the purpose of the workbook. It is for a doubles bowling tournament in which each entrant is randomly paired with another entrant. However, each entrant can also choose other entrants as partners. So, if John enters the tournament, he's paired with another entrant but he doesn't know who it is. John wants to bowl with Cindy and Bobby for sure, so he enters with them but he is still going to be paired with another entrant at random.

The workbook consists of two sheets. One has a list of the entrants and the other has a list of pre-selected partners (like John/Cindy and John/Bobby from above). Once all entrants are inputted, there is code that randomizes the list into pairs (placed in the M and N columns from my initial post). Using the example from above, the code that I'm seeking needs to see if John was randomly paired with Cindy or Bobby and re-randomize if that is the case because he is already partnered with them. The list of entrants is generally less than 50 people in size and not everybody enters with a partner, so I think the odds of infinite re-randomizations is very small.
 
Upvote 0
Ah, you already have code. That changes things a little. It would make more sense to modify your existing VBA code to check for matches before putting the results into Excel.

You're probably better of now posting your code in a new thread, perhaps called something like: VBA code to generate random pairs without duplicates
 
Upvote 0

Forum statistics

Threads
1,224,828
Messages
6,181,217
Members
453,024
Latest member
Wingit77

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top