Building Matchmaking Spreadsheet

[hus2020]

Dear all,

We host a matchmaking event every 3 years for boys and girls who aspire to settle down 0.

Many sessions and games are run which are conducted by the councellers.

After spending some days, each participant is required to fill in a form where they select their name and 3 selection of the opposite gender,

So essentially, the dataset will be 4 columns. 1)Participant name. 2) Choice 1. 3) Choice 2. 4) Choice 3.

Each row will be the entry by each participant,

We have 24 Boys code B1-B24 and 24 Girls code G1-G24

I want some analysis to be done on this data set as follws-
1) Mark mutual hits i.e. B1choose G1 as one of the options and G1 choose B1 as one of the options. If many mutuals, the priroty is by choice order i.e Choice 1 has more weight then Choice 2.
2) Mark person who has no mutual hits. This scenario, their choice is one sided. They opted for 3 people but none of those choose them back, In this scenario the one sided selection must be analyzed against the availability of the person listed as their choice.

Need advice on building this spreadsheet using match/index/sumproduct/etc formulas and some conditional formatting in excel.

Thanks.

 

[HighAndWilder]

Dear all,

We host a matchmaking event every 3 years for boys and girls who aspire to settle down 0.

Many sessions and games are run which are conducted by the councellers.

After spending some days, each participant is required to fill in a form where they select their name and 3 selection of the opposite gender,

So essentially, the dataset will be 4 columns. 1)Participant name. 2) Choice 1. 3) Choice 2. 4) Choice 3.

Each row will be the entry by each participant,

We have 24 Boys code B1-B24 and 24 Girls code G1-G24

I want some analysis to be done on this data set as follws-
1) Mark mutual hits i.e. B1choose G1 as one of the options and G1 choose B1 as one of the options. If many mutuals, the priroty is by choice order i.e Choice 1 has more weight then Choice 2.
2) Mark person who has no mutual hits. This scenario, their choice is one sided. They opted for 3 people but none of those choose them back, In this scenario the one sided selection must be analyzed against the availability of the person listed as their choice.

Need advice on building this spreadsheet using match/index/sumproduct/etc formulas and some conditional formatting in excel.

Thanks.

Can I suggest this for starters.

Give each person an ID as duplicate names may cause a problem.

Store the names for boys and girls together.

Then you can have additional columns to carry out the analysis.

Building Matchmaking Spreadsheet.xlsm
	A	B	C	D	E	F
1	Participant ID	Gender	Name	Choice Number	Choice Number ID	Reciprocated
2	1	Female	Avery	1	25	Yes
3	1	Female	Avery	2	27
4	1	Female	Avery	3	32
5	2	Female	Mila	1	42
6	2	Female	Mila	2	1
7	2	Female	Mila	3	6
8	25	Male	Jack	1	1	Yes
9	25	Male	Jack	2	4
10	25	Male	Jack	3	6
11	32	Male	Daniel	1	12
12	32	Male	Daniel	2	17
13	32	Male	Daniel	3	6
Choices

Cell Formulas
Range	Formula
B2:B13	B2	=INDEX(Names!$A$2:$A$49,MATCH($A2,Names!$B$2:$B$49,0))
C2:C13	C2	=INDEX(Names!$C$2:$C$49,MATCH($A2,Names!$B$2:$B$49,0))
F2:F13	F2	=IF(COUNTIFS($A$2:$A$13,$E2,$E$2:$E$13,$A2)>0,"Yes","")

Building Matchmaking Spreadsheet.xlsm
	A	B	C
1	Gender	Participant ID	Name
2	Female	1	Avery
3	Female	2	Mila
4	Female	3	Aria
5	Female	4	Scarlett
6	Female	5	Penelope
7	Female	6	Layla
8	Female	7	Chloe
9	Female	8	Victoria
10	Female	9	Madison
11	Female	10	Eleanor
12	Female	11	Grace
13	Female	12	Nora
14	Female	13	Riley
15	Female	14	Zoey
16	Female	15	Hannah
17	Female	16	Hazel
18	Female	17	Lily
19	Female	18	Ellie
20	Female	19	Olivia
21	Female	20	Emma
22	Female	21	Ava
23	Female	22	Sophia
24	Female	23	Isabella
25	Female	24	May
26	Male	25	Jack
27	Male	26	Mateo
28	Male	27	Ezekiel
29	Male	28	Ian
30	Male	29	Samuel
31	Male	30	Levi
32	Male	31	James
33	Male	32	Daniel
34	Male	33	Noah
35	Male	34	Josiah
36	Male	35	Adrian
37	Male	36	Aaron
38	Male	37	Leo
39	Male	38	Ryan
40	Male	39	Wesley
41	Male	40	Liam
42	Male	41	Caleb
43	Male	42	Gabriel
44	Male	43	Charles
45	Male	44	Luke
46	Male	45	Andrew
47	Male	46	Mason
48	Male	47	Owen
49	Male	48	Cameron
Names