Many to Many?

s1192

New Member
Joined
Apr 21, 2019
Messages
1
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

ContractReinsurerShare
001XMunich0.3
001XSwiss0.6
001XLloyds0.1
002XLLoyds0.2
002XMuncih0.3
002XRSA0.1
002XEverest0.1
002XAIG0.2
002XAXA0.1
003XRSA0.5
003XAIG0.4
003Xlloyds0.1

<colgroup><col width="64" style="width:48pt" span="3"> </colgroup><tbody>
</tbody>


and


ContractRisk CodeAmount
001XA1000
001XB2000
001XC2500
001XD3000
001XE4000
001XF1500
003XG10
003XB1500
003XF9000
003XR10000
003XT150000
002XA150
002XB1500
002XC150000

<colgroup><col width="64" style="width:48pt" span="3"> </colgroup><tbody>
</tbody>

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,
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
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
ABCDEFGHIJKL
3ContractReinsurerShare
4001XMunich0.3
5001XSwiss0.6
6001XLloyds0.1
7002XLLoyds0.2
8002XMuncih0.3
9002XRSA0.1
10002XEverest0.1
11002XAIG0.2
12002XAXA0.1
13003XRSA0.5
14003XAIG0.4
15003Xlloyds0.1
16
17
18ContractRisk CodeAmountAIGAXAEverestLloydsMuncihMunichRSASwiss
19001XA100000010003000600
20001XB2000000200060001200
21001XC2500000250075001500
22001XD3000000300090001800
23001XE40000004000120002400
24001XF150000015004500900
25003XG1040010050
26003XB150060000150007500
27003XF90003600009000045000
28003XR1000040000010000050000
29003XT15000060000001500000750000
30002XA15030151530450150
31002XB150030015015030045001500
32002XC15000030000150001500030000450000150000
Sheet30
Cell Formulas
RangeFormula
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]
 
Upvote 0
Another Option will be to use Power Query and join the two tables on the Contract
Data Range
Data Range
A
B
C
D
E
F
1
Contract​
Risk Code​
Amount​
Table1.Reinsurer​
Table1.Share​
Custom​
2
001x​
A​
1000​
Munich​
0.3​
300​
3
001x​
A​
1000​
Swiss​
0.6​
600​
4
001x​
A​
1000​
Lloyds​
0.1​
100​
5
001x​
B​
2000​
Munich​
0.3​
600​
6
001x​
B​
2000​
Swiss​
0.6​
1200​
7
001x​
B​
2000​
Lloyds​
0.1​
200​
8
001x​
C​
2500​
Munich​
0.3​
750​
9
001x​
C​
2500​
Swiss​
0.6​
1500​
10
001x​
C​
2500​
Lloyds​
0.1​
250​
11
001x​
D​
3000​
Munich​
0.3​
900​
12
001x​
D​
3000​
Swiss​
0.6​
1800​
13
001x​
D​
3000​
Lloyds​
0.1​
300​
14
001x​
E​
4000​
Munich​
0.3​
1200​
15
001x​
E​
4000​
Swiss​
0.6​
2400​
16
001x​
E​
4000​
Lloyds​
0.1​
400​
17
001x​
F​
1500​
Munich​
0.3​
450​
18
001x​
F​
1500​
Swiss​
0.6​
900​
19
001x​
F​
1500​
Lloyds​
0.1​
150​
20
002x​
A​
150​
Lloyds​
0.2​
30​
21
002x​
A​
150​
Munich​
0.3​
45​
22
002x​
A​
150​
RSA​
0.1​
15​
23
002x​
A​
150​
Everest​
0.1​
15​
24
002x​
A​
150​
AIG​
0.2​
30​
25
002x​
A​
150​
AXA​
0.1​
15​
26
002x​
B​
1500​
Lloyds​
0.2​
300​
27
002x​
B​
1500​
Munich​
0.3​
450​
28
002x​
B​
1500​
RSA​
0.1​
150​
29
002x​
B​
1500​
Everest​
0.1​
150​
30
002x​
B​
1500​
AIG​
0.2​
300​
31
002x​
B​
1500​
AXA​
0.1​
150​
32
003x​
G​
10​
RSA​
0.5​
5​
33
003x​
G​
10​
AIG​
0.4​
4​
34
003x​
G​
10​
Lloyds​
0.1​
1​
35
003x​
B​
1500​
RSA​
0.5​
750​
36
003x​
B​
1500​
AIG​
0.4​
600​
37
003x​
B​
1500​
Lloyds​
0.1​
150​
38
003x​
F​
9000​
RSA​
0.5​
4500​
39
003x​
F​
9000​
AIG​
0.4​
3600​
40
003x​
F​
9000​
Lloyds​
0.1​
900​
41
003x​
R​
10000​
RSA​
0.5​
5000​
42
003x​
R​
10000​
AIG​
0.4​
4000​
43
003x​
R​
10000​
Lloyds​
0.1​
1000​
44
003x​
T​
150000​
RSA​
0.5​
75000​
45
003x​
T​
150000​
AIG​
0.4​
60000​
46
003x​
T​
150000​
Lloyds​
0.1​
15000​
47
002x​
C​
150000​
Lloyds​
0.2​
30000​
48
002x​
C​
150000​
Munich​
0.3​
45000​
49
002x​
C​
150000​
RSA​
0.1​
15000​
50
002x​
C​
150000​
Everest​
0.1​
15000​
51
002x​
C​
150000​
AIG​
0.2​
30000​
52
002x​
C​
150000​
AXA​
0.1​
15000​
53

Here is the Mcode

Code:
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"
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,923
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top