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.
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Peter_SSs
MrExcel MVP, Moderator
- Joined
- May 28, 2005
- Messages
- 65,372
- Office Version
- 365
- Platform
- Windows
1. Just to be sure we are clear, could we have 5-10 rows of dummy sample data and the expected results?
2. Are you looking for a manual method, formula, macro do do this job?
3. What version of Excel are you using?
2. Are you looking for a manual method, formula, macro do do this job?
3. What version of Excel are you using?
Upvote
0
[TABLE="width: 472"]
<colgroup><col><col><col><col span="2"><col><col></colgroup><tbody></tbody>[/TABLE]
[TABLE="width: 389"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]-1[/TD]
[TD]166[/TD]
[TD]ALFORD, JIM[/TD]
[TD]10[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]-1[/TD]
[TD]61[/TD]
[TD]ALFORD, RUTH[/TD]
[TD]19.5[/TD]
[TD]16[/TD]
[/TR]
[TR]
[TD]-1[/TD]
[TD]39[/TD]
[TD]ALMASI, CAROL*[/TD]
[TD]19.1[/TD]
[TD]16[/TD]
[/TR]
[TR]
[TD]-1[/TD]
[TD]181[/TD]
[TD]ALMASI, DENNIS*[/TD]
[TD]5.6[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]-1[/TD]
[TD]18[/TD]
[TD]ANDRIOLA, BETTY*[/TD]
[TD]20.2[/TD]
[TD]17[/TD]
[/TR]
[TR]
[TD]-1[/TD]
[TD]750[/TD]
[TD]BADGER, JIM[/TD]
[TD]16.7[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]-1[/TD]
[TD]749[/TD]
[TD]BADGER, LYN[/TD]
[TD]13.1[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]-1[/TD]
[TD]57[/TD]
[TD]BARNABY, BUTCH[/TD]
[TD]17.5[/TD]
[TD]16[/TD]
[/TR]
[TR]
[TD]-1[/TD]
[TD]44[/TD]
[TD]BAUER, RICHARD*[/TD]
[TD]16.8[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]-1[/TD]
[TD]66[/TD]
[TD]BEIERLE, BOB[/TD]
[TD]9.2[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]-1[/TD]
[TD]210[/TD]
[TD]BEIERLE, CAROLYN[/TD]
[TD]20.1[/TD]
[TD]17[/TD]
[/TR]
[TR]
[TD]-1[/TD]
[TD]130[/TD]
[TD]BELL, DON[/TD]
[TD]16.6[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]-1[/TD]
[TD]99[/TD]
[TD]BERGER, RHONDA[/TD]
[TD]20.2[/TD]
[TD]17[/TD]
[/TR]
[TR]
[TD]-1[/TD]
[TD]97[/TD]
[TD]BERGER, TODD[/TD]
[TD]18.2[/TD]
[TD]16[/TD]
[/TR]
[TR]
[TD]-1[/TD]
[TD]352[/TD]
[TD]BERGERON, LESLIE*[/TD]
[TD]9.4[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]-1[/TD]
[TD]353[/TD]
[TD]BERGERON, MAURICE*[/TD]
[TD]17.5[/TD]
[TD]16
[/TD]
[/TR]
</tbody>[/TABLE]
I want to sort so that just the rows with the couples that have the last names are listed in alphabetical order
with the remaining singular names like Barnaby, Bauer and Bell are added at the end.
Office for Windows 10, version 1016 I think.
Upvote
0
Peter_SSs
MrExcel MVP, Moderator
- Joined
- May 28, 2005
- Messages
- 65,372
- Office Version
- 365
- Platform
- Windows
So, have you shown the original order or the sorted order? Whatever it is, could we see the other order too please so we know what we are starting with and what we are to finish with?
You have not addressed at all Q2.
You have not addressed at all Q2.
Upvote
0
This is the original file. I would want the finished product to list all of the couples ie the two cells with people who have the same last name. I want to list these in alphabetical order and then have the single names at the end of the file.
As for question 2, what ever method is the easiest I guess. I thought there would be a formula that I could put in the top cell and then do a repeat, not sure how this can be done.
Thanks for taking the time to respond.
Upvote
0
Peter_SSs
MrExcel MVP, Moderator
- Joined
- May 28, 2005
- Messages
- 65,372
- Office Version
- 365
- Platform
- Windows
1. What would that look like for the previous sample data?
2. What do the * after some of the names mean?
3. Is the original data always already in alphabetical order like the sample given?
4. If there are two couples, or one couple and a single, that have the same last name, how do we distinguish those?
5. Your original description indicated last name and surname in "consecutive columns". The sample data looks more like names combined in one column. What is the actual layout in that regard?
Upvote
0
5. I will start with 5 and maybe that will clear some things up, sorry I meant to say consecutive rows.
4. If there are two couples or a couple and a single they should all be grouped together.
3. Yes
2. ?
1. All I really want is a way to take the file and sort it with the couples together and the singles at the end.
Upvote
0
sandy666
Banned - Rules violations
- Joined
- Oct 24, 2015
- Messages
- 7,497
is that what you want?
[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Column1[/td][td=bgcolor:#5B9BD5]Column2[/td][td=bgcolor:#5B9BD5]Column3[/td][td=bgcolor:#5B9BD5]Column4[/td][td=bgcolor:#5B9BD5]Column5[/td][td][/td][td=bgcolor:#70AD47]Column1[/td][td=bgcolor:#70AD47]Column2[/td][td=bgcolor:#70AD47]Merged[/td][td=bgcolor:#70AD47]Column4[/td][td=bgcolor:#70AD47]Column5[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
[tr=bgcolor:#FFFFFF][td]
[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
[tr=bgcolor:#FFFFFF][td]
[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
[tr=bgcolor:#FFFFFF][td]
[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
[tr=bgcolor:#FFFFFF][td]
[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
[tr=bgcolor:#FFFFFF][td]
[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
[tr=bgcolor:#FFFFFF][td]
[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
[tr=bgcolor:#FFFFFF][td]
[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
[tr=bgcolor:#FFFFFF][td]
[/table]
[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Column1[/td][td=bgcolor:#5B9BD5]Column2[/td][td=bgcolor:#5B9BD5]Column3[/td][td=bgcolor:#5B9BD5]Column4[/td][td=bgcolor:#5B9BD5]Column5[/td][td][/td][td=bgcolor:#70AD47]Column1[/td][td=bgcolor:#70AD47]Column2[/td][td=bgcolor:#70AD47]Merged[/td][td=bgcolor:#70AD47]Column4[/td][td=bgcolor:#70AD47]Column5[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
-1
[/td][td=bgcolor:#DDEBF7]166
[/td][td=bgcolor:#DDEBF7]ALFORD, JIM[/td][td=bgcolor:#DDEBF7]10
[/td][td=bgcolor:#DDEBF7]9
[/td][td][/td][td=bgcolor:#E2EFDA]-1
[/td][td=bgcolor:#E2EFDA]166
[/td][td=bgcolor:#E2EFDA]ALFORD, JIM[/td][td=bgcolor:#E2EFDA]10
[/td][td=bgcolor:#E2EFDA]9
[/td][/tr][tr=bgcolor:#FFFFFF][td]
-1
[/td][td]61
[/td][td]ALFORD, RUTH[/td][td]19.5
[/td][td]16
[/td][td][/td][td]-1
[/td][td]61
[/td][td]ALFORD, RUTH[/td][td]19.5
[/td][td]16
[/td][/tr][tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
-1
[/td][td=bgcolor:#DDEBF7]39
[/td][td=bgcolor:#DDEBF7]ALMASI, CAROL*[/td][td=bgcolor:#DDEBF7]19.1
[/td][td=bgcolor:#DDEBF7]16
[/td][td][/td][td=bgcolor:#E2EFDA]-1
[/td][td=bgcolor:#E2EFDA]39
[/td][td=bgcolor:#E2EFDA]ALMASI, CAROL*[/td][td=bgcolor:#E2EFDA]19.1
[/td][td=bgcolor:#E2EFDA]16
[/td][/tr][tr=bgcolor:#FFFFFF][td]
-1
[/td][td]181
[/td][td]ALMASI, DENNIS*[/td][td]5.6
[/td][td]5
[/td][td][/td][td]-1
[/td][td]181
[/td][td]ALMASI, DENNIS*[/td][td]5.6
[/td][td]5
[/td][/tr][tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
-1
[/td][td=bgcolor:#DDEBF7]18
[/td][td=bgcolor:#DDEBF7]ANDRIOLA, BETTY*[/td][td=bgcolor:#DDEBF7]20.2
[/td][td=bgcolor:#DDEBF7]17
[/td][td][/td][td=bgcolor:#E2EFDA]-1
[/td][td=bgcolor:#E2EFDA]750
[/td][td=bgcolor:#E2EFDA]BADGER, JIM[/td][td=bgcolor:#E2EFDA]16.7
[/td][td=bgcolor:#E2EFDA]15
[/td][/tr][tr=bgcolor:#FFFFFF][td]
-1
[/td][td]750
[/td][td]BADGER, JIM[/td][td]16.7
[/td][td]15
[/td][td][/td][td]-1
[/td][td]749
[/td][td]BADGER, LYN[/td][td]13.1
[/td][td]11
[/td][/tr][tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
-1
[/td][td=bgcolor:#DDEBF7]749
[/td][td=bgcolor:#DDEBF7]BADGER, LYN[/td][td=bgcolor:#DDEBF7]13.1
[/td][td=bgcolor:#DDEBF7]11
[/td][td][/td][td=bgcolor:#E2EFDA]-1
[/td][td=bgcolor:#E2EFDA]66
[/td][td=bgcolor:#E2EFDA]BEIERLE, BOB[/td][td=bgcolor:#E2EFDA]9.2
[/td][td=bgcolor:#E2EFDA]8
[/td][/tr][tr=bgcolor:#FFFFFF][td]
-1
[/td][td]57
[/td][td]BARNABY, BUTCH[/td][td]17.5
[/td][td]16
[/td][td][/td][td]-1
[/td][td]210
[/td][td]BEIERLE, CAROLYN[/td][td]20.1
[/td][td]17
[/td][/tr][tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
-1
[/td][td=bgcolor:#DDEBF7]44
[/td][td=bgcolor:#DDEBF7]BAUER, RICHARD*[/td][td=bgcolor:#DDEBF7]16.8
[/td][td=bgcolor:#DDEBF7]15
[/td][td][/td][td=bgcolor:#E2EFDA]-1
[/td][td=bgcolor:#E2EFDA]99
[/td][td=bgcolor:#E2EFDA]BERGER, RHONDA[/td][td=bgcolor:#E2EFDA]20.2
[/td][td=bgcolor:#E2EFDA]17
[/td][/tr][tr=bgcolor:#FFFFFF][td]
-1
[/td][td]66
[/td][td]BEIERLE, BOB[/td][td]9.2
[/td][td]8
[/td][td][/td][td]-1
[/td][td]97
[/td][td]BERGER, TODD[/td][td]18.2
[/td][td]16
[/td][/tr][tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
-1
[/td][td=bgcolor:#DDEBF7]210
[/td][td=bgcolor:#DDEBF7]BEIERLE, CAROLYN[/td][td=bgcolor:#DDEBF7]20.1
[/td][td=bgcolor:#DDEBF7]17
[/td][td][/td][td=bgcolor:#E2EFDA]-1
[/td][td=bgcolor:#E2EFDA]352
[/td][td=bgcolor:#E2EFDA]BERGERON, LESLIE*[/td][td=bgcolor:#E2EFDA]9.4
[/td][td=bgcolor:#E2EFDA]8
[/td][/tr][tr=bgcolor:#FFFFFF][td]
-1
[/td][td]130
[/td][td]BELL, DON[/td][td]16.6
[/td][td]15
[/td][td][/td][td]-1
[/td][td]353
[/td][td]BERGERON, MAURICE*[/td][td]17.5
[/td][td]16
[/td][/tr][tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
-1
[/td][td=bgcolor:#DDEBF7]99
[/td][td=bgcolor:#DDEBF7]BERGER, RHONDA[/td][td=bgcolor:#DDEBF7]20.2
[/td][td=bgcolor:#DDEBF7]17
[/td][td][/td][td=bgcolor:#E2EFDA]-1
[/td][td=bgcolor:#E2EFDA]18
[/td][td=bgcolor:#E2EFDA]ANDRIOLA, BETTY*[/td][td=bgcolor:#E2EFDA]20.2
[/td][td=bgcolor:#E2EFDA]17
[/td][/tr][tr=bgcolor:#FFFFFF][td]
-1
[/td][td]97
[/td][td]BERGER, TODD[/td][td]18.2
[/td][td]16
[/td][td][/td][td]-1
[/td][td]57
[/td][td]BARNABY, BUTCH[/td][td]17.5
[/td][td]16
[/td][/tr][tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
-1
[/td][td=bgcolor:#DDEBF7]352
[/td][td=bgcolor:#DDEBF7]BERGERON, LESLIE*[/td][td=bgcolor:#DDEBF7]9.4
[/td][td=bgcolor:#DDEBF7]8
[/td][td][/td][td=bgcolor:#E2EFDA]-1
[/td][td=bgcolor:#E2EFDA]44
[/td][td=bgcolor:#E2EFDA]BAUER, RICHARD*[/td][td=bgcolor:#E2EFDA]16.8
[/td][td=bgcolor:#E2EFDA]15
[/td][/tr][tr=bgcolor:#FFFFFF][td]
-1
[/td][td]353
[/td][td]BERGERON, MAURICE*[/td][td]17.5
[/td][td]16
[/td][td][/td][td]-1
[/td][td]130
[/td][td]BELL, DON[/td][td]16.6
[/td][td]15
[/td][/tr][/table]
Upvote
0
Peter_SSs
MrExcel MVP, Moderator
- Joined
- May 28, 2005
- Messages
- 65,372
- Office Version
- 365
- Platform
- Windows
Thanks for the various clarifications.
1. Copy the formula shown down.
2. Select all the data, including this additional column.
3. Sort the data based on the additional column
Results after performing the steps above.
You could then deleted column F if you want.
Here is a fairly simple manual method.
1. Copy the formula shown down.
2. Select all the data, including this additional column.
3. Sort the data based on the additional column
| Excel Workbook | ||||||||
|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | |||
| 1 | ||||||||
| 2 | -1 | 166 | ALFORD, JIM | 10 | 9 | 1 | ||
| 3 | -1 | 61 | ALFORD, RUTH | 19.5 | 16 | 1 | ||
| 4 | -1 | 39 | ALMASI, CAROL* | 19.1 | 16 | 1 | ||
| 5 | -1 | 181 | ALMASI, DENNIS* | 5.6 | 5 | 1 | ||
| 6 | -1 | 18 | ANDRIOLA, BETTY* | 20.2 | 17 | 2 | ||
| 7 | -1 | 750 | BADGER, JIM | 16.7 | 15 | 1 | ||
| 8 | -1 | 749 | BADGER, LYN | 13.1 | 11 | 1 | ||
| 9 | -1 | 57 | BARNABY, BUTCH | 17.5 | 16 | 2 | ||
| 10 | -1 | 44 | BAUER, RICHARD* | 16.8 | 15 | 2 | ||
| 11 | -1 | 66 | BEIERLE, BOB | 9.2 | 8 | 1 | ||
| 12 | -1 | 210 | BEIERLE, CAROLYN | 20.1 | 17 | 1 | ||
| 13 | -1 | 130 | BELL, DON | 16.6 | 15 | 2 | ||
| 14 | -1 | 99 | BERGER, RHONDA | 20.2 | 17 | 1 | ||
| 15 | -1 | 97 | BERGER, TODD | 18.2 | 16 | 1 | ||
| 16 | -1 | 352 | BERGERON, LESLIE* | 9.4 | 8 | 1 | ||
| 17 | -1 | 353 | BERGERON, MAURICE* | 17.5 | 16 | 1 | ||
Sheet2 | ||||||||
Results after performing the steps above.
You could then deleted column F if you want.
| Excel Workbook | ||||||||
|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | |||
| 1 | ||||||||
| 2 | -1 | 166 | ALFORD, JIM | 10 | 9 | 1 | ||
| 3 | -1 | 61 | ALFORD, RUTH | 19.5 | 16 | 1 | ||
| 4 | -1 | 39 | ALMASI, CAROL* | 19.1 | 16 | 1 | ||
| 5 | -1 | 181 | ALMASI, DENNIS* | 5.6 | 5 | 1 | ||
| 6 | -1 | 750 | BADGER, JIM | 16.7 | 15 | 1 | ||
| 7 | -1 | 749 | BADGER, LYN | 13.1 | 11 | 1 | ||
| 8 | -1 | 66 | BEIERLE, BOB | 9.2 | 8 | 1 | ||
| 9 | -1 | 210 | BEIERLE, CAROLYN | 20.1 | 17 | 1 | ||
| 10 | -1 | 99 | BERGER, RHONDA | 20.2 | 17 | 1 | ||
| 11 | -1 | 97 | BERGER, TODD | 18.2 | 16 | 1 | ||
| 12 | -1 | 352 | BERGERON, LESLIE* | 9.4 | 8 | 1 | ||
| 13 | -1 | 353 | BERGERON, MAURICE* | 17.5 | 16 | 1 | ||
| 14 | -1 | 18 | ANDRIOLA, BETTY* | 20.2 | 17 | 2 | ||
| 15 | -1 | 57 | BARNABY, BUTCH | 17.5 | 16 | 2 | ||
| 16 | -1 | 44 | BAUER, RICHARD* | 16.8 | 15 | 2 | ||
| 17 | -1 | 130 | BELL, DON | 16.6 | 15 | 2 | ||
Sheet2 | ||||||||
Last edited:
Upvote
0
Yes this is exactly what I want, so I just add another column and flag all the individuals that are not part of a couple and the run the formula. Thanks again, your website and you individuals are extremely helpful. Randy
Upvote
0
