Populate names in spreadsheet automatically

[Rock5150]

I have the below data with names. Column A with 60names. I would like Column B and C to populate all possible combinations of names. So the first person listed in A2 would be repeated 60 times in Column B and Column C would populate the other names 2-60. THen row 62 would be person #2 in column B2 times and Column C with each member . There should be after 2 people have 122 (including header) rows total, then keep going . ( i think that's right lol)

EDIT: It should be
sorry grab name in A2 and list that name up to row 60 in column B and in column C have names A3-a61. Then starting in B62, take the name in A3 and repeat so name in A3 is then repeated 59 times and column C starts C62 with the other names to include the name in A1 and skip A2 then start again from A3; then repeat.

Net Game calculator.xlsx
	A	B	C
1	Combined	1st partner	2nd Partner
2	Alston James
3	Archuleta Michael
4	Baker Bea
5	Bolden Anthony
6	Booker Richard
7	Brister Antonio
8	Brown Art
9	Colebrook James
10	Colebrook Sean
11	Diaz Mario
12	Dubose Damitoin
13	Edmonds Jojo
14	Edmonds Scott
15	Evans Booker
16	Foster Kenneth
17	Gardner Mary
18	Garlington Ryan
19	Gary Charles
20	Graham Hosea
21	Gregory Lyndon
22	Hall Griffith
23	Harmon Jonathan
24	Hill Jerry
25	James Leroy
26	Johnson Cory
27	Kellum Willie
28	Kimbrew Raphiel
29	Ladner Ron
30	Lawrence Prem
31	Loveless Albert
32	Lowry Mark
33	Macedo Tomas
34	Mahone Monique
35	Marshall Ted
36	McDermott Michael
37	Monroe Joseph
38	Morris David
39	Mosley Cecil
40	Mosley Emma
41	Neal Eric
42	Oliver Bobby
43	Osborne Jason
44	Patterson Terry
45	Porter Derrick
46	Ragsdale Lincoln
47	Scholl Karen
48	Scholl Mark
49	Sheffield James
50	Shelton Michael
51	Simeon Ron
52	Simmons Elmer
53	Spates Ronnie
54	Stokes Al
55	Suttles Carolyn
56	Thomas Kenney
57	Tyler Shannon
58	Upton Jeff
59	Witherspoon Ezekiel
60	Wring Betty
61	Wring Neal
Handicap and Names

 

[Crystalyzer]

So, I was able to get you half way there. You will need to insert a helper column in Column A and insert a value of 59 in A2 with the formula =A2-1 in A3 and copy that down to the end of your data. That will give you how many times to repeat each name. Then copy the formula in C2 down to C1831. If you go to far it will give you an "#N/A" error.

Book12
	A	B	C	D
1	Helper Column	Combined	1st partner	2nd Partner
2	59	Alston James	Alston James	Archuleta Michael
3	58	Archuleta Michael	Alston James	Baker Bea
4	57	Baker Bea	Alston James	Bolden Anthony
5	56	Bolden Anthony	Alston James	Booker Richard
6	55	Booker Richard	Alston James	Brister Antonio
7	54	Brister Antonio	Alston James	Brown Art
8	53	Brown Art	Alston James	Colebrook James
9	52	Colebrook James	Alston James	Colebrook Sean
10	51	Colebrook Sean	Alston James	Diaz Mario
11	50	Diaz Mario	Alston James	Dubose Damitoin
12	49	Dubose Damitoin	Alston James	Edmonds Jojo
13	48	Edmonds Jojo	Alston James	Edmonds Scott
14	47	Edmonds Scott	Alston James	Evans Booker
15	46	Evans Booker	Alston James	Foster Kenneth
16	45	Foster Kenneth	Alston James	Gardner Mary
17	44	Gardner Mary	Alston James	Garlington Ryan
Sheet1

Cell Formulas
Range	Formula
C2:C17	C2	=INDEX($B$2:$B$61,MATCH(1,SIGN(COUNTIF($C$1:C1,$B$2:$B$61)<SUMIF($B$2:$B$61,$B$2:$B$61,$A$2:$A$61)),0))
D2:D17	D2	=+B3
A3:A17	A3	=+A2-1

 

[Rock5150]

I attached the data but after the first subset with "James Alston", the next name "Michael Archulata didn't care over to the last name. Did i copy wrong? Sorry i put the Number sequence as Column A since Column A is already used for another vlookup but did i think adjust the column in the formula

Net Game calculator.xlsx
	A	B	C	D	E
