Identifying Reoccurring Account Numbers

[laxd17]

I have 5 columns of account numbers, I need to identify account numbers that:

1. Appear in column 1
AND
2. Appear in at least 3 of the other 4 columns

Anyone know how to identify the account numbers that fit that criteria?

Thanks!

 

[MickG]

Try this:-
Data Assumed to be in columns 1 to 5.
Account numbers in column "A" shown in "Yellow" if count in other Columns >= 3.

[COLOR="Navy"]Sub[/COLOR] MG28Oct28
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer,[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    c = 0
    [COLOR="Navy"]For[/COLOR] n = 1 To 4
        c = c + IIf(Application.CountIf(Rng.Offset(, n), Dn.Value) > 0, 1, 0)
    [COLOR="Navy"]Next[/COLOR] n
    [COLOR="Navy"]If[/COLOR] c >= 3 [COLOR="Navy"]Then[/COLOR] Dn.Interior.ColorIndex = 6
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Special-K99]

Assuming the columns are A B C D E and the numbers do not repeat in each column

in F1
=IF(COUNTIF(B1:B10000,A1)+COUNTIF(C1:C10000,A1)+COUNTIF(D1:D10000,A1)+COUNTIF(E1:E10000,A1)>=3,"Y","")
and copy down column F

Y indicates the numbers you want

 

[Learning To Excel]

You could use something like the formula below where:
1. The first column is A
2. The other four columns are B:E
3. The first Account to test is in A1

=IF(SUM(IF(IFERROR(MATCH(A1,A:A,0),0)=0,0,1),
IF(IFERROR(MATCH(A1,B:B,0),0)=0,0,1),
IF(IFERROR(MATCH(A1,C:C,0),0)=0,0,1),
IF(IFERROR(MATCH(A1,D:D,0),0)=0,0,1),
IF(IFERROR(MATCH(A1,E:E,0),0)=0,0,1))
>=4,"Success","Fail")

 

[laxd17]

Awesome! Thanks all!!!
Keith

 

[MickG]

You're welcome