SOLVED

Sort data by categories between certain dates.

Iron Contributor

Hi,

 

I have a large amount of data which involves categories and transaction. I would like to find a formula which will automatically find the relevant information between certain date periods. I would then like it to be sorted by each category. I have attached an example.

 

Can someone please assist.

 

Kind regards,

13 Replies
HI, the attachment didn't come through. Have you used Power Query at all? Sounds ideal for that sort of thing

@calof1 

As @Wyn Hopkins  said, Power Query is a great option and can be easily refreshed to include new data.

If you have Office 365 then my preference will be to Dynamic Arrays SortBy function.

Good Luck

Nabil Mourad

HI@Wyn Hopkins 

 

Thanks for the message. I have reuattached the spreadsheet, so please let me know if any issues.

 

Unfortunately i have never used power query before, but keen to learn.

 

Thank you,

Hi@nabilmourad 

 

Thank you for the message.

 

As i have never used the DynamicArrays sort before, are you able to provide an example?

 

Many thanks,

Hi@nabilmourad 

 

Thank you for the message.

 

As i have never used the DynamicArrays sort before, are you able to provide an example?

 

Many thanks,

best response confirmed by calof1 (Iron Contributor)
Solution

@calof1 

 

Actually a Pivot Table with a TimeLine slicer will give you what you need

 

see attached

Hi@Wyn Hopkins 

 

Looks great. Will work well for my needs.

 

Thanks again for your help, much appreciated.

@calof1 

Dynamic arrays have yet to be released (they are available within the Office 365 insider channel).

= SORT( FILTER(Table1, selected?, "Null"), 2 )

where selected? refers to the formula

= (Table1[Date]>=periodStart) * (Table1[Date]<=periodEnd)

 

Much as I like dynamic arrays, I suspect Power Query is better suited to your needs.

 

@Wyn Hopkins 

What are your criteria for deciding PQ or DA?  Factors that occur to me are:

Availability:

Is the end user working with Office 365? If not, eliminate DA.

Problem size:

Up to 10 000 rows favour DA, up to 100 000 rows PQ, beyond PQ to data model and Power Pivot.

Use case:

External data imported periodically; think PQ and refresh

Assumptions changed to support 'what if' studies; suggests DA (no refresh required)

The above is simply initial speculation and in no way authoritative 

 

It is somewhat strange that the first dynamic array functions actually target gaps in list processing functionality (e.g. filtering and sorting) rather than pure array functionality (e.g. aggregate by row to get a column vector).

 

 

 

@Peter Bartholomew 

 

That list sounds sensible.  Dynamic Arrays will refresh immediately whereas with Power Query you have to manually refresh so there may be significant advantage there.  But I won't start using Dynamic Arrays until it gets added to the Semi Annual channel which may not be until mid next year or later.

 

Not really stress tested DAs on big data sets so don't know about row sizes.

 

Maybe a discussion for a different spot so we don't hammer @calof1 with this :)

@calof1 

Although finding a solution to an Excel problem is the main goal of being here, however, Being new to this community, I am much enjoying the discussions back and forth....  so on a second thought, I agree with @Wyn Hopkins  and @Peter Bartholomew  that for the issues of 

Availability of the Tool

Stability & Testing

Manual Refreshing

Then, Power Query would be a better option.

Thank you

Nabil Mourad

 

@calof1 

Since sorting a column of date entries, especially very recent along with very old dates like 17th or 18th centuries produce unpredictable results, I have decided to study the problem.  It turns out that I could only resolve this problem by writing a VBA excel program.  This program provides instructions and generates a column based on the following rules and assumptions:

 

  1. It was written for the 365 Office platform and the American form of M/D/Y for dates.
  2. It assumes that the divider is “/” and includes a 4-digit year, such as 02/06/1854 or 6/5/2001.
  3. It may or may not include a time entry such as 6/5/2001 8:55:12 PM.
  4. It provides a column transformed to the form Y/M/D, that lends itself to a predictable sort.
  5. Generally, this generated column is found to be useful as a hidden column.
  6. When a sort of the visible column is requested, it is the hidden column that effects the sorting.

 

The program is as follows:

 

Sub DateAdjust()

   Dim A, B, C, D, E, F, G, H, I, J, Sel1, FromTo

   FromTo = Selection.Rows.Address(0, 0)

   If InStr(FromTo, ",") = 0 Then

      A = MsgBox("SELECT CELL IN FIRST ROW OF COLUMN OF DATES TO BE SORTED" & vbCr & vbLf & "                         AND" & vbCr & vbLf & "WHILE HOLDING CTRL BUTTON" & vbCr & vbLf & _

           vbCr & vbLf & "SELECT SECOND CELL IN SAME ROW OF AN EMPTY COLUMN", vbYesNo + vbDefaultButton1, "SORT ALL DATES (OLD & NEW)")

      If A = vbNo Then Exit Sub

   Else

      B = Left(FromTo, InStr(FromTo, ",") - 1)

      C = Right(FromTo, InStr(FromTo, ",") - 1)

      Range(B).Select

      Range(Selection, Selection.End(xlDown)).Select

      Sel1 = Selection.Rows.Address(0, 0)

      D = Selection.Rows.Count

      Selection.Copy

      Range(C).Select

      Selection.PasteSpecial Paste:=xlPasteFormulasAndNumberFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

      Application.CutCopyMode = False

      ActiveCell.Offset(0, 1).Select

      E = Selection.Address(0, 0)

      Range(E & ":" & Left(E, 1) & D + 1).Select

      Selection.NumberFormat = "@"

      For J = 1 To D

         Call Adjust

      Next J

      Range(C & ":" & Left(C, 1) & D + 1).Select

      Selection.ClearContents

      Range(E).Select

   End If

End Sub

Sub Adjust()

   Dim A, B, C, D, E, F, G, H, I, J, K, L, M, N, O, P, Q

   F = ActiveCell.Offset(0, -1)

   If Len(F) > 10 Then

      F = Date

   End If

   E = StrReverse(F)

   J = InStr(1, E, "/")

   K = Right(F, J - 1)

   L = year(F)

   If J = 3 Then G = L & "/" Else G = Right(F, 4) & "/"

   A = InStr(F, "/")

   If A < 3 Then B = "0" & Left(F, A - 1) & "/" Else B = Left(F, A - 1) & "/"

   C = InStr(A + 1, F, "/")

   If C < 5 Then D = "0" & Mid(F, C - 1, 1) Else D = Mid(F, C - 2, 2)

   H = Left(F, C)

   I = G & B & D

   ActiveCell = I

   ActiveCell.Offset(1, 0).Range("A1").Activate

End Sub

 

I have a much improved and more flexible VBA routine to replace the one I sent you yesterday:

 

Sub DateAdjust()
Dim A, B, C, D, E, F, G, H, I, J, K, Sel1, FromTo
FromTo = Selection.Rows.Address(0, 0)
If InStr(FromTo, ",") = 0 Then
A = MsgBox("SELECT CELL IN FIRST ROW OF COLUMN OF DATES TO BE SORTED" & vbCr & vbLf & " AND" & vbCr & vbLf & "WHILE HOLDING CTRL BUTTON" & vbCr & vbLf & _
vbCr & vbLf & "SELECT SECOND CELL IN SAME ROW OF AN EMPTY COLUMN", vbYesNo + vbDefaultButton1, "SORT ALL DATES (OLD & NEW)")
If A = vbNo Then Exit Sub
Else
B = Left(FromTo, InStr(FromTo, ",") - 1)
C = Right(FromTo, InStr(FromTo, ",") - 1)
Range(B).Select
F = ActiveCell.Column
F = Split(Cells(1, F).Address, "$")(1)
G = ActiveCell.Row
Range(Selection, Selection.End(xlDown)).Select
Sel1 = Selection.Rows.Address(0, 0)
D = Selection.Rows.Count
Range(C).Select
E = Selection.Address(0, 0)
H = ActiveCell.Column
H = Split(Cells(1, H).Address, "$")(1)
I = ActiveCell.Row
Range(E & ":" & H & I + D).Select
Selection.NumberFormat = "@"
ActiveCell.Select
For J = 1 To D
Range(F & G + (J - 1)).Select
Call Adjust(H & I + (J - 1))
Next J
Range(E & ":" & H & I + D).Select
Selection.HorizontalAlignment = xlCenter
ActiveCell.Select
End If
End Sub
Sub Adjust(X)
Dim A, B, C, D, E, F, G, H, I, J, K, L, M, N, O, P, Q
F = ActiveCell
If Len(F) > 10 Then
F = Date
End If
E = StrReverse(F)
J = InStr(1, E, "/")
K = Right(F, J - 1)
L = year(F)
If J = 3 Then G = L & "/" Else G = Right(F, 4) & "/"
A = InStr(F, "/")
If A < 3 Then B = "0" & Left(F, A - 1) & "/" Else B = Left(F, A - 1) & "/"
C = InStr(A + 1, F, "/")
If C < 5 Then D = "0" & Mid(F, C - 1, 1) Else D = Mid(F, C - 2, 2)
H = Left(F, C)
I = G & B & D
Range(X).Select
ActiveCell = I
End Sub

 

@calof1 

Hi@Friedemann Schaefer

 

thank you kindly for the help and assistance, very much appreciated. 

 

This is much easier than how i managing originally.

 

Thanks again

1 best response

Accepted Solutions
best response confirmed by calof1 (Iron Contributor)
Solution

@calof1 

 

Actually a Pivot Table with a TimeLine slicer will give you what you need

 

see attached

View solution in original post