Count number of times two columns match

[Juan Pablo Melgarejo Zamora]

Hi all,

I am stuck in what I thought would be a simple calculation.

I have two columns with permutated values, and I want to count the number of times they match for the first column

For example:

 

 

 

Column1

Column2

Calculated col

 

 

Person1

Person1

3

 

 

Person1

Person1

3

 

 

Person1

Person1

3

 

 

Person1

Person2

3

 

 

Person2

Person1

2

 

 

Person2

Person2

2

 

 

Person2

Person2

2

 

 

Person2

Person4

2

 

 

Person2

Person5

2

 

 

 

Thank you,

Juan Pablo.

[Gaia Paolini]

You calculate it as

Sum(Integer([Column1]=[Column2])) OVER ([Column1])

optionally wrap an Integer() around the result (or simply format to zero decimals).

[Column1]=[Column2] is a boolean, and its integer representation is 1 for True and 0 for False. You then add them up grouping by [Column1]

 

Gaia