Finding switched matches

English
, , , ,
#1

Working with this list of names, for example:

  1. John, Smith
  2. Jane, Doe
  3. John, Doe
  4. Smith, Joe
  5. Smith, John
  6. Doe, Jane
  7. Smith, Smith
  8. Doe, Jane
  9. Doe, Doe
  10. Jane, John

How would I find all the cases where there is a match, but switched? Returning (or marking): 1, 2, 5, 6, 8

Possible parts of the solution I’m considering:

=MATCH(A2,B:B,0) returns 6 (where “Jane” in first column matches “Jane” in 2nd column).

Also trying out nested IF statements.

EDIT:
Based on Opaque’s chart below, this is the desired outcome:
image description

#2

Working with this list of names,

Your list is unclear (at least to me). Is it organized like A (3 columns),B (2 columns) or C (1 column)

image description

#3

Either is fine, but A

Which would change my possible part I’ve been trying to use to =MATCH(B2,C:C,0)

(Also it is not too hard to switch between your C or B to A using Text to Column in the Data menu)

#4

Either is fine, but A

I did ask information about how it is - but this response makes me assume the design is still to do.

#5

I actually have the data in both B and A.

#6

Hello,

one(!) solution may be in the following sample file:

FindReverseNames.ods

Please note
This solution completely ignores the case where value in column B and C of a row are identical (as per your desired outcome statement). In other words: The solution does not find duplicates of Doe,Doe or Smith,Smith in the same row even if they appear twice (or more often).

#7

Great, I’m checking it with a larger list of data.

#8

It works well, except for if there is a blank in one of the two columns. Those get a check.

Also it takes lots of memory. I only got it to work with a few 100 as it has crashed with my 10,000 size list.

#9

To save some memory, I switched it to just this:

=MATCH(A2&B2,B:B&A:A,0)

Then I can later delete the "#N/A"s

#10

Why does =MATCH(A2&B2,B:B&A:A,0) not work in Excel?

#11

Why does =MATCH(A2&B2,B:B&A:A,0) not work in Excel?

Ask in a Excel forum - this site is about LibreOffice (and I got no Excel since decades)

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.