Compare values in 2 columns, return Y/N in a third column

[creed1101]

Hi

I am trying to compare numbers in column 2 againt column 1 and return yes or no if they exist in column 3.

Hovever these numbers are 8digit and begin with zero.

I am using the countif function.

But when i compare in col2 '01111111' with '1111111' in col1 I am getting a 'yes' in col3 where as I should be getting a 'no'.

Any ideas anyone??

cheers

[DonkeyOte]

COUNTIF will coerce the values to numerics and therefore sees (0)111111 = 111111

Are Columns 1 & 2 formatted to Text ? When you say you're comparing are you comparing column 2 to the entirety of column 1 or just the adjacent cell ?

[creed1101]

Both colums are formatted to text.

I am compare a cell in col2 to the whole of col1 to see if it is there.

[DonkeyOte]

Perhaps an ISNUMBER(MATCH test may be preferable, ie:

=IF(ISNUMBER(MATCH(B1,A:A,0)),"Yes","No")

[creed1101]

Sorry forgot to mention a few values may contain text eg 'a1234567'

[DonkeyOte]

Shouldn't make a difference... the ISNUMBER is testing to see if MATCH returned a numerical result (ie the row in which the match was found) - if it returned an Error (#N/A) then the ISNUMBER would return FALSE... from that boolean return you can determine if match found (TRUE) or not (FALSE).

[creed1101]

Thanks that worked

One final question:

Is there a way to stop a space being entered at the start or end of the string.

As im designing a form to be used by others, I have noticed ppl entering in a space after the number.

With the ISNUMBER(MATCH) Formula it returns 'no' if someone enters a space after a number in col1 but doesnt in col2.

thanks

[Paul]

You can use a macro to remove any leading or trailing spaces after a user has entered a cell value, however it might just be easier to include the TRIM() function in your formula.

=TRIM(B1) would remove any leading or trailing spaces in cell B1.