Compare data in col A+B wih data in col C+D

[Leo]

Excel 2000: Windows XP:

I thought I could do this with the IF statement, but couldn't, and I do not
know programming. I have a spreadsheet with thousands of account numbers
listed in column "B" that are preceeded by a state (e.g., PA, NY, DE, etc.)
in Column "A". Likewise, there are similar state and account numbers in
columns "C" and "D". I need to know what accounts listed in columns "A" and
"B" are not listed in columns "C" and "D" and have the results listed
separately, say, columns "E" and "F".

 

[Max]

One way which gets you there ..

Source data in cols A and B, to be compared with corresponding data in cols
C and D. Data assumed running in row2 down, with extent in cols C and D
assumed to row100

Put in E2, array-enter the formula by pressing CTRL+SHIFT+ENTER (CSE)
(instead of just pressing ENTER):

=IF(OR(A2="",B2=""),"",IF(ISNUMBER(MATCH(A2&B2,$C$2:$C$100&$D$2:$D$100,0)),"",ROW()))

Leave E1 blank. Do ensure that the formula is array-entered correctly. It
should appear wrapped by curly braces: { } within the formula bar. These
braces are inserted by Excel. If you don't see the braces, it hasn't been
array-entered. Click inside the formula bar and try the CSE again.

Put in F2 (normal ENTER will do):
=IF(ROW(A1)>COUNT($E:$E),"",INDEX(A:A,SMALL($E:$E,ROW(A1))))

Copy F2 to G2. Select E2:G2, copy down to the last row of data in cols A and
B. The required results will be returned in cols F and G, all neatly bunched
at the top.

 

[Leo]

Thank you Max for taking the time to write those long formulas. Everything
works like a charm except for cells A2&B2 matching C3&D3, yet appearing in
E2&F2&G2, contrary to your formula. Everything else down the line works
fine. I cleared the cell contents and re-entered matching data and got the
same results. The only thing I changed in your formula was the ending row,
from 100 to 10000.

A B C D E F G
PEC ACCOUNT BU Account
PA 888889 NJ 777722 2 PA 888889
NJ 54321 PA 888889 3 NJ 54321
DE 23456 CA 122345 4 DE 23456
CA 122345 PA 25252 NE 678910
NE 678910 DE 141516 6 PA 12345
NE 111213 NE 114455 NJ 54321
DE 141516 NE 111213 DE 23456
PA 12345 WA 233678 9 NE 678910
NJ 54321 MA 544678 10 MD 546789
DE 23456 Wy 333778 11 DC 345561
CA 122345 RI 987899
NE 678910 MA 222111 13
NE 111213
DE 141516
MD 546789 16


DC 345561 19

 

[Max]

.. 2 PA 888889

I replicated your results over here. Think you got the above anomaly because
the top cell formula in E2 isn't array-entered (while the rest in E3 down is
ok). Just do a quick visual check - look at E2's formula bar - are there
curly braces wrapped around the formula ? Re-click inside E2's formula bar,
press CSE* to array enter.
*CTRL+SHIFT+ENTER

Believe E2 was originally array-entered correctly (since the others in E3
down are filled correctly as array formulas) but you might have somehow
re-clicked into E2's formula bar subsequently (edit mode) and then
"accidentally" pressed ENTER in exiting the cell instead of pressing Esc. The
CSE needs to be re-done each time you "edit" the cell.

 

[Leo]

Bingo! You were correct about cell E2 not being entered by CSE and very
politely letting me know what I "accidentally" did.

After employing your formulas, over 1600 accounts were found not to be in
the master list of near 10,000 accounts, saving a lot of time doing it
manually.

Again, many thanks, Max.

 

[Max]

Glad to hear that !
You're welcome, Leo.