# First time creating macros for complicated sorting

#### AyEmmKay

##### New Member
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!

##### New Member
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()
End Sub

The result is as desired:

#### AyEmmKay

##### New Member
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!

##### New Member
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
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
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
Err. Ascending. I swear I'm smarter than I sound at times.

#### AyEmmKay

##### New Member
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
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:

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: