Need formula to compare text in two cells, indicate if they match, not move when corrections are inserted

Synthia

Board Regular
Joined
Dec 11, 2009
Messages
56
Office Version
  1. 365
Platform
  1. Windows
  2. Web
HI, Thanks in advance, (-:
I have 39000+ rows of data in four columns First name A, Last name A, First Name B, Last Name B.
The lists of names are from two different GEDCOM files that need to match but do not match. (I need to see the matches and the mismatches easily, as reference to repair them)
So, I am comparing the "A" names to the "B" names and in a 5th row want to see TRUE or FALSE for the names in each row, AND when I insert cells in columns First name A, Last name A to push down the names so they are on the same row as their matches in First Name B, Last Name B.
Right now, I am only comparing the first names, because I don't know enough Excel to write the correct formula to compare text in two cells to text cells, without changing the formula when the cells are realigned to match up correctly, help with that would be appreciated.
Pasted below is what I am currently using that does not compare two cells to two cells AND when I insert cells to make corrections so that the names that do match line up correctly, the formula changes. I have about 24,000 names to go, there has to be an easier way than redraggign the formula down after making every insert so the names line up....THANK YOU.
1st column is last name A, 2nd column is first name A, 3rd column is first name B, 4th column is last name B, 5th column is the current result of the formula/place to show matches/mismatches.
1627912705674.png

1st column is last name A, 2nd column is first name A, 3rd column is first name B, 4th column is last name B, 5th column is the formula/place to show matches/mismatches.
1627913119610.png
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

JGordon11

Board Regular
Joined
Jan 18, 2021
Messages
236
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
You could try

=IF(AND(EXACT(INDIRECT("A"&ROW()),INDIRECT("D"&ROW())),EXACT(INDIRECT("B"&ROW()),INDIRECT("C"&ROW()))),"T",FALSE)

It uses the INDIRECT function which won't change the formula when you move or insert cells. It is however volatile, meaning it will recalculate every time you make a change to the worksheet. 39000 of these formulas might slow down your worksheet so maybe use about 1000 of them at a time and revert back to your previous formula for correct lines or something like

=IF(AND(EXACT(A2,D2),EXACT(B2,C2),"T",FALSE) before you accumulate a ton of the INDIRECT functions

I used "T" and FALSE rather than TRUE and FALSE so the FALSEs stand out more, but you can just as easily leave the IF out and it will return TRUE or FALSE.

i.e., =AND(EXACT(INDIRECT("A"&ROW()),INDIRECT("D"&ROW())),EXACT(INDIRECT("B"&ROW()),INDIRECT("C"&ROW())))

If you don't care about case sensitivity (Alice = alice) then just go with

=IF(AND(INDIRECT("A"&ROW())=INDIRECT("D"&ROW()),INDIRECT("B"&ROW())=INDIRECT("C"&ROW())),"T",FALSE)

Book3
ABCDE
1Last Name AFirst Name AFirst Name BLast Name BMatch?
2adamsalicealiceadamsT
3cobbcharliebettybrownFALSE
4eckmanernestcharliecobbFALSE
5goldfingergloriadeltadawnFALSE
6ernesteckmanFALSE
7franklinfestivusFALSE
8gloriagoldfingerFALSE
Sheet1
Cell Formulas
RangeFormula
E2:E8E2=IF(AND(EXACT(INDIRECT("A"&ROW()),INDIRECT("D"&ROW())),EXACT(INDIRECT("B"&ROW()),INDIRECT("C"&ROW()))),"T",FALSE)


After inserting two cells the formula hasn't changed and provides the intended result:

Book3
ABCDE
1Last Name AFirst Name AFirst Name BLast Name BMatch?
2adamsalicealiceadamsT
3bettybrownFALSE
4cobbcharliecharliecobbT
5eckmanernestdeltadawnFALSE
6goldfingergloriaernesteckmanFALSE
7franklinfestivusFALSE
8gloriagoldfingerFALSE
Sheet1
Cell Formulas
RangeFormula
E2:E8E2=IF(AND(EXACT(INDIRECT("A"&ROW()),INDIRECT("D"&ROW())),EXACT(INDIRECT("B"&ROW()),INDIRECT("C"&ROW()))),"T",FALSE)
 
Solution

Forum statistics

Threads
1,143,686
Messages
5,720,290
Members
422,274
Latest member
steefq

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
Top