Many to Many?

[s1192]

All

First time on the site so appologies if I can find the answer elsewhere.

I have a many to many situation, that I am looking to resolve to give me one dataset. Any advice on formula/ add ins that can be used (cant code in VBA) would be appreicated.

Eg Two data sets of

[TABLE="width: 192"]
<colgroup><col width="64" style="width:48pt" span="3"> </colgroup><tbody>[TR]
[TD="width: 64, bgcolor: transparent"]Contract[/TD]
[TD="width: 64, bgcolor: transparent"]Reinsurer[/TD]
[TD="width: 64, bgcolor: transparent"]Share[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]001X[/TD]
[TD="bgcolor: transparent"]Munich[/TD]
[TD="bgcolor: transparent, align: right"]0.3[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]001X[/TD]
[TD="bgcolor: transparent"]Swiss[/TD]
[TD="bgcolor: transparent, align: right"]0.6[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]001X[/TD]
[TD="bgcolor: transparent"]Lloyds[/TD]
[TD="bgcolor: transparent, align: right"]0.1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]002X[/TD]
[TD="bgcolor: transparent"]LLoyds[/TD]
[TD="bgcolor: transparent, align: right"]0.2[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]002X[/TD]
[TD="bgcolor: transparent"]Muncih[/TD]
[TD="bgcolor: transparent, align: right"]0.3[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]002X[/TD]
[TD="bgcolor: transparent"]RSA[/TD]
[TD="bgcolor: transparent, align: right"]0.1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]002X[/TD]
[TD="bgcolor: transparent"]Everest[/TD]
[TD="bgcolor: transparent, align: right"]0.1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]002X[/TD]
[TD="bgcolor: transparent"]AIG[/TD]
[TD="bgcolor: transparent, align: right"]0.2[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]002X[/TD]
[TD="bgcolor: transparent"]AXA[/TD]
[TD="bgcolor: transparent, align: right"]0.1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]003X[/TD]
[TD="bgcolor: transparent"]RSA[/TD]
[TD="bgcolor: transparent, align: right"]0.5[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]003X[/TD]
[TD="bgcolor: transparent"]AIG[/TD]
[TD="bgcolor: transparent, align: right"]0.4[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]003X[/TD]
[TD="bgcolor: transparent"]lloyds[/TD]
[TD="bgcolor: transparent, align: right"]0.1
[/TD]
[/TR]
</tbody>[/TABLE]


and


[TABLE="width: 192"]
<colgroup><col width="64" style="width:48pt" span="3"> </colgroup><tbody>[TR]
[TD="width: 64, bgcolor: transparent"]Contract[/TD]
[TD="width: 64, bgcolor: transparent"]Risk Code[/TD]
[TD="width: 64, bgcolor: transparent"]Amount[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]001X[/TD]
[TD="bgcolor: transparent"]A[/TD]
[TD="bgcolor: transparent, align: right"]1000[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]001X[/TD]
[TD="bgcolor: transparent"]B[/TD]
[TD="bgcolor: transparent, align: right"]2000[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]001X[/TD]
[TD="bgcolor: transparent"]C[/TD]
[TD="bgcolor: transparent, align: right"]2500[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]001X[/TD]
[TD="bgcolor: transparent"]D[/TD]
[TD="bgcolor: transparent, align: right"]3000[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]001X[/TD]
[TD="bgcolor: transparent"]E[/TD]
[TD="bgcolor: transparent, align: right"]4000[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]001X[/TD]
[TD="bgcolor: transparent"]F[/TD]
[TD="bgcolor: transparent, align: right"]1500[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]003X[/TD]
[TD="bgcolor: transparent"]G[/TD]
[TD="bgcolor: transparent, align: right"]10[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]003X[/TD]
[TD="bgcolor: transparent"]B[/TD]
[TD="bgcolor: transparent, align: right"]1500[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]003X[/TD]
[TD="bgcolor: transparent"]F[/TD]
[TD="bgcolor: transparent, align: right"]9000[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]003X[/TD]
[TD="bgcolor: transparent"]R[/TD]
[TD="bgcolor: transparent, align: right"]10000[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]003X[/TD]
[TD="bgcolor: transparent"]T[/TD]
[TD="bgcolor: transparent, align: right"]150000[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]002X[/TD]
[TD="bgcolor: transparent"]A[/TD]
[TD="bgcolor: transparent, align: right"]150[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]002X[/TD]
[TD="bgcolor: transparent"]B[/TD]
[TD="bgcolor: transparent, align: right"]1500[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]002X[/TD]
[TD="bgcolor: transparent"]C[/TD]
[TD="bgcolor: transparent, align: right"]150000[/TD]
[/TR]
</tbody>[/TABLE]

