Auto-identification of account sharing from 2 columns of data

[RockandGrohl]

Hi guys,

We have a sheet here with two data columns and thousands of rows. One is "Account number" and the other is "Username"

We want to find out where more than one username is using the same account number.

Currently what we are doing is filtering down to suspected Usernames, and then copying the Account Number, then removing filters and filtering down on the account number to see if more than 1 username appear.

Each row is another transaction, so where "Bob" withdraws to his account "1234" 3 times in a year he will have 3 rows.

So what I need to have is a formula that counts how many times "1234" appears and then I suppose counts on how many usernames belong to that account number.

 

[RockandGrohl]

Humpty-bumpty

 

[jtakw]

Hi,

Can you show some sample data with expected results?

 

[RockandGrohl]

Sure!

Here's the same destination being used by 3 accounts (excluding blank and N/A)

I'd like to highlight cases where that happens.

Currently I'm using

=COUNTIFS(D:D,D1735,C:C,"<>"&C1735)

 

[jtakw]

So what's your expected result.
Does your COUNTIFS not work for you.
And, if possible, provide sample using XL2BB, see my signature, or at least in Table format.