sorting

Lowggy · Oct 5, 2018

I have and excel file with about 200 rows. Consecutive columns have cells with last name, first name but in between those pairs of couple there are single names. I want to sort the file in alphabetical order keeping all the couples together and listing the single names at the end and of course keeping all the other columns in tact.

Fluff · Jan 21, 2019

Nobody on this thread has suggested you upload a file to dropbox that I can see.
Also as you have not supplied a link, there is no way anyone can look at it

Lowggy · Jan 21, 2019

Fluff said:
Nobody on this thread has suggested you upload a file to dropbox that I can see.
Also as you have not supplied a link, there is no way anyone can look at it

How do I supply a link to my file?

Lowggy · Jan 21, 2019

Lowggy said:
How do I supply a link to my file?

https://www.dropbox.com/s/7b8gy5e3ten60mx/latesttestof40couples -FORMREXCEL- Copy.xls?dl=0

sandy666 · Jan 21, 2019

is that what you want to achieve?

example:

Index	name	handicap
0	SOCZKA, CARLA	8
0	SOCZKA, CLARENCE	8
0	MATEER, DONNA	17
0	MATEER, DAN	10
1	REIER, BOB	16
1	REIER, PHYLISS	17
1	WALLER, MARY ANN	16
1	WALLER, ROY	13
2	LEWIS, JUDY*	8
2	LEWIS, CHARLIE*	2
2	LARSEN, PAM*	15
2	LARSEN, RON*	8
3	RICHEY, LINDA Y*	11
3	RICHEY, MORRIS*	13
3	FRALEY, KAREN	13
3	FRALEY, LONNIE	10
4	BEIERLE, CAROLYN*	17
4	BEIERLE, BOB*	8
4	BERGER, TODD	16
4	BERGER, RHONDA	16
5	BUCHHOLZ, MARIE	17
5	BUCHHOLZ, RUDY	12
5	THOMAS, LINNEA	14
5	THOMAS, JACK*	8
6	BADGER, LYN*	11
6	BADGER, JIM*	14
6	VANBIBBER, WALT	16
6	VANBIBBER, CONNIE	17
7	KLIT, ESTHER*	17
7	KLIT, EARL*	8
7	PRUNER, MERLE	9
7	PRUNER, SHIRLEY	12
8	COOPER, NORM	11
8	COOPER, JOAN JACKLIN	15
8	WOOD, LYLE	16
8	WOOD, DORTHY	17
9	WOLFE, SHARON*	17
9	WOLFE, WILLIE*	16
9	RYZSKI, HANK	12
9	RYZSKI, LOIS	17
10	STEWART, DONALD*	11
10	STEWART, KARENE*	17
10	SMILEY, NANCY*	17
10	SMILEY, JACK*	8
11	LEISSES, ****	8
11	LEISSES, JANE	17
11	YETTO, KAREN*	13
11	YETTO, WAYNE*	6
12	WILLIAMS, BEVERLY	17
12	WILLIAMS, BOB	8
12	O'CONNER, GLENDA	17
12	O'CONNER, PETE	9
13	HOLLAND, RONALD*	9
13	HOLLAND, PAT	17
13	MANN, NORMA*	17
13	MANN, EARL*	7
14	BERGERON, LESLIE	7
14	BERGERON, MAURICE	16
14	UTZ, ANN	16
14	UTZ, ED	7
15	Q'TRMAINE, MARY*	16
15	Q'TRMAINE, JERRY*	13
15	COPPO, DON*	14
15	COPPO, CHERYL*	8
16	TAMBAKIS, PAUL*	11
16	TAMBAKIS, CARMEN*	12
16	UPENIECKS, EMO	15
16	UPENIECKS, CAROL	13
17	MCNAMARA, CONNY	17
17	MCNAMARA, DAVE	16
17	RICHARDSON, PHIL*	2
17	RICHARDSON, RUTH*	4
18	SUTFIN, MURIAL	13
18	SUTFIN, LOU	10
18	MCSHANE, TOM	14
18	MCSHANE, JUDY	17
19	GASSEK, CHARLES*	5
19	GASSEK, VICKY*	11
19	EISENHART, SUE	12
19	EISENHART, CHUCK	13
20	NELSON, LOU	12
20	NELSON, BOB	11
20	WELDON, JANE*	11
20	WELDON, TIM*	10
21	HRABETS, HERB	5
21	HRABETS, MARY	17
21	KARNES, JOE*	6
21	KARNES, SHARON*	10
22	FOX, TERRI*	13
22	FOX, FRANCIS*	6
22	JACKSON, TOM	15
22	JACKSON,TAMMY	17
23	ELLIS, LINDA	17
23	ELLIS, JIM	11
23	MATTSON, KATHLEEN	15
23	MATTSON, LELAND	16
24	KOMRO, JACK	8
24	KOMRO, CAROLE	17
24	CAMPBELL, CARLOYN	14
24	CAMPBELL, RULE*	8
25	SIMMONS, JERRI*	17
25	SIMMONS, JIM*	8
25	MANNIS, LINDA*	13
25	MANIS, JOHN*	4
26	TAYLOR, JAKE	14
26	TAYLOR, SUE*	16
26	HURLEY, ANN*	17
26	HURLEY, PETER*	15
27	ALFORD, RUTH	16
27	ALFORD, JIM	11
27	FULMER, DAVE	10
27	FULMER, CAROL*	12
28	DALTON, KAY	17
28	DALTON, JACK	11
28	PINTER ,LESS	8
28	PINTER, DEBBIE	9
29	HOOVER, BILL	12
29	HOOVER, CAROL	17
29	BURK, SHARON	17
29	BURK, CHUCK	11
30	CREWS, BONNIE	17
30	CREWS, MORRIS	9
30	LAMERS, JOHN	8
30	LAMERS, MARY	17
31	FULTON, TERRY	9
31	FULTON, JOANNE	16
31	MOHLER, BOB	7
31	MOHLER, BEV	17
32	GAFFNEY, MARGIE	16
32	GAFFNEY, FRED	8
32	SENNETT, CHUCK	3
32	SENNETT, ESTHER	14
33	GAUSE, SHIRLEY	17
33	GAUSE, JIM	7
33	KALKBRENNER, LINDA*	17
33	KALKBRENNER, LENNY*	9
34	WRIGHT, SANDRA	17
34	WRIGHT, PHILLIP	13
34	HAWK, SUSIE	17
34	HAWK, JIM	7
35	OSTERHOUT, JIM*	9
35	OSTERHOUT, MARY ALICE*	17
35	CLOUD, ****	10
35	CLOUD, GLORIA	17
36	EICH, LEROY	16
36	EICH, LINDA	17
36	ROUSH, MIC	13
36	ROUSH, JANE	17
37	ALMASI, CAROL*	16
37	ALMASI, DENNIS*	6
37	MILLER, DAN	16
37	MILLER, LARAINE	17
38	VANDENBERG, RAY	13
38	VANDERBURG, BETTY	17
38	LOGAN, RANDY*	8
38	LOGAN, SHARON*	10
39	GOODHUE, MARTHA	14
39	GOODHUE, JIM	13
39	REID, ERNIE	9
39	REID, MARLENE*	17

