Jump to content
We've recently updated our Privacy Statement, available here. ×

Count number of times two columns match


Recommended Posts

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...