Aray_Countif tricky Question

[aborg88]

Hi guys

I got two columns

Name;

Contains name + surname

e.g.

andrew smith
andrew smith
john smith
andrew john

second column

credit card number e.g.

XXXXXXXXXXXX7240
XXXXXXXXXXXX7240
XXXXXXXXXXXX5985
XXXXXXXXXXXX9042

Now each row contains the users name, and the user's card number.

The above example is how it should be. A credit card can only be in use by one user.

The below example is something i want to catch...

Columns A
andrew smith
andrew andrew
john smith
andrew john

column B
XXXXXXXXXXXX7240
XXXXXXXXXXXX7240
XXXXXXXXXXXX5985
XXXXXXXXXXXX9042

The credit card has been used under two names.

------------

I only want a result when the credit card has been used under two DISTINCT NAMES;

the below array formula gives me a result if the user has used the card twice

{=IF(COUNT(IF($B$1:$B$4=$B1,ROW($A$1:$A$4),""))>1,"Card has Two Owners","")}

However, i only want a result/return if a card has been used by two distrinct names.

Hope you guys can help

[aborg88]

credits go to dilipandey who provided with ther original array

[NBVC]

Perhaps?

Please Login or Register  to view this content.

[aborg88]

perfect! thanks!

[Cutter]

aborg88
Please read over the forum rules. Particularly #5 (Duplicate thread) and #9 (Mark your thread as SOLVED).

This is a duplicate post and as such does not comply with Rule 5 of our forum rules. This thread will now be closed, you may continue in your other thread.

Thread Closed.