Jun 23 2019 11:57 PM
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,
Jun 24 2019 12:10 AM
Jun 24 2019 02:47 AM
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
Jun 24 2019 04:11 PM - edited Jun 24 2019 04:18 PM
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,
Jun 24 2019 04:13 PM - edited Jun 24 2019 04:18 PM
Thank you for the message.
As i have never used the DynamicArrays sort before, are you able to provide an example?
Many thanks,
Jun 24 2019 04:14 PM - edited Jun 24 2019 04:20 PM
Thank you for the message.
As i have never used the DynamicArrays sort before, are you able to provide an example?
Many thanks,
Jun 24 2019 08:20 PM
SolutionJun 24 2019 09:59 PM
Looks great. Will work well for my needs.
Thanks again for your help, much appreciated.
Jun 25 2019 02:01 AM
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.
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).
Jun 25 2019 02:12 AM
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 :)
Jun 25 2019 02:57 AM
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
Apr 06 2020 11:33 AM
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:
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
Apr 07 2020 07:42 AM
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
Apr 20 2020 04:06 AM
thank you kindly for the help and assistance, very much appreciated.
This is much easier than how i managing originally.
Thanks again
Jun 24 2019 08:20 PM
Solution
Actually a Pivot Table with a TimeLine slicer will give you what you need
see attached