First time creating macros for complicated sorting

AyEmmKay

New Member
Joined
Sep 26, 2019
Messages
6
Hey, all! Just a bit of background:

I work in sales at a hotel. I've been tasked with manually entering our numbers into a new system until it interfaces with the program the front desk uses to keep track of rooms. It's daunting. I'm given an extremely dense Excel file with information about the groups that stay at the hotel, the dates they stay, and the number of rooms they use per night. I'm trying to figure out how to do a combination of between and within group sorting. I feel like a visual, extremely simplified, example is best:

https://imgur.com/rNNQu1o

The first set is the original data, completely randomized just for example's sake. The second set is sorted E then D, so it'd be like Occupancy Date then Group Name. This results in the Occupancy Date being in order, but the Group Names are not grouped. The third set is sorted G then H, so it'd be like Group Name then Occupancy Date. This results in the Group Names being grouped together, but it's not in chronological order. The fourth set is how I'd like it to come out. The Group Names are grouped together, they progress from one grouping to the next based on initial Occupancy Date, and they progress within the groupings in chronological order.

I don't even know where to start, to be completely honest. I've never had to create macros before, though I get the gist. I could easily do simple things to rid the sheet of extra data that I don't need. I have no idea how to touch the grouping/sorting, though.

I'm still trying small things to get it to work like I'd want, but I figured macros would be the way to go.

Although I'm definitely asking for help, I also want to learn and understand. I get the feeling Excel is going to be my bff for a while so I want to learn how to use it properly.

Thanks a billion times over!

 

brownbread

New Member
Joined
Jun 21, 2010
Messages
20
Hi there, here's one method... I've entered an extra column into your data, called Anchor (for want of a better name), it's in grey in column B below:



The formula in column B is =IF(A2<>A1,C2,B1)
The formula is dragged down through all rows. It's comparing the row's Occupancy Date with that of the row above, and if it's different then it grabs the Group Name from its own row; otherwise it copies the Anchor data from the row above. The result doesn't mean anything, until the sorting starts...

The following macro uses three simple sort commands, one after the other. We sort using column C, then A, then B:

Sub sorter()
Columns("A:C").Sort key1:=Range("C1"), order1:=xlAscending, Header:=xlYes
Columns("A:C").Sort key1:=Range("A1"), order1:=xlAscending, Header:=xlYes
Columns("A:C").Sort key1:=Range("B1"), order1:=xlAscending, Header:=xlYes
End Sub


The result is as desired:

 

AyEmmKay

New Member
Joined
Sep 26, 2019
Messages
6
That makes a LOT of sense. I'm super new to this so I had no idea how to even approach it logically. An anchor column never occurred to me.

I'm going to give this a shot with my data as the example was extremely simplified. Not that I doubt you, but I'll let you know when/if it works.

Sincerely, thank you. You've likely made my job SO MUCH faster and easier. I'd give you a cookie if I could! :)
 

brownbread

New Member
Joined
Jun 21, 2010
Messages
20
Ace, glad I could help. Let me know if you run into problems and we can try work it out together. Instead of using Columns("A:C"), you could absolutely use an explicit range, such as Range("A1:Z1000")... or you could have the code figure out the range dynamically, based on your own rules.

Also you could potenially achieve the 'anchor' solution via code instead of the extra column. But it could require significant extra code. In fact, if you did need to do that, it'd probably be easiest to have the code automatically generate the anchor column; use it; then delete it again.

What I love about having multiple sorting steps is the fact you have to run the steps in reverse to get to your destination. I love the logic - it feels weird and then magic when you get the result.
 
Last edited:

AyEmmKay

New Member
Joined
Sep 26, 2019
Messages
6
The word "magic" definitely crossed my mind. I had to finish up some tasks at work and fly out to take care of some stuff, so I didn't get the chance to try it out. It'll be one of the first things I do tomorrow morning, though!

If you're willing, could you walk me through your thought process step by step? I think I get it, but I'm just not used to the language. I haven't used logic like that since high school. Shame, shame, I know.
 

AyEmmKay

New Member
Joined
Sep 26, 2019
Messages
6
Oh, crud. I just noticed something and I'm so sorry. I guess I'm just missing things because I've been thinking about it and twisting my mind in knots about it. I also need the Occupancy Date to end up in chronological order as well. I guess in the case of the example you used up there, it would be alphabetical. So I guess it's that I need column A in order descending, and then the values in column C descending within the groupings created by column A? Again, I'm sorry. I just don't know the language to use to communicate it.
 

AyEmmKay

New Member
Joined
Sep 26, 2019
Messages
6
Err. Ascending. I swear I'm smarter than I sound at times.
 

AyEmmKay

