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.
Excel Facts
Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
sandy666
Banned - Rules violations
- Joined
- Oct 24, 2015
- Messages
- 7,497
???
here is a M code for PowerQuery (Get&Transform)
there is no formula or something like that
Code:
[SIZE=1]let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Split Column by Delimiter" = Table.SplitColumn(Source, "Column3", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Column3.1", "Column3.2"}),
#"Grouped Rows" = Table.Group(#"Split Column by Delimiter", {"Column3.1"}, {{"Count", each Table.RowCount(_), type number}, {"CAR", each _, type table}}),
#"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Count", Order.Descending}, {"Column3.1", Order.Ascending}}),
#"Expanded CAR" = Table.ExpandTableColumn(#"Sorted Rows", "CAR", {"Column1", "Column2", "Column3.1", "Column3.2", "Column4", "Column5"}, {"Column1", "Column2", "Column3.1.1", "Column3.2", "Column4", "Column5"}),
#"Merged Columns" = Table.CombineColumns(#"Expanded CAR",{"Column3.1.1", "Column3.2"},Combiner.CombineTextByDelimiter(", ", QuoteStyle.None),"Merged"),
#"Removed Columns" = Table.RemoveColumns(#"Merged Columns",{"Column3.1", "Count"})
in
#"Removed Columns"[/SIZE]
Upvote
0
Run what formula?
I thought it was you, but I got a file showing my file with an additional column where there was a number 2 for all singles and a 1 for the couples with a formula to run at the top of the additional column.
it was not you?
Upvote
0
sandy666
Banned - Rules violations
- Joined
- Oct 24, 2015
- Messages
- 7,497
here is version with "flag". 1 = single (column: count)
| Column1 | Column2 | Count | Merged | Column4 | Column5 |
-1 | 166 | 2 | ALFORD, JIM | 10 | 9 |
-1 | 61 | 2 | ALFORD, RUTH | 19.5 | 16 |
-1 | 39 | 2 | ALMASI, CAROL* | 19.1 | 16 |
-1 | 181 | 2 | ALMASI, DENNIS* | 5.6 | 5 |
-1 | 750 | 2 | BADGER, JIM | 16.7 | 15 |
-1 | 749 | 2 | BADGER, LYN | 13.1 | 11 |
-1 | 66 | 2 | BEIERLE, BOB | 9.2 | 8 |
-1 | 210 | 2 | BEIERLE, CAROLYN | 20.1 | 17 |
-1 | 99 | 2 | BERGER, RHONDA | 20.2 | 17 |
-1 | 97 | 2 | BERGER, TODD | 18.2 | 16 |
-1 | 352 | 2 | BERGERON, LESLIE* | 9.4 | 8 |
-1 | 353 | 2 | BERGERON, MAURICE* | 17.5 | 16 |
-1 | 18 | 1 | ANDRIOLA, BETTY* | 20.2 | 17 |
-1 | 57 | 1 | BARNABY, BUTCH | 17.5 | 16 |
-1 | 44 | 1 | BAUER, RICHARD* | 16.8 | 15 |
-1 | 130 | 1 | BELL, DON | 16.6 | 15 |
Code:
[SIZE=1]let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Split Column by Delimiter" = Table.SplitColumn(Source, "Column3", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Column3.1", "Column3.2"}),
#"Grouped Rows" = Table.Group(#"Split Column by Delimiter", {"Column3.1"}, {{"Count", each Table.RowCount(_), type number}, {"CAR", each _, type table}}),
#"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Count", Order.Descending}, {"Column3.1", Order.Ascending}}),
#"Expanded CAR" = Table.ExpandTableColumn(#"Sorted Rows", "CAR", {"Column1", "Column2", "Column3.1", "Column3.2", "Column4", "Column5"}, {"Column1", "Column2", "Column3.1.1", "Column3.2", "Column4", "Column5"}),
#"Merged Columns" = Table.CombineColumns(#"Expanded CAR",{"Column3.1.1", "Column3.2"},Combiner.CombineTextByDelimiter(", ", QuoteStyle.None),"Merged"),
#"Removed Columns" = Table.RemoveColumns(#"Merged Columns",{"Column3.1"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Column1", "Column2", "Count", "Merged", "Column4", "Column5"})
in
#"Reordered Columns"[/SIZE]
Upvote
0
Ok I see your code now, thank you.
Upvote
0
Peter_SSs
MrExcel MVP, Moderator
- Joined
- May 28, 2005
- Messages
- 63,880
- Office Version
- 365
- Platform
- Windows
What you quoted in post 10 was from Sandy666 so I assumed that your comments in that post were directed at him.
Post 9 was my suggestion for a manual approach. The formula in column F was to mark couples/multiples with a 1 and singles with a 2 and then manually sort the data. You said the easiest method and I thought that was easy.
Is that what you tried?
Upvote
0
ok, thanks everyone. I thought learning excel would be easier than this. Now i am having trouble sorting my file. I want to keep all the double rows together that are numbered 1 and 1, 2 and 2 with the associated names but I want to sort the file so that it comes out in ascending order of the value in column F. I tried several custom sorts that I thought should work but no luck.
Here is part of the file. low couple handicap being column F
<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>
Here is part of the file. low couple handicap being column F
| Group | Couples Name, last then first | Individual Handicap | Total Couple Handicap | Low Couple Handicap |
| 1 | ALFORD, JIM* | 9 | 25 | 9 |
| 1 | ALFORD, RUTH* | 16 | ||
| 2 | ALMASI, CAROL* | 16 | 21 | 5 |
| 2 | ALMASI, DENNIS* | 5 | ||
| 3 | BADGER, JIM* | 15 | 26 | 11 |
| 3 | BADGER, LYN* | 11 | ||
| 4 | BEIERLE, BOB* | 8 | 25 | 8 |
| 4 | BEIERLE, CAROLYN* | 17 | ||
| 5 | BERGER, RHONDA* | 17 | 33 | 16 |
| 5 | BERGER, TODD* | 16 | ||
| 6 | BERGERON, LESLIE* | 8 | 24 | 8 |
| 6 | BERGERON, MAURICE* | 16 | ||
| 7 | BOBE, DONNA* | 17 | 25 | 8 |
| 7 | BOBE, LARRY* | 8 | ||
| 8 | BOSTWICK, CAROL* | 12 | 22 | 10 |
| 8 | DAVE FULMER | 10 |
<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>
Upvote
0
Peter_SSs
MrExcel MVP, Moderator
- Joined
- May 28, 2005
- Messages
- 63,880
- Office Version
- 365
- Platform
- Windows
Sticking with a manual approach, try a formula like this in a vacant column then select all columns and sort on the new column.
| Excel Workbook | ||||||||
|---|---|---|---|---|---|---|---|---|
| B | C | D | E | F | G | |||
| 1 | Group | Couples Name, last then first | Individual Handicap | Total Couple Handicap | Low Couple Handicap | |||
| 2 | 1 | ALFORD, JIM* | 9 | 25 | 9 | 9001 | ||
| 3 | 1 | ALFORD, RUTH* | 16 | 9001 | ||||
| 4 | 2 | ALMASI, CAROL* | 16 | 21 | 5 | 5002 | ||
| 5 | 2 | ALMASI, DENNIS* | 5 | 5002 | ||||
| 6 | 3 | BADGER, JIM* | 15 | 26 | 11 | 11003 | ||
| 7 | 3 | BADGER, LYN* | 11 | 11003 | ||||
| 8 | 4 | BEIERLE, BOB* | 8 | 25 | 8 | 8004 | ||
| 9 | 4 | BEIERLE, CAROLYN* | 17 | 8004 | ||||
| 10 | 5 | BERGER, RHONDA* | 17 | 33 | 16 | 16005 | ||
| 11 | 5 | BERGER, TODD* | 16 | 16005 | ||||
| 12 | 6 | BERGERON, LESLIE* | 8 | 24 | 8 | 8006 | ||
| 13 | 6 | BERGERON, MAURICE* | 16 | 8006 | ||||
| 14 | 7 | BOBE, DONNA* | 17 | 25 | 8 | 8007 | ||
| 15 | 7 | BOBE, LARRY* | 8 | 8007 | ||||
| 16 | 8 | BOSTWICK, CAROL* | 12 | 22 | 10 | 10008 | ||
| 17 | 8 | DAVE FULMER | 10 | 10008 | ||||
Sort | ||||||||
Last edited:
Upvote
0
