identify rows that contain 2 cells with matching, but switched, values

**srur** · 10-01-2011, 02:30 PM

Hi,

I’m using Excel 2004 for Mac.
I have a whopper of an excel problem, and I don’t have the skills yet to be able to crack it. I’d be so grateful to anyone who could help.

I have a sheet of data, a few thousand rows down by a few dozen columns wide. Each row is data provided by one member of a couple (ie romantic/married). I have matched up most of partners (ie who form a couple) based on other more dependable methods. My goal now is to match up the partners for whom the dependable method didn’t work.

In one of columns (let’s call it A) the person entered the first 3 letters of his/her own name. In the second column (eg, B) the person entered the first 3 letters of his/her partner’s name.

As an example, let’s imagine these are the two columns. I’ll use commas to separate the columns.

bob,jen
jen,bob
abc,cde
fgh,ijk
lmn,opq

The first 2 rows are very possibly a couple. the last 3 rows are clearly not couples. I'd like to identify the rows that are likely couples, such as the 1st and second.

Can anyone figure out a way to do this? So many thanks to anyone who can help!

David

**beaunydal** · 10-01-2011, 07:46 PM

Maybe something like the attached doc.
However, it will only work when there are no duplicate pairs such as more than one row with bob/jen or more than one row with jen/bob. Say the formula is in a row where the pair is bob/jen, it searches for the row with jen/bob. If more than one row has jen/bob the formula returns the sum of those row numbers, which is useless.

Beau Nydal

**Nazerith** · 10-01-2011, 10:12 PM

can you please link your work book. or a demo work book

**srur** · 10-02-2011, 05:06 PM

Thanks for the help so far! Beau, I'll check that option out, but the problem you mention will likely render it useless. There are quite a few cases, so duplicate pairs are be likely.

(**EDIT** Beau, I spoke too soon. I just gave your awesome formula a whirl, and it identified 10 couples! This is so great; it benefits not only me, but those couples too. So, thank you! By the way, it appears that in no case was the number returned a sum of two numbers.)

Nazerith, I've attached a sample of the file. Names have been scrambled, but I've arranged that the first two cases are likely a couple, Jos and Dav.

(**EDIT** In light of the success I've had with Beau's solution, I think the problem is pretty well solved. However, I'll leave this up in case it benefits someone, or in case Nazerith is already at work on a solution. Regardless, if Naz comes up with something, I'll give it a go too.)

In reality, there are about 150 columns (each column being a variable, or questionnaire item) and over 500 rows (each row is a participant). Each participant enters his/her own name (first 3 letters) and his/her partner's name. In the actual dataset, I expect that there will be few matches for 2 reasons: I have used a primary matching system that has removed 200 couples already; and, it is likely that the second partner in a couple never actually completes the survey.

Thanks again for the help!