1	Combined	1st partner	2nd Partner	HCP	Helper
2	Alston James	Alston James	Archuleta Michael	20	59
3	Archuleta Michael	Alston James	Baker Bea	4	58
4	Baker Bea	Alston James	Bolden Anthony	22	57
5	Bolden Anthony	Alston James	Booker Richard	10	56
6	Booker Richard	Alston James	Brister Antonio	13	55
7	Brister Antonio	Alston James	Brown Art	9	54
8	Brown Art	Alston James	Colebrook James	7	53
9	Colebrook James	Alston James	Colebrook Sean	4	52
10	Colebrook Sean	Alston James	Diaz Mario	1	51
11	Diaz Mario	Alston James	Dubose Damitoin	-3	50
12	Dubose Damitoin	Alston James	Edmonds Jojo	9	49
13	Edmonds Jojo	Alston James	Edmonds Scott	25	48
14	Edmonds Scott	Alston James	Evans Booker	17	47
15	Evans Booker	Alston James	Foster Kenneth	18	46
16	Foster Kenneth	Alston James	Gardner Mary	11	45
17	Gardner Mary	Alston James	Garlington Ryan	27	44
18	Garlington Ryan	Alston James	Gary Charles	19	43
19	Gary Charles	Alston James	Graham Hosea	17	42
20	Graham Hosea	Alston James	Gregory Lyndon	13	41
21	Gregory Lyndon	Alston James	Hall Griffith	5	40
22	Hall Griffith	Alston James	Harmon Jonathan	26	39
23	Harmon Jonathan	Alston James	Hill Jerry	12	38
24	Hill Jerry	Alston James	James Leroy	6	37
25	James Leroy	Alston James	Johnson Cory	26	36
26	Johnson Cory	Alston James	Kellum Willie	13	35
27	Kellum Willie	Alston James	Kimbrew Raphiel	9	34
28	Kimbrew Raphiel	Alston James	Ladner Ron	11	33
29	Ladner Ron	Alston James	Lawrence Prem	6	32
30	Lawrence Prem	Alston James	Loveless Albert	7	31
31	Loveless Albert	Alston James	Lowry Mark	8	30
32	Lowry Mark	Alston James	Macedo Tomas	4	29
33	Macedo Tomas	Alston James	Mahone Monique	4	28
34	Mahone Monique	Alston James	Marshall Ted	24	27
35	Marshall Ted	Alston James	McDermott Michael	20	26
36	McDermott Michael	Alston James	Monroe Joseph	18	25
37	Monroe Joseph	Alston James	Morris David	5	24
38	Morris David	Alston James	Mosley Cecil	9	23
39	Mosley Cecil	Alston James	Mosley Emma	18	22
40	Mosley Emma	Alston James	Neal Eric	17	21
41	Neal Eric	Alston James	Oliver Bobby	31	20
42	Oliver Bobby	Alston James	Osborne Jason	18	19
43	Osborne Jason	Alston James	Patterson Terry	10	18
44	Patterson Terry	Alston James	Porter Derrick	30	17
45	Porter Derrick	Alston James	Ragsdale Lincoln	7	16
46	Ragsdale Lincoln	Alston James	Scholl Karen	18	15
47	Scholl Karen	Alston James	Scholl Mark	8	14
48	Scholl Mark	Alston James	Sheffield James	9	13
49	Sheffield James	Alston James	Shelton Michael	9	12
50	Shelton Michael	Alston James	Simeon Ron	13	11
51	Simeon Ron	Alston James	Simmons Elmer	9	10
52	Simmons Elmer	Alston James	Spates Ronnie	16	9
53	Spates Ronnie	Alston James	Stokes Al	22	8
54	Stokes Al	Alston James	Suttles Carolyn	10	7
55	Suttles Carolyn	Alston James	Thomas Kenney	13	6
56	Thomas Kenney	Alston James	Tyler Shannon	10	5
57	Tyler Shannon	Alston James	Upton Jeff	2	4
58	Upton Jeff	Alston James	Witherspoon Ezekiel	11	3
59	Witherspoon Ezekiel	Alston James	Wring Betty	7	2
60	Wring Betty	Alston James	Wring Neal	26	1
61	Wring Neal	Archuleta Michael	Alston James	23	0
62	Alston James	Archuleta Michael	Archuleta Michael		-1
63	Archuleta Michael	Archuleta Michael	Baker Bea		-2
64	Baker Bea	Archuleta Michael	Bolden Anthony		-3
65	Bolden Anthony	Archuleta Michael	Booker Richard		-4
66	Booker Richard	Archuleta Michael	Brister Antonio		-5
67	Brister Antonio	Archuleta Michael	Brown Art		-6
68	Brown Art	Archuleta Michael	Colebrook James		-7
69	Colebrook James	Archuleta Michael	Colebrook Sean		-8
70	Colebrook Sean	Archuleta Michael	Diaz Mario		-9
71	Diaz Mario	Archuleta Michael	Dubose Damitoin		-10
72	Dubose Damitoin	Archuleta Michael	Edmonds Jojo		-11
73	Edmonds Jojo	Archuleta Michael	Edmonds Scott		-12
74	Edmonds Scott	Archuleta Michael	Evans Booker		-13
75	Evans Booker	Archuleta Michael	Foster Kenneth		-14
76	Foster Kenneth	Archuleta Michael	Gardner Mary		-15
77	Gardner Mary	Archuleta Michael	Garlington Ryan		-16
78	Garlington Ryan	Archuleta Michael	Gary Charles		-17
79	Gary Charles	Archuleta Michael	Graham Hosea		-18
80	Graham Hosea	Archuleta Michael	Gregory Lyndon		-19
81	Gregory Lyndon	Archuleta Michael	Hall Griffith		-20
82	Hall Griffith	Archuleta Michael	Harmon Jonathan		-21
83	Harmon Jonathan	Archuleta Michael	Hill Jerry		-22
84	Hill Jerry	Archuleta Michael	James Leroy		-23
85	James Leroy	Archuleta Michael	Johnson Cory		-24
86	Johnson Cory	Archuleta Michael	Kellum Willie		-25
87	Kellum Willie	Archuleta Michael	Kimbrew Raphiel		-26
88	Kimbrew Raphiel	Archuleta Michael	Ladner Ron		-27
89	Ladner Ron	Archuleta Michael	Lawrence Prem		-28
90	Lawrence Prem	Archuleta Michael	Loveless Albert		-29
91	Loveless Albert	Archuleta Michael	Lowry Mark		-30
92	Lowry Mark	Archuleta Michael	Macedo Tomas		-31
93	Macedo Tomas	Archuleta Michael	Mahone Monique		-32
94	Mahone Monique	Archuleta Michael	Marshall Ted		-33
95	Marshall Ted	Archuleta Michael	McDermott Michael		-34
96	McDermott Michael	Archuleta Michael	Monroe Joseph		-35
97	Monroe Joseph	Archuleta Michael	Morris David		-36
98	Morris David	Archuleta Michael	Mosley Cecil		-37
99	Mosley Cecil	Archuleta Michael	Mosley Emma		-38
100	Mosley Emma	Archuleta Michael	Neal Eric		-39
101	Neal Eric	Archuleta Michael	Oliver Bobby		-40
102	Oliver Bobby	Archuleta Michael	Osborne Jason		-41
103	Osborne Jason	Archuleta Michael	Patterson Terry		-42
104	Patterson Terry	Archuleta Michael	Porter Derrick		-43
105	Porter Derrick	Archuleta Michael	Ragsdale Lincoln		-44
106	Ragsdale Lincoln	Archuleta Michael	Scholl Karen		-45
107	Scholl Karen	Archuleta Michael	Scholl Mark		-46
108	Scholl Mark	Archuleta Michael	Sheffield James		-47
109	Sheffield James	Archuleta Michael	Shelton Michael		-48
110	Shelton Michael	Archuleta Michael	Simeon Ron		-49
111	Simeon Ron	Archuleta Michael	Simmons Elmer		-50
112	Simmons Elmer	Archuleta Michael	Spates Ronnie		-51
113	Spates Ronnie	Archuleta Michael	Stokes Al		-52
114	Stokes Al	Archuleta Michael	Suttles Carolyn		-53
115	Suttles Carolyn	Archuleta Michael	Thomas Kenney		-54
116	Thomas Kenney	Archuleta Michael	Tyler Shannon		-55
117	Tyler Shannon	Archuleta Michael	Upton Jeff		-56
118	Upton Jeff	Archuleta Michael	Witherspoon Ezekiel		-57
119	Witherspoon Ezekiel	Baker Bea	Wring Betty		-58
120	Wring Betty	Baker Bea	Wring Neal		-59
121	Wring Neal	Baker Bea	Alston James		-60
Handicap and Names