Would want to expand the second data set to include every reinsurer for every contract and their share (which I would multiply by the amount).

Thanks,

 

[DRSteele]

Unless I misunderstand what you're after, this will work. You will need to be using Excel365 for new functions SORT and UNIQUE to work. Otherwise, you can populate row E18:L18 with a unique list of all the insurers.

Copy E18 across and down as far as necessary.

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
3	Contract	Reinsurer	Share
4	001X	Munich	0.3
5	001X	Swiss	0.6
6	001X	Lloyds	0.1
7	002X	LLoyds	0.2
8	002X	Muncih	0.3
9	002X	RSA	0.1
10	002X	Everest	0.1
11	002X	AIG	0.2
12	002X	AXA	0.1
13	003X	RSA	0.5
14	003X	AIG	0.4
15	003X	lloyds	0.1
16
17
18	Contract	Risk Code	Amount		AIG	AXA	Everest	Lloyds	Muncih	Munich	RSA	Swiss
19	001X	A	1000		0	0	0	100	0	300	0	600
20	001X	B	2000		0	0	0	200	0	600	0	1200
21	001X	C	2500		0	0	0	250	0	750	0	1500
22	001X	D	3000		0	0	0	300	0	900	0	1800
23	001X	E	4000		0	0	0	400	0	1200	0	2400
24	001X	F	1500		0	0	0	150	0	450	0	900
25	003X	G	10		4	0	0	1	0	0	5	0
26	003X	B	1500		600	0	0	150	0	0	750	0
27	003X	F	9000		3600	0	0	900	0	0	4500	0
28	003X	R	10000		4000	0	0	1000	0	0	5000	0
29	003X	T	150000		60000	0	0	15000	0	0	75000	0
30	002X	A	150		30	15	15	30	45	0	15	0
31	002X	B	1500		300	150	150	300	450	0	150	0
32	002X	C	150000		30000	15000	15000	30000	45000	0	15000	0
Sheet30

Cell Formulas
Range	Formula
E18	=TRANSPOSE(SORT(UNIQUE(B4:B15)))
E19	=SUMPRODUCT(($A$4:$A$15=$A19)*($B$4:$B$15=E$18)*$C$4:$C$15)*$C19

[/FONT]

 

[alansidman]