Lowggy · Jan 21, 2019

sandy666 said:
is that what you want to achieve?

example:

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Index[/COLOR] [COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]name[/COLOR] [COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]handicap[/COLOR]
0
SOCZKA, CARLA
8
0
SOCZKA, CLARENCE
8
0
MATEER, DONNA
17
0
MATEER, DAN
10
1
REIER, BOB
16
1
REIER, PHYLISS
17
1
WALLER, MARY ANN
16
1
WALLER, ROY
13
2
LEWIS, JUDY*
8
2
LEWIS, CHARLIE*
2
2
LARSEN, PAM*
15
2
LARSEN, RON*
8
3
RICHEY, LINDA Y*
11
3
RICHEY, MORRIS*
13
3
FRALEY, KAREN
13
3
FRALEY, LONNIE
10
4
BEIERLE, CAROLYN*
17
4
BEIERLE, BOB*
8
4
BERGER, TODD
16
4
BERGER, RHONDA
16
5
BUCHHOLZ, MARIE
17
5
BUCHHOLZ, RUDY
12
5
THOMAS, LINNEA
14
5
THOMAS, JACK*
8
6
BADGER, LYN*
11
6
BADGER, JIM*
14
6
VANBIBBER, WALT
16
6
VANBIBBER, CONNIE
17
7
KLIT, ESTHER*
17
7
KLIT, EARL*
8
7
PRUNER, MERLE
9
7
PRUNER, SHIRLEY
12
8
COOPER, NORM
11
8
COOPER, JOAN JACKLIN
15
8
WOOD, LYLE
16
8
WOOD, DORTHY
17
9
WOLFE, SHARON*
17
9
WOLFE, WILLIE*
16
9
RYZSKI, HANK
12
9
RYZSKI, LOIS
17
10
STEWART, DONALD*
11
10
STEWART, KARENE*
17
10
SMILEY, NANCY*
17
10
SMILEY, JACK*
8
11
LEISSES, ****
8
11
LEISSES, JANE
17
11
YETTO, KAREN*
13
11
YETTO, WAYNE*
6
12
WILLIAMS, BEVERLY
17
12
WILLIAMS, BOB
8
12
O'CONNER, GLENDA
17
12
O'CONNER, PETE
9
13
HOLLAND, RONALD*
9
13
HOLLAND, PAT
17
13
MANN, NORMA*
17
13
MANN, EARL*
7
14
BERGERON, LESLIE
7
14
BERGERON, MAURICE
16
14
UTZ, ANN
16
14
UTZ, ED
7
15
Q'TRMAINE, MARY*
16
15
Q'TRMAINE, JERRY*
13
15
COPPO, DON*
14
15
COPPO, CHERYL*
8
16
TAMBAKIS, PAUL*
11
16
TAMBAKIS, CARMEN*
12
16
UPENIECKS, EMO
15
16
UPENIECKS, CAROL
13
17
MCNAMARA, CONNY
17
17
MCNAMARA, DAVE
16
17
RICHARDSON, PHIL*
2
17
RICHARDSON, RUTH*
4
18
SUTFIN, MURIAL
13
18
SUTFIN, LOU
10
18
MCSHANE, TOM
14
18
MCSHANE, JUDY
17
19
GASSEK, CHARLES*
5
19
GASSEK, VICKY*
11
19
EISENHART, SUE
12
19
EISENHART, CHUCK
13
20
NELSON, LOU
12
20
NELSON, BOB
11
20
WELDON, JANE*
11
20
WELDON, TIM*
10
21
HRABETS, HERB
5
21
HRABETS, MARY
17
21
KARNES, JOE*
6
21
KARNES, SHARON*
10
22
FOX, TERRI*
13
22
FOX, FRANCIS*
6
22
JACKSON, TOM
15
22
JACKSON,TAMMY
17
23
ELLIS, LINDA
17
23
ELLIS, JIM
11
23
MATTSON, KATHLEEN
15
23
MATTSON, LELAND
16
24
KOMRO, JACK
8
24
KOMRO, CAROLE
17
24
CAMPBELL, CARLOYN
14
24
CAMPBELL, RULE*
8
25
SIMMONS, JERRI*
17
25
SIMMONS, JIM*
8
25
MANNIS, LINDA*
13
25
MANIS, JOHN*
4
26
TAYLOR, JAKE
14
26
TAYLOR, SUE*
16
26
HURLEY, ANN*
17
26
HURLEY, PETER*
15
27
ALFORD, RUTH
16
27
ALFORD, JIM
11
27
FULMER, DAVE
10
27
FULMER, CAROL*
12
28
DALTON, KAY
17
28
DALTON, JACK
11
28
PINTER ,LESS
8
28
PINTER, DEBBIE
9
29
HOOVER, BILL
12
29
HOOVER, CAROL
17
29
BURK, SHARON
17
29
BURK, CHUCK
11
30
CREWS, BONNIE
17
30
CREWS, MORRIS
9
30
LAMERS, JOHN
8
30
LAMERS, MARY
17
31
FULTON, TERRY
9
31
FULTON, JOANNE
16
31
MOHLER, BOB
7
31
MOHLER, BEV
17
32
GAFFNEY, MARGIE
16
32
GAFFNEY, FRED
8
32
SENNETT, CHUCK
3
32
SENNETT, ESTHER
14
33
GAUSE, SHIRLEY
17
33
GAUSE, JIM
7
33
KALKBRENNER, LINDA*
17
33
KALKBRENNER, LENNY*
9
34
WRIGHT, SANDRA
17
34
WRIGHT, PHILLIP
13
34
HAWK, SUSIE
17
34
HAWK, JIM
7
35
OSTERHOUT, JIM*
9
35
OSTERHOUT, MARY ALICE*
17
35
CLOUD, ****
10
35
CLOUD, GLORIA
17
36
EICH, LEROY
16
36
EICH, LINDA
17
36
ROUSH, MIC
13
36
ROUSH, JANE
17
37
ALMASI, CAROL*
16
37
ALMASI, DENNIS*
6
37
MILLER, DAN
16
37
MILLER, LARAINE
17
38
VANDENBERG, RAY
13
38
VANDERBURG, BETTY
17
38
LOGAN, RANDY*
8
38
LOGAN, SHARON*
10
39
GOODHUE, MARTHA
14
39
GOODHUE, JIM
13
39
REID, ERNIE
9
39
REID, MARLENE*
17

