Some basic help for a novice

[Jimboexcel]

Hi

I want to be able to calculate the number of names next to each other in each column.

In the table below Andy appears twice together in column 1 and also in column 2 for a total of 2. How to do this?

Any help appreciated.

Thanks

Jimbo

Mary	Andy	Jill	Andy
John	Andy	Mary	John
Bob	Jill	Bob	Jill
Jill	Harry	Harry	Bob
Harry	Bob	Harry	Harry
Andy	Mary	Andy	Mary
Andy	John	Jill	Andy

 

[OMGSALESFORCE2]

try out 10:09

 

[Zot]

It help if you update your profile to show your OS and Excel version. The best/possible solution is different from different Excel version.

 

[Jimboexcel]

I'm using excel 2010 version.

 

[Zot]

My solution now involved creating another table but I'm sure experts out there has better solution ?

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Mary	Andy	Jill	Andy		FALSE	TRUE	FALSE	FALSE		2
2	John	Andy	Mary	John		FALSE	FALSE	FALSE	FALSE
3	Bob	Jill	Bob	Jill		FALSE	FALSE	FALSE	FALSE
4	Jill	Harry	Harry	Bob		FALSE	FALSE	FALSE	FALSE
5	Harry	Bob	Harry	Harry		FALSE	FALSE	FALSE	FALSE
6	Andy	Mary	Andy	Mary		TRUE	FALSE	FALSE	FALSE
7	Andy	John	Jill	Andy		FALSE	FALSE	FALSE	FALSE
Sheet1

Cell Formulas
Range	Formula
F1:I7	F1	=AND(A1="ANDY",EXACT(A1,A2))
K1	K1	=COUNTIF(F1:I7,"TRUE")

 

[Zot]

If you are interested in UDF, then can use this function

Function CountPair(rng As Range, str As String) As Long

For Each Column In rng.Columns
    For Each cell In Column.Cells
        If cell = cell.Offset(1, 0) And cell = str Then
            CountPair = CountPair + 1
        End If
    Next
Next

End Function

Usage

Andy.xlsx
	A	B	C	D	E	F
1	Mary	Andy	Jill	Andy
2	John	Andy	Mary	John
3	Bob	Jill	Bob	Jill
4	Jill	Harry	Harry	Bob
5	Harry	Bob	Harry	Harry
6	Andy	Mary	Andy	Mary
7	Andy	John	Jill	Andy		2
Sheet1

Cell Formulas
Range	Formula
F7	F7	=CountPair(A1:D7,"Andy")

 

[Jimboexcel]

Thanks Zot, will give it a try