Create pivottable with 2 tables with duplicates in each column

[Acoma7]

Hi
I am trying to work out how to make a pivottable that counts how many members are in each group that exist in each club.
Names are the members of each group or club.
The pivottable in the example is to give you an idea of what I am trying to work out.
I have tried using Data Modal but it says I can not do this with duplicates entries in the columns.
So a bit stuck, any advice would be really greatful.
The full data model is about 4000 names, 200 groups and 100 clubs.

Example.xlsx
	A	B	C	D	E
1	Name	Groups
2	PersonA	Group1
3	PersonB	Group1
4	PersonC	Group2
5	PersonD	Group3
6	PersonA	Group3
7	PersonB	Group2
8	PersonD	Group4
9	PersonB	Group3
10	PersonC	Group4
11	PersonD	Group1
12	PersonA	Group4
13
14	Name	Clubs
15	PersonA	Club2
16	PersonB	Club3
17	PersonC	Club4
18	PersonD	Club1
19	PersonA	Club3
20	PersonB	Club2
21	PersonD	Club2
22	PersonB	Club4
23	PersonC	Club1
24	PersonD	Club3
25	PersonA	Club4
26
27	PivotTable	Club1	Club2	Club3	Club4
28	Group1	1	3	3	2
29	Group2	1	1	1	2
30	Group3	1	3	3	2
31	Group4	2	2	2	2
Groups

 

[alansidman]

Power Query Solution

let
    T1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    T2=Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    MQ = Table.NestedJoin(T1, {"Name"},T2, {"Name"}, "Table2", JoinKind.FullOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(MQ, "Table2", {"Clubs"}, {"Table2.Clubs"}),
    #"Pivoted Column" = Table.Pivot(#"Expanded Table2", List.Distinct(#"Expanded Table2"[Table2.Clubs]), "Table2.Clubs", "Name", List.Count),
    #"Reordered Columns" = Table.ReorderColumns(#"Pivoted Column",{"Groups", "Club1", "Club2", "Club3", "Club4"})

in
    #"Reordered Columns"

Book6
	A	B	C	D	E
1	Name	Groups
2	PersonA	Group1
3	PersonB	Group1
4	PersonC	Group2
5	PersonD	Group3
6	PersonA	Group3
7	PersonB	Group2
8	PersonD	Group4
9	PersonB	Group3
10	PersonC	Group4
11	PersonD	Group1
12	PersonA	Group4
13
14	Name	Clubs
15	PersonA	Club2
16	PersonB	Club3
17	PersonC	Club4
18	PersonD	Club1
19	PersonA	Club3
20	PersonB	Club2
21	PersonD	Club2
22	PersonB	Club4
23	PersonC	Club1
24	PersonD	Club3
25	PersonA	Club4
26
27
28	Groups	Club1	Club2	Club3	Club4
29	Group1	1	3	3	2
30	Group2	1	1	1	2
31	Group3	1	3	3	2
32	Group4	2	2	2	2
Sheet2