Compare Social Security Number in 2 columns

[AndiH]

I have 2 colums with SSN's in general format. Submitted Claims range - A2:A1709. Paid Claims B2:B4232. I need to know which of the submitted claims have not been paid. (Which SSNs in Col A are not in Col B).

Excel 2007.

 

[VoG]

Try in C2 copied down

=IF(ISNUMBER(MATCH(A2,B:B,0)),"Paid","Not paid")

 

[dazfoz]

Andi,

You could put a simple formula into column c2 and copy down to C1709

=countif(B:B,A2)

This would return a 1 for every claim that has been paid, with the added bonus that it would return greater than 1 if the claim has been paid more than once (Assuming that these are one off claims and you should only be paying them once!)

You could also place conditional formatting into the claim cell, to highlight those that have been paid/not paid.

But I'm sure a Guru will post a much better method soon.

Daz

 

[Joneye]

Andi,

You could put a simple formula into column c2 and copy down to C1709

=countif(B:B,A2)

This would return a 1 for every claim that has been paid, with the added bonus that it would return greater than 1 if the claim has been paid more than once (Assuming that these are one off claims and you should only be paying them once!)

You could also place conditional formatting into the claim cell, to highlight those that have been paid/not paid.

But I'm sure a Guru will post a much better method soon.

Daz

Correct me if im wrong here, im sure that formula only works if you have a-z your data, as A2 changes each time?

 

[AndiH]

Hi there,
I like the premise and while the Paid/Unpaid status works for the majority of SSNs, there are quite a few calculation fields where I get a blank instead of stsatus. Some have numbers in both columns and others don't. Ideas? All fields are formatted general.

 

[AndiH]

Oops. Stupid user trick. It worked like a charm. THANK YOU!

 

[VoG]

If you are using my formula, I cannot see how it could possibly return a blank.

 

[dazfoz]

Correct me if im wrong here, im sure that formula only works if you have a-z your data, as A2 changes each time?

Only if you made A2 an absolute reference in the formula when you copied it down, otherwise when you copy the formula down it will become A3, A4, A5 which is what the OP required, to know if the SSN in column A, has been paid in B......Thats the way I would have done it anyway, just to make sure that I had not made any dupilcate payments.

.......like I said, I am no Guru.....and VOG has shown a much better way of doing it.