Another Option will be to use Power Query and join the two tables on the Contract
Data Range
Data Range
[Table="class: grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][/tr]
[tr][td]

1​

[/td][td]

Contract​

[/td][td]

Risk Code​

[/td][td]

Amount​

[/td][td]

Table1.Reinsurer​

[/td][td]

Table1.Share​

[/td][td]

Custom​

[/td][/tr]


[tr][td]

2​

[/td][td]

001x​

[/td][td]

A​

[/td][td]

1000​

[/td][td]

Munich​

[/td][td]

0.3​

[/td][td]

300​

[/td][/tr]


[tr][td]

3​

[/td][td]

001x​

[/td][td]

A​

[/td][td]

1000​

[/td][td]

Swiss​

[/td][td]

0.6​

[/td][td]

600​

[/td][/tr]


[tr][td]

4​

[/td][td]

001x​

[/td][td]

A​

[/td][td]

1000​

[/td][td]

Lloyds​

[/td][td]

0.1​

[/td][td]

100​

[/td][/tr]


[tr][td]

5​

[/td][td]

001x​

[/td][td]

B​

[/td][td]

2000​

[/td][td]

Munich​

[/td][td]

0.3​

[/td][td]

600​

[/td][/tr]


[tr][td]

6​

[/td][td]

001x​

[/td][td]

B​

[/td][td]

2000​

[/td][td]

Swiss​

[/td][td]

0.6​

[/td][td]

1200​

[/td][/tr]


[tr][td]

7​

[/td][td]

001x​

[/td][td]

B​

[/td][td]

2000​

[/td][td]

Lloyds​

[/td][td]

0.1​

[/td][td]

200​

[/td][/tr]


[tr][td]

8​

[/td][td]

001x​

[/td][td]

C​

[/td][td]

2500​

[/td][td]

Munich​

[/td][td]

0.3​

[/td][td]

750​

[/td][/tr]


[tr][td]

9​

[/td][td]

001x​

[/td][td]

C​

[/td][td]

2500​

[/td][td]

Swiss​

[/td][td]

0.6​

[/td][td]

1500​

[/td][/tr]


[tr][td]

10​

[/td][td]

001x​

[/td][td]

C​

[/td][td]

2500​

[/td][td]

Lloyds​

[/td][td]

0.1​

[/td][td]

250​

[/td][/tr]


[tr][td]

11​

[/td][td]

001x​

[/td][td]

D​

[/td][td]

3000​

[/td][td]

Munich​

[/td][td]

0.3​

[/td][td]

900​

[/td][/tr]


[tr][td]

12​

[/td][td]

001x​

[/td][td]

D​

[/td][td]

3000​

[/td][td]

Swiss​

[/td][td]

0.6​

[/td][td]

1800​

[/td][/tr]


[tr][td]

13​

[/td][td]

001x​

[/td][td]

D​

[/td][td]

3000​

[/td][td]

Lloyds​

[/td][td]

0.1​

[/td][td]

300​

[/td][/tr]


[tr][td]

14​

[/td][td]

001x​

[/td][td]

E​

[/td][td]

4000​

[/td][td]

Munich​

[/td][td]

0.3​

[/td][td]

1200​

[/td][/tr]


[tr][td]

15​

[/td][td]

001x​

[/td][td]

E​

[/td][td]

4000​

[/td][td]

Swiss​

[/td][td]

0.6​

[/td][td]

2400​

[/td][/tr]


[tr][td]

16​

[/td][td]

001x​

[/td][td]

E​

[/td][td]

4000​

[/td][td]

Lloyds​

[/td][td]

0.1​

[/td][td]

400​

[/td][/tr]


[tr][td]

17​

[/td][td]

001x​

[/td][td]

F​

[/td][td]

1500​

[/td][td]

Munich​

[/td][td]

0.3​

[/td][td]

450​

[/td][/tr]


[tr][td]

18​

[/td][td]

001x​

[/td][td]

F​

[/td][td]

1500​

[/td][td]

Swiss​

[/td][td]

0.6​

[/td][td]

900​

[/td][/tr]


[tr][td]

19​

[/td][td]

001x​

[/td][td]

F​

[/td][td]

1500​

[/td][td]

Lloyds​

[/td][td]

0.1​

[/td][td]

150​

[/td][/tr]


[tr][td]

20​

[/td][td]

002x​

[/td][td]

A​

[/td][td]

150​

[/td][td]

Lloyds​

[/td][td]

0.2​

[/td][td]

30​

[/td][/tr]


[tr][td]

21​

[/td][td]

002x​

[/td][td]

A​

[/td][td]

150​

[/td][td]

Munich​

[/td][td]

0.3​

[/td][td]

45​

[/td][/tr]


[tr][td]

22​

[/td][td]

002x​

[/td][td]

A​

[/td][td]

150​

[/td][td]

RSA​

[/td][td]

0.1​

[/td][td]

15​

[/td][/tr]


[tr][td]

23​

[/td][td]

002x​

[/td][td]

A​

[/td][td]

150​

[/td][td]

Everest​

[/td][td]

0.1​

[/td][td]

15​

[/td][/tr]


[tr][td]

24​

[/td][td]

002x​

[/td][td]

A​

[/td][td]

150​

[/td][td]

AIG​

[/td][td]

0.2​

[/td][td]

30​

[/td][/tr]


[tr][td]

25​

[/td][td]

002x​

[/td][td]

A​

[/td][td]

150​

[/td][td]

AXA​

[/td][td]

0.1​

[/td][td]

15​

[/td][/tr]


[tr][td]

26​

[/td][td]

002x​

[/td][td]

B​

[/td][td]

1500​

[/td][td]

Lloyds​

[/td][td]

0.2​

[/td][td]

300​

[/td][/tr]


[tr][td]

27​

[/td][td]

002x​

[/td][td]

B​

[/td][td]

1500​

[/td][td]

Munich​

[/td][td]

0.3​

[/td][td]

450​

[/td][/tr]


[tr][td]

28​

[/td][td]

002x​

[/td][td]

B​

[/td][td]

1500​

[/td][td]

RSA​

[/td][td]

0.1​

[/td][td]

150​

[/td][/tr]


[tr][td]

29​

[/td][td]

002x​

[/td][td]

B​

[/td][td]

1500​

[/td][td]

Everest​

[/td][td]

0.1​

[/td][td]

150​

[/td][/tr]


[tr][td]

30​

[/td][td]

002x​

[/td][td]

B​

[/td][td]

1500​

[/td][td]

AIG​

[/td][td]

0.2​

[/td][td]

300​

[/td][/tr]


[tr][td]

31​

[/td][td]

002x​

[/td][td]

B​

[/td][td]

1500​

[/td][td]

AXA​

[/td][td]

0.1​

[/td][td]

150​

[/td][/tr]


[tr][td]

32​

[/td][td]

003x​

[/td][td]

G​

[/td][td]

10​

[/td][td]

RSA​

[/td][td]

0.5​

[/td][td]

5​

[/td][/tr]


[tr][td]

33​

[/td][td]

003x​

[/td][td]

G​

[/td][td]

10​

[/td][td]

AIG​

[/td][td]

0.4​

[/td][td]

4​

[/td][/tr]


[tr][td]

34​

[/td][td]

003x​

[/td][td]

G​

[/td][td]

10​

[/td][td]

Lloyds​

[/td][td]

0.1​

[/td][td]

1​

[/td][/tr]


[tr][td]

35​

[/td][td]

003x​

[/td][td]

B​

[/td][td]

1500​

[/td][td]

RSA​

[/td][td]

0.5​

[/td][td]

750​

[/td][/tr]


[tr][td]

36​

[/td][td]

003x​

[/td][td]

B​

[/td][td]

1500​

[/td][td]

AIG​

[/td][td]

0.4​

[/td][td]

600​

[/td][/tr]


[tr][td]

37​

[/td][td]

003x​

[/td][td]

B​

[/td][td]

1500​

[/td][td]

Lloyds​

[/td][td]

0.1​

[/td][td]

150​

[/td][/tr]


[tr][td]

38​

[/td][td]

003x​

[/td][td]

F​

[/td][td]

9000​

[/td][td]

RSA​

[/td][td]

0.5​

[/td][td]

4500​

[/td][/tr]


[tr][td]

39​

[/td][td]

003x​

[/td][td]

F​

[/td][td]

9000​

[/td][td]

AIG​

[/td][td]

0.4​

[/td][td]

3600​

[/td][/tr]


[tr][td]

40​

[/td][td]

003x​

[/td][td]

F​

[/td][td]

9000​

[/td][td]

Lloyds​

[/td][td]

0.1​

[/td][td]

900​

[/td][/tr]


[tr][td]

41​

[/td][td]

003x​

[/td][td]

R​

[/td][td]

10000​

[/td][td]

RSA​

[/td][td]

0.5​

[/td][td]

5000​

[/td][/tr]


[tr][td]

42​

[/td][td]

003x​

[/td][td]

R​

[/td][td]

10000​

[/td][td]

AIG​

[/td][td]

0.4​

[/td][td]

4000​

[/td][/tr]


[tr][td]

43​

[/td][td]

003x​

[/td][td]

R​

[/td][td]

10000​

[/td][td]

Lloyds​

[/td][td]

0.1​

[/td][td]

1000​

[/td][/tr]


[tr][td]

44​

[/td][td]

003x​

[/td][td]

T​

[/td][td]

150000​

[/td][td]

RSA​

[/td][td]

0.5​

[/td][td]

75000​

[/td][/tr]


[tr][td]

45​

[/td][td]

003x​

[/td][td]

T​

[/td][td]

150000​

[/td][td]

AIG​

[/td][td]

0.4​

[/td][td]

60000​

[/td][/tr]


[tr][td]

46​

[/td][td]

003x​

[/td][td]

T​

[/td][td]

150000​

[/td][td]

Lloyds​

[/td][td]

0.1​

[/td][td]

15000​

[/td][/tr]


[tr][td]

47​

[/td][td]

002x​

[/td][td]

C​

[/td][td]

150000​

[/td][td]

Lloyds​

[/td][td]

0.2​

[/td][td]

30000​

[/td][/tr]


[tr][td]

48​

[/td][td]

002x​

[/td][td]

C​

[/td][td]

150000​

[/td][td]

Munich​

[/td][td]

0.3​

[/td][td]

45000​

[/td][/tr]


[tr][td]

49​

[/td][td]

002x​

[/td][td]

C​

[/td][td]

150000​

[/td][td]

RSA​

[/td][td]

0.1​

[/td][td]

15000​

[/td][/tr]


[tr][td]

50​

[/td][td]

002x​

[/td][td]

C​

[/td][td]

150000​

[/td][td]

Everest​

[/td][td]

0.1​

[/td][td]

15000​

[/td][/tr]


[tr][td]

51​

[/td][td]

002x​

[/td][td]

C​

[/td][td]

150000​

[/td][td]

AIG​

[/td][td]

0.2​

[/td][td]

30000​

[/td][/tr]


[tr][td]

52​

[/td][td]

002x​

[/td][td]

C​

[/td][td]

150000​

[/td][td]

AXA​

[/td][td]

0.1​

[/td][td]

15000​

[/td][/tr]


[tr][td]

53​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]
[/table]

Here is the Mcode

let
let
    Source = Table.NestedJoin(Table2,{"Contract"},Table1,{"Contract"},"Table1",JoinKind.LeftOuter),
    #"Expanded Table1" = Table.ExpandTableColumn(Source, "Table1", {"Reinsurer", "Share"}, {"Table1.Reinsurer", "Table1.Share"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Table1", "Custom", each [Amount]*[Table1.Share])
in
    #"Added Custom"