Dear all,

Please take a look at the attached screenshot of a dummy dataset. How do I generate a variable Relatives, which tells me for every individual in a household, how many others in that household have the same colour as that individual?

For example, in household A, there are 2 red people, 2 blue people, and 2 pink people. Therefore individual A1 has 1 other relative who has the same colour as him - red. Similarly, individual A3 has 1 other relative who has the same colour as him - blue. And similarly, in household B, individual B4 is the only pink person, therefore the number of relatives who have the same colour as him is 0.

The actual data set has a lot more colours and a lot more households.

Thanks in advance!