New Member
Joined
Sep 26, 2019
Messages
6
OH WAIT NO YOU'RE AN ANGEL! It's perfect! I was getting confused because I was looking at the group names as the letters and the dates as the numbers. You switched them and that messed me all up because I'm silly sometimes. BUT I GET IT NOW! I am forever indebted to you!
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,666
Office Version
365
Platform
Windows
Welcome to the MrExcel board!

If I have understood correctly, I think you can do this with a helper column & just a single sort.
I am assuming that you do actually have dates instead of single-digit numbers & probably actual names not single letters. If so, remember that it is best not to over-simplify sample data. It is also better for you (that is more helpers & faster if you provide any sample data in a form that can be copied and pasted into a workbook to test with (like mine below). My signature block below has help with that. Unfortunately we cannot copy fro images like you (or brownbread) provided.

If my assumptions are correct then try this formula in C2, copied down then select all 3 columns and sort on the helper column. The 20 values in the formula just need to be anything bigger than the longest name likely to occur in column A.

Before sorting:

<b>Excel 2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Group</td><td style="text-align: right;;">Date</td><td style=";">Sort</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">xyz</td><td style="text-align: right;;">13/09/2019</td><td style=";">xyz                 43721</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">bbb</td><td style="text-align: right;;">14/09/2019</td><td style=";">bbb                 43722</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">kkkkk</td><td style="text-align: right;;">15/09/2019</td><td style=";">kkkkk               43723</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">kkkkk</td><td style="text-align: right;;">14/09/2019</td><td style=";">kkkkk               43722</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">xyz</td><td style="text-align: right;;">17/09/2019</td><td style=";">xyz                 43725</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">abcde</td><td style="text-align: right;;">18/09/2019</td><td style=";">abcde               43726</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">kkkkk</td><td style="text-align: right;;">13/09/2019</td><td style=";">kkkkk               43721</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">xyz</td><td style="text-align: right;;">14/09/2019</td><td style=";">xyz                 43722</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">x</td><td style="text-align: right;;">15/09/2019</td><td style=";">x                   43723</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";">abcde</td><td style="text-align: right;;">14/09/2019</td><td style=";">abcde               43722</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";">kkkkk</td><td style="text-align: right;;">17/09/2019</td><td style=";">kkkkk               43725</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style=";">a</td><td style="text-align: right;;">13/09/2019</td><td style=";">a                   43721</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style=";">x</td><td style="text-align: right;;">17/09/2019</td><td style=";">x                   43725</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style=";">x</td><td style="text-align: right;;">14/09/2019</td><td style=";">x                   43722</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style=";">abcde</td><td style="text-align: right;;">17/09/2019</td><td style=";">abcde               43725</td></tr></tbody></table><p style="width:3.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sort</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C2</th><td style="text-align:left">=LEFT(<font color="Blue">A2&REPT(<font color="Red">" ",20</font>),20</font>)&B2</td></tr></tbody></table></td></tr></table><br />


After sorting:

<b>Excel 2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Group</td><td style="text-align: right;;">Date</td><td style=";">Sort</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">a</td><td style="text-align: right;;">13/09/2019</td><td style=";">a                   43721</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">abcde</td><td style="text-align: right;;">14/09/2019</td><td style=";">abcde               43722</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">abcde</td><td style="text-align: right;;">17/09/2019</td><td style=";">abcde               43725</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">abcde</td><td style="text-align: right;;">18/09/2019</td><td style=";">abcde               43726</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">bbb</td><td style="text-align: right;;">14/09/2019</td><td style=";">bbb                 43722</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">kkkkk</td><td style="text-align: right;;">13/09/2019</td><td style=";">kkkkk               43721</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">kkkkk</td><td style="text-align: right;;">14/09/2019</td><td style=";">kkkkk               43722</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">kkkkk</td><td style="text-align: right;;">15/09/2019</td><td style=";">kkkkk               43723</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">kkkkk</td><td style="text-align: right;;">17/09/2019</td><td style=";">kkkkk               43725</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";">x</td><td style="text-align: right;;">14/09/2019</td><td style=";">x                   43722</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";">x</td><td style="text-align: right;;">15/09/2019</td><td style=";">x                   43723</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style=";">x</td><td style="text-align: right;;">17/09/2019</td><td style=";">x                   43725</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style=";">xyz</td><td style="text-align: right;;">13/09/2019</td><td style=";">xyz                 43721</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style=";">xyz</td><td style="text-align: right;;">14/09/2019</td><td style=";">xyz                 43722</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style=";">xyz</td><td style="text-align: right;;">17/09/2019</td><td style=";">xyz                 43725</td></tr></tbody></table><p style="width:3.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sort</p><br /><br />
 
Last edited:

Forum statistics

Threads
1,085,513
Messages
5,384,107
Members
401,881
Latest member
Dato

Some videos you may like

This Week's Hot Topics

Top