<tbody>
</tbody>

Not quite, I wanted the total handicap of all 4somes to be as close as possible. It's great that the teams keep the spouses together but like I said the total of the handicaps has to be close. Thanks so much for your effort, if you could make it work so they are close in total that would be awesome.

sandy666 · Jan 21, 2019

any example for 5 teams?

edit:
and don't quote whole post, please! use just Reply not Reply With Quote

Lowggy · Jan 21, 2019

Not exactly sure what you mean but from doing it by hand I match the teams so that their total handicaps comes to about 50. Of course there are a few couples that are very low ie the Richardsons are a 2 and a 4 so they can only be matched with the highest pair who are a 16 and a 17. There are a few others like that as well ie Lewis, Soczka.
most of the others if equally matched will be around 50. I hope this makes sense. It would be great if each time you hit the push me it would do a different match. I know I am asking a lot, not even sure it is doable.
Thanks, Randy

sandy666 · Jan 21, 2019

something like this?

Index	name	handicap	Tot
0	MANIS, JOHN*	4	50
0	MANNIS, LINDA*	13	50
0	MILLER, LARAINE	17	50
0	MILLER, DAN	16	50
1	WILLIAMS, BOB	8	47
1	WILLIAMS, BEVERLY	17	47
1	COPPO, DON*	14	47
1	COPPO, CHERYL*	8	47
2	KLIT, EARL*	8	56
2	KLIT, ESTHER*	17	56
2	MATTSON, LELAND	16	56
2	MATTSON, KATHLEEN	15	56
3	WOOD, LYLE	16	55
3	WOOD, DORTHY	17	55
3	HRABETS, MARY	17	55
3	HRABETS, HERB	5	55
4	DALTON, JACK	11	56
4	DALTON, KAY	17	56
4	UPENIECKS, CAROL	13	56
4	UPENIECKS, EMO	15	56
5	HOLLAND, PAT	17	59
5	HOLLAND, RONALD*	9	59
5	VANBIBBER, WALT	16	59
5	VANBIBBER, CONNIE	17	59

