checking data against only the one client

**BigTandthepc** · 06-11-2012, 09:50 AM

Hi,

Anyone know how i can get a formular to check if data in any cell in colum B matches the data in B2 but only if the name in A2 matches that client as well?

**NBVC** · 06-11-2012, 09:55 AM

Do you mean?

=COUNTIFS(A:A,A2,B:B,B2)>1

this will result in TRUE if there are duplicates of A2 and B2 combo in the list.

**BigTandthepc** · 06-11-2012, 10:00 AM

Hi Looks like it works perfect,

will do a bit more checking but seems great, thank you so much.

:-)

**BigTandthepc** · 06-11-2012, 10:52 AM

Hi ,

ive adapted the formula so it does exactly what I need it to,

the only problem is it sees values as exact amounts so for example £2.10 matches £2.10, but £2.10 does not match £2.1011111111111 for example.

how do i make the fomula less exact so it will match the numbers?

the adapted formula is:

=IF(A15=0,0,COUNTIFS(Q:Q,Q15,L:L,L15)>1)

please help if you can

**NBVC** · 06-11-2012, 11:00 AM

You will need to round then.. so you will need to revert to SUMPRODUCT

eg.

=IF(A15=0,0,SUMPRODUCT(--(ROUND($Q$2:$Q$100,2)=ROUND(Q15,2)),--(ROUND($L$2:$L$100,2)=ROUND(L15,2)))

copied down

Note: with SUMPRODUCT it is advised to use minimum number of rows as necessary... too many rows will slow down efficiency.

An alternate would be to create 2 new helper columns that first Round the values, then you can use COUNTIFS against those helper columns.

**BigTandthepc** · 06-11-2012, 11:03 AM

Hi I'm Happy To Use Helper Colums can you give me the formula for this, as there is a lot of data?

thanks again so much for your help, i'm learning so much today.

**BigTandthepc** · 06-11-2012, 11:09 AM

i THINK iVE Done it! wow!

thanks you for your help, I looked at the formula you did for me and took the Round part and used it and it worked, thanks so much.