Cell Formulas
Range	Formula
B2:B121	B2	=INDEX($A$2:$A$61,MATCH(1,SIGN(COUNTIF($B$1:B1,$A$2:$A$61)<SUMIF($A$2:$A$61,$A$2:$A$61,$E$2:$E$61)),0))
C2:C121	C2	=+A3
E3:E121	E3	=+E2-1

 

[Peter_SSs]

Here is a smaller example. See if it is what you want.

21 08 28.xlsm
	A	B	C
1		1st	2nd
2	Name 1	Name 1	Name 2
3	Name 2	Name 1	Name 3
4	Name 3	Name 1	Name 4
5	Name 4	Name 1	Name 5
6	Name 5	Name 2	Name 3
7		Name 2	Name 4
8		Name 2	Name 5
9		Name 3	Name 4
10		Name 3	Name 5
11		Name 4	Name 5
12
13
14
15
Combos

Cell Formulas
Range	Formula
B2:B15	B2	=LET(rng,A$2:A$6,rws,ROWS(rng),IF(ROWS(B$2:B2)>(rws-1)/2*rws,"",INDEX(rng,IFNA(MATCH(B1,rng,0)+(COUNTIF(B$1:B1,B1)=rws-MATCH(B1,rng,0)),1))))
C2:C15	C2	=IF(B2="","",INDEX(A$2:A$6,MATCH(B2,A$2:A$6,0)+COUNTIF(B$2:B2,B2)))

 

[Rock5150]

Thank you Peter. This would work as you take out duplicates pairings (when they are in B or C as long as the "net" pair is the two people). I will use this for my spreadsheet. Being a little greedy here, but is there a way "as well" to do the above so yes there may be duplicates but In column B, Name 2 would be listed "five times" in that column C would have Name 1, Name 2 (again yes a duplicate) Name 3, Name 4, and Name 5, and then Name 3 would have listed five times Name 1, Name 2, Name 3 (again.)..etc? Name 1, would have Name 1, 2, 3, 4, 5? The reason for this "bigger" duplicate set is that i would go down the list and if Name 1 says i want Name 3 and 5 on my team, the person "asking" for the partner would be in Column B. My goal is as i go down the list, i would put an "x" in column D next to teh pairing so in row 5, if Name 1 comes to me and say "hey I want Name 5", i'll put an x to the right of column C in the corresponding row.

Again the one you provided i will definitely use.

 

[Rock5150]

Peter, my desired output per above is below

Net Game calculator.xlsx
	A	B	C
1	Names	Partner A	Partner B
2	Name 1	Name 1	Name 1
3	Name 2	Name 1	Name 2
4	Name 3	Name 1	Name 3
5	Name 4	Name 1	Name 4
6	Name 5	Name 1	Name 5
7		Name 2	Name 1
8		Name 2	Name 2
9		Name 2	Name 3
10		Name 2	Name 4
11		Name 2	Name 5
12		Name 3	Name 1
13		Name 3	Name 2
14		Name 3	Name 3
15		Name 3	Name 4
16		Name 3	Name 5
17		Name 4	Name 1
18		Name 4	Name 2
19		Name 4	Name 3
20		Name 4	Name 4
21		Name 4	Name 5
22		Name 5	Name 1
23		Name 5	Name 2
24		Name 5	Name 3
25		Name 5	Name 4
26		Name 5	Name 5
Sheet2

 

[Peter_SSs]

.. is there a way "as well" ..

Sure, that is easier in that you just need a single formula in B2 and the other results will automatically spill to the required columns and rows.

21 08 28.xlsm
	A	B	C
1	Names	Partner A	Partner B
2	Name 1	Name 1	Name 1
3	Name 2	Name 1	Name 2
4	Name 3	Name 1	Name 3
5	Name 4	Name 1	Name 4
6	Name 5	Name 1	Name 5
7		Name 2	Name 1
8		Name 2	Name 2
9		Name 2	Name 3
10		Name 2	Name 4
11		Name 2	Name 5
12		Name 3	Name 1
13		Name 3	Name 2
14		Name 3	Name 3
15		Name 3	Name 4
16		Name 3	Name 5
17		Name 4	Name 1
18		Name 4	Name 2
19		Name 4	Name 3
20		Name 4	Name 4
21		Name 4	Name 5
22		Name 5	Name 1
23		Name 5	Name 2
24		Name 5	Name 3
25		Name 5	Name 4
26		Name 5	Name 5
Combos (2)

Cell Formulas
Range	Formula
B2:C26	B2	=LET(rng,A2:A6,rws,ROWS(rng),CHOOSE({1,2},INDEX(rng,SEQUENCE(rws^2,,0,1/rws)+1),INDEX(rng,MOD(SEQUENCE(rws^2)-1,rws)+1)))
Dynamic array formulas.