I'm afraid you'll need vba to do this if Tot should be more close to 50 or even =50

Lowggy · Jan 21, 2019

There is existing vba code in the file. When run it makes teams that are very close to 50 but it does not keep the spouses together. Can the code be altered to do that? You have looked at the code, right?

sandy666 · Jan 21, 2019

sorry mate but I'm not interested in vba in any form so you'll need to wait for someone else

have a nice day

sorting

Lowggy

New Member

Fluff

MrExcel MVP, Moderator

Excel Facts

Lowggy

New Member

Lowggy

New Member

sandy666

Banned - Rules violations

Lowggy

New Member

sandy666

Banned - Rules violations

Lowggy

New Member

sandy666

Banned - Rules violations

Lowggy

New Member

sandy666

Banned - Rules violations

Similar threads

Forum statistics

Share this page

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Index[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]name[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]handicap[/COLOR]
0	SOCZKA, CARLA	8
0	SOCZKA, CLARENCE	8
0	MATEER, DONNA	17
0	MATEER, DAN	10
1	REIER, BOB	16
1	REIER, PHYLISS	17
1	WALLER, MARY ANN	16
1	WALLER, ROY	13
2	LEWIS, JUDY*	8
2	LEWIS, CHARLIE*	2
2	LARSEN, PAM*	15
2	LARSEN, RON*	8
3	RICHEY, LINDA Y*	11
3	RICHEY, MORRIS*	13
3	FRALEY, KAREN	13
3	FRALEY, LONNIE	10
4	BEIERLE, CAROLYN*	17
4	BEIERLE, BOB*	8
4	BERGER, TODD	16
4	BERGER, RHONDA	16
5	BUCHHOLZ, MARIE	17
5	BUCHHOLZ, RUDY	12
5	THOMAS, LINNEA	14
5	THOMAS, JACK*	8
6	BADGER, LYN*	11
6	BADGER, JIM*	14
6	VANBIBBER, WALT	16
6	VANBIBBER, CONNIE	17
7	KLIT, ESTHER*	17
7	KLIT, EARL*	8
7	PRUNER, MERLE	9
7	PRUNER, SHIRLEY	12
8	COOPER, NORM	11
8	COOPER, JOAN JACKLIN	15
8	WOOD, LYLE	16
8	WOOD, DORTHY	17
9	WOLFE, SHARON*	17
9	WOLFE, WILLIE*	16
9	RYZSKI, HANK	12
9	RYZSKI, LOIS	17
10	STEWART, DONALD*	11
10	STEWART, KARENE*	17
10	SMILEY, NANCY*	17
10	SMILEY, JACK*	8
11	LEISSES, ****	8
11	LEISSES, JANE	17
11	YETTO, KAREN*	13
11	YETTO, WAYNE*	6
12	WILLIAMS, BEVERLY	17
12	WILLIAMS, BOB	8
12	O'CONNER, GLENDA	17
12	O'CONNER, PETE	9
13	HOLLAND, RONALD*	9
13	HOLLAND, PAT	17
13	MANN, NORMA*	17
13	MANN, EARL*	7
14	BERGERON, LESLIE	7
14	BERGERON, MAURICE	16
14	UTZ, ANN	16
14	UTZ, ED	7
15	Q'TRMAINE, MARY*	16
15	Q'TRMAINE, JERRY*	13
15	COPPO, DON*	14
15	COPPO, CHERYL*	8
16	TAMBAKIS, PAUL*	11
16	TAMBAKIS, CARMEN*	12
16	UPENIECKS, EMO	15
16	UPENIECKS, CAROL	13
17	MCNAMARA, CONNY	17
17	MCNAMARA, DAVE	16
17	RICHARDSON, PHIL*	2
17	RICHARDSON, RUTH*	4
18	SUTFIN, MURIAL	13
18	SUTFIN, LOU	10
18	MCSHANE, TOM	14
18	MCSHANE, JUDY	17
19	GASSEK, CHARLES*	5
19	GASSEK, VICKY*	11
19	EISENHART, SUE	12
19	EISENHART, CHUCK	13
20	NELSON, LOU	12
20	NELSON, BOB	11
20	WELDON, JANE*	11
20	WELDON, TIM*	10
21	HRABETS, HERB	5
21	HRABETS, MARY	17
21	KARNES, JOE*	6
21	KARNES, SHARON*	10
22	FOX, TERRI*	13
22	FOX, FRANCIS*	6
22	JACKSON, TOM	15
22	JACKSON,TAMMY	17
23	ELLIS, LINDA	17
23	ELLIS, JIM	11
23	MATTSON, KATHLEEN	15
23	MATTSON, LELAND	16
24	KOMRO, JACK	8
24	KOMRO, CAROLE	17
24	CAMPBELL, CARLOYN	14
24	CAMPBELL, RULE*	8
25	SIMMONS, JERRI*	17
25	SIMMONS, JIM*	8
25	MANNIS, LINDA*	13
25	MANIS, JOHN*	4
26	TAYLOR, JAKE	14
26	TAYLOR, SUE*	16
26	HURLEY, ANN*	17
26	HURLEY, PETER*	15
27	ALFORD, RUTH	16
27	ALFORD, JIM	11
27	FULMER, DAVE	10
27	FULMER, CAROL*	12
28	DALTON, KAY	17
28	DALTON, JACK	11
28	PINTER ,LESS	8
28	PINTER, DEBBIE	9
29	HOOVER, BILL	12
29	HOOVER, CAROL	17
29	BURK, SHARON	17
29	BURK, CHUCK	11
30	CREWS, BONNIE	17
30	CREWS, MORRIS	9
30	LAMERS, JOHN	8
30	LAMERS, MARY	17
31	FULTON, TERRY	9
31	FULTON, JOANNE	16
31	MOHLER, BOB	7
31	MOHLER, BEV	17
32	GAFFNEY, MARGIE	16
32	GAFFNEY, FRED	8
32	SENNETT, CHUCK	3
32	SENNETT, ESTHER	14
33	GAUSE, SHIRLEY	17
33	GAUSE, JIM	7
33	KALKBRENNER, LINDA*	17
33	KALKBRENNER, LENNY*	9
34	WRIGHT, SANDRA	17
34	WRIGHT, PHILLIP	13
34	HAWK, SUSIE	17
34	HAWK, JIM	7
35	OSTERHOUT, JIM*	9
35	OSTERHOUT, MARY ALICE*	17
35	CLOUD, ****	10
35	CLOUD, GLORIA	17
36	EICH, LEROY	16
36	EICH, LINDA	17
36	ROUSH, MIC	13
36	ROUSH, JANE	17
37	ALMASI, CAROL*	16
37	ALMASI, DENNIS*	6
37	MILLER, DAN	16
37	MILLER, LARAINE	17
38	VANDENBERG, RAY	13
38	VANDERBURG, BETTY	17
38	LOGAN, RANDY*	8
38	LOGAN, SHARON*	10
39	GOODHUE, MARTHA	14
39	GOODHUE, JIM	13
39	REID, ERNIE	9
39	REID, MARLENE*	17

sorting

New Member

MrExcel MVP, Moderator

Excel Facts

New Member

New Member

Banned - Rules violations

New Member

Banned - Rules violations

New Member

Banned - Rules violations

New Member

Banned - Rules violations

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock