Count similar category presence

[vimalthehero]

dear team, 

im trying to achieve a special counting logic to find similar category present in an other columns

here is the input and desired output needed.

can someone suggest some ideas to acheive it.?

INPUT
SPORTS	GROUP
FOOTBALL	FOOTBALL;CRICKET;
CRICKET	CRICKET;VOLLEYBALL;
VOLLEYBALL	VOLLEYBALL;
	CRICKET;FOOTBALL;
	CRICKET;
	FOOTBALL;

OUTPUT
SPORTS	COUNT SPORTS PRESENCE
FOOTBALL	3
CRICKET	4
VOLLEYBALL	2

[aguirre · Accepted solution]

you could do:

GROUPS:
Load * Inline
[
GROUP
FOOTBALL;CRICKET
CRICKET;VOLLEYBALL
VOLLEYBALL
CRICKET;FOOTBALL
CRICKET
FOOTBALL
];

SPORTS:
LOAD
SubField(GROUP,';') as SPORT
Resident GROUPS;

DROP TABLE GROUPS;

and then with Count(Sport) you get the number of sport like in the picture