CODEWITHSUNDEEP
excel-formula
user1923975
user1923975

Reputation: 1389

Sorting a list of dates with duplciates

I have a data set which is as follows:

A day and month combination can appear twice with different names.

I'd like the original data set to be unchanged, then preferably using a formula, sort this list into chronological order.

Upvotes: 0

Views: 926

Answers (1)

NiH
NiH

Reputation: 466

I assume just using a data table and sorting is not an option?

If you want to use formulas, it will involve some dummy columns and some array formulas. First of all, you would have to convert your months into numbers. Let's say you do this in column D. I will also assume your dataset has headers in row 1, so data will start in row 2 and end (for illustration purposes) in row 20.

Next, in column F will be your first dummy column. in F2, you put 1 , in F3 = F2 + 1 and copy down. All this does is number your columns.

Now we jump to column J for the months. You get a sorted list of months by using the formula = SMALL( D$2:D$20; F2 ) in K2 and copying down.

Next we'll get the day numbers in column J. What you want to do is to use the SMALL() function again, but now on a subset of column B. First we'll count the occurences of every month. We'll do that in column G, which will be our second help column. In G2, you put = IF( K2 = K1; G1 + 1; 1 ), and copy down. This will get you the number of the occurence for every month. Now, we can use this for the formula in column J to get the date numbers sorted. This will be our first array formula. = SMALL( IF( D$2:D$20 = K2; B$2:B$20; "" ); G2 ). Make sure to confirm with CTRL+Shift+Enter rather than just Enter. This tells Excel it's an array formula. This formula will get us the n-th (from G2) smallest day within the subset of days matching the month in K2. You can copy down the array formula by selecting the desired range and pressing CTRL+D.

It's a bit more complicated to get the names and we'll need two dummy columns and an array function for that. Our first dummy column will number the occurences of each specific date. We'll do this in column E by putting the formula = COUNTIFS( B$1:B2; B2; D$1:D2; D2 )in E2 and copying down. in column H we do exactly the same, but now for the output range. In H2, this becomes = COUNTIFS( J$1:J2; J2; K$1:K2; K2 ). Finally, to get the actual names, we need to lookup the first value in the original list of names matching 3 criteria:

  • Specified month
  • Specified day
  • n-th occurence of this combination

Again, we'll use an array formula in I2: = INDEX( A$2:A$20; MATCH( 1; ( J2 = B$2:B$20 ) * ( K2 = D$2:D$20 ) * ( H2 = E$2:E$20 ); 0 ) ). Confirm with CTRL+Shift+Enter and copy down with CTRL+D.

That should be it. Hope this answers your question. To make it a bit clearer, here's some pictures of what it looks like in Excel:

Dataset and dummy columns

Output columns

Sorry the pictures are not embedded. StackExchange won't let me do this because my rep is not high enough yet... This is also the reason I couldn't comment and had to post this as an answer right away, so I hope it's not completely besides the point...

Upvotes: 1

Related Questions