Count combination of two words in comma separated column

[StephenJPSheehan]

Hello,

I am looking to try and count the combinations of two countries, separated by commas, in order to form a matrix. The data I am looking at is based on hurricane landfall per country.

Please see the attached spreadsheet which has an extract of the original data and the desired output.

I've tried a few different ways with the Transpose/Summarize functions but haven't quite cracked it! Any help would be greatly appreciated.

Thanks,

Stephen

[Felipe_Ribeir0 · Accepted solution]

Hi @StephenJPSheehan 

One way of doing this (cool problem by the way):

[Felipe_Ribeir0]

Hi @StephenJPSheehan 

It worked?

If yes, please accept it as a solution :)

Thanks!

[KrishnaChithrathil]

@Felipe_Ribeir0 

had to refer your code just to understand the scenario. 😄

[StephenJPSheehan]

@Felipe_Ribeir0 - your solution did work, thank you for your help!