Modify VBA code to sort by date ignoring words, please :)

D

dakotacondon1

New Member
Joined
Oct 25, 2023
Messages
9
Office Version
  1. 365
Platform
  1. Windows
Hi! I have data that looks like this:

Axon Body 3 Video 2021-11-25 2229 X60A986468J
Axon Body 3 Video 2021-11-24 2231 X60A656565V
Axon Fleet 3 Interior Camera Video 2022-02-21 1920 X60A656565V
Axon Body 3 Video 2021-08-26 2158 X60A656565V
Axon Fleet 3 Front Camera Video 2021-11-27 2156 X60A986468J
Axon Body 3 Video 2021-12-01 2154 X60A656565V
(These are not real, I made them up.)

Ultimately, I need these titles to be ordered chronologically and bracketed with a space in the middle in one cell so they can be pasted into a report. The end result should look like this:

[Axon Body 3 Video 2021-08-26 2158 X60A656565V] [Axon Body 3 Video 2021-11-24 2231 X60A656565V] [Axon Body 3 Video 2021-11-25 2229 X60A986468J] [Axon Fleet 3 Front Camera Video 2021-11-27 2156 X60A986468J] [Axon Body 3 Video 2021-12-01 2154 X60A656565V] [Axon Fleet 3 Interior Camera Video 2022-02-21 1920 X60A656565V]

I do not understand VBA code. I hope to one day but right now I am just an okay Google-er. Currently, I have it miraculously set up with VBA code I found on the internet so when you paste the data into Column B, it automatically orders it. The problem is that it is ordering it alphabetically so it obviously doesn't work when there are any differences in the beginnings of the titles. I need these to be ordered by the date and then the time (military time, the four numbers after the date) no matter the title. And then they will work with the rudimentary formulas I made to make them formatted correctly.

Right now I have this in a Worksheet - Change situation:

VBA Code: 
Sub Worksheet_Change(ByVal Target As Range)

Dim lastrow As Long
lastrow = Cells(Rows.Count, 2).End(xlUp).Row

    If Not Intersect(Target, Range("B:B")) Is Nothing Then
        Range("B1:B" & lastrow).Sort key1:=Range("B1:B" & lastrow), _
        order1:=xlAscending, Header:=xlYes

    
    End If
    End Sub

Then Column B adds brackets [=IF(B2<>"", "["&B2&"]", " "] and then Column F puts them all together in one cell and adds spaces [=D2&" "&D3&" "&D4&" "&D5&" "&D6&" "&D7&" "&D8.....etc]

I'm not sure if I am totally making sense to people who actually know how this works. I have attached a screenshot of the sheet before pasting and one after pasting the above values in it.

Please be nice I have no idea what I am doing :) and THANK YOU
 

Attachments

  • before pasting.png
    before pasting.png
    6.7 KB · Views: 11
  • after pasting.png
    after pasting.png
    17.7 KB · Views: 10

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Sort by date Sort by votes
dakotacondon1 said:
Hi! I have data that looks like this:

Axon Body 3 Video 2021-11-25 2229 X60A986468J
Axon Body 3 Video 2021-11-24 2231 X60A656565V
Axon Fleet 3 Interior Camera Video 2022-02-21 1920 X60A656565V
Axon Body 3 Video 2021-08-26 2158 X60A656565V
Axon Fleet 3 Front Camera Video 2021-11-27 2156 X60A986468J
Axon Body 3 Video 2021-12-01 2154 X60A656565V
(These are not real, I made them up.)

Ultimately, I need these titles to be ordered chronologically and bracketed with a space in the middle in one cell so they can be pasted into a report. The end result should look like this:

[Axon Body 3 Video 2021-08-26 2158 X60A656565V] [Axon Body 3 Video 2021-11-24 2231 X60A656565V] [Axon Body 3 Video 2021-11-25 2229 X60A986468J] [Axon Fleet 3 Front Camera Video 2021-11-27 2156 X60A986468J] [Axon Body 3 Video 2021-12-01 2154 X60A656565V] [Axon Fleet 3 Interior Camera Video 2022-02-21 1920 X60A656565V]

I do not understand VBA code. I hope to one day but right now I am just an okay Google-er. Currently, I have it miraculously set up with VBA code I found on the internet so when you paste the data into Column B, it automatically orders it. The problem is that it is ordering it alphabetically so it obviously doesn't work when there are any differences in the beginnings of the titles. I need these to be ordered by the date and then the time (military time, the four numbers after the date) no matter the title. And then they will work with the rudimentary formulas I made to make them formatted correctly.

Right now I have this in a Worksheet - Change situation:

VBA Code: 
Sub Worksheet_Change(ByVal Target As Range)

Dim lastrow As Long
lastrow = Cells(Rows.Count, 2).End(xlUp).Row

    If Not Intersect(Target, Range("B:B")) Is Nothing Then
        Range("B1:B" & lastrow).Sort key1:=Range("B1:B" & lastrow), _
        order1:=xlAscending, Header:=xlYes

   
    End If
    End Sub

Then Column B adds brackets [=IF(B2<>"", "["&B2&"]", " "] and then Column F puts them all together in one cell and adds spaces [=D2&" "&D3&" "&D4&" "&D5&" "&D6&" "&D7&" "&D8.....etc]

I'm not sure if I am totally making sense to people who actually know how this works. I have attached a screenshot of the sheet before pasting and one after pasting the above values in it.

Please be nice I have no idea what I am doing :) and THANK YOU
Click to expand...
Would this help? It extracts the Date and Military Time so that the sort can take place.

It also concatenates the data using ] ] as delimiters.

Book1
ABC
1DataDateMilitary Time
2Axon Body 3 Video 2021-08-26 2158 X60A656565V2021-08-262158
3Axon Body 3 Video 2021-11-24 2231 X60A656565V2021-11-242231
4Axon Body 3 Video 2021-11-25 2229 X60A986468J2021-11-252229
5Axon Fleet 3 Front Camera Video 2021-11-27 2156 X60A986468J2021-11-272156
6Axon Body 3 Video 2021-12-01 2154 X60A656565V2021-12-012154
7Axon Fleet 3 Interior Camera Video 2022-02-21 1920 X60A656565V2022-02-211920
8
9[Axon Body 3 Video 2021-08-26 2158 X60A656565V] [Axon Body 3 Video 2021-11-24 2231 X60A656565V] [Axon Body 3 Video 2021-11-25 2229 X60A986468J] [Axon Fleet 3 Front Camera Video 2021-11-27 2156 X60A986468J] [Axon Body 3 Video 2021-12-01 2154 X60A656565V] [Axon Fleet 3 Interior Camera Video 2022-02-21 1920 X60A656565V]
10
Sheet1
Cell Formulas
RangeFormula
B2:B7B2=MID($A2,SEARCH("????-??-??",$A2,1),10)
C2:C7C2=MID($A2,SEARCH("????-??-??",$A2,1)+11,4)
A9A9="[" & TEXTJOIN("] [",TRUE,A2:A7) & "]"
 
Upvote 1
1698337324046.png


Welcome to MrExcel. From my perspective that's just a confusing blob of text. Consider using the free xl2BB tool to post your data in way that is accessible to others

1698337451197.png
 
Upvote 0
rlv01 said:
View attachment 101040

Welcome to MrExcel. From my perspective that's just a confusing blob of text. Consider using the free xl2BB tool to post your data in way that is accessible to others

View attachment 101041
Click to expand...
The source data seems fine as long as it is representative but combining this data into one cell is confusing I agree. I wonder what people would get out of looking at it.
 
Upvote 0
Herakles said:
The source data seems fine as long as it is representative but combining this data into one cell is confusing I agree. I wonder what people would get out of looking at it.
Click to expand...
Hello! Thank you both for taking a look. Apologies, now I am confused by your question haha. The source data is exactly how the real data looks, I just changed all of the numbers and times. That confusing blob of text is the desired outcome, exactly how it looks and in one cell. Could one of you phrase your feedback a different way and I can try to be more clear?
 
Upvote 0
rlv01 said:
View attachment 101040

Welcome to MrExcel. From my perspective that's just a confusing blob of text. Consider using the free xl2BB tool to post your data in way that is accessible to others

View attachment 101041
Click to expand...
Hello! Thank you both for taking a look. Apologies, now I am confused by your question haha. The source data is exactly how the real data looks, I just changed all of the numbers and times. That confusing blob of text is the desired outcome, exactly how it looks and in one cell. Could one of you phrase your feedback a different way and I can try to be more clear?
 
Upvote 0
Herakles said:
Would this help? It extracts the Date and Military Time so that the sort can take place.

It also concatenates the data using ] ] as delimiters.

Book1
ABC
1DataDateMilitary Time
2Axon Body 3 Video 2021-08-26 2158 X60A656565V2021-08-262158
3Axon Body 3 Video 2021-11-24 2231 X60A656565V2021-11-242231
4Axon Body 3 Video 2021-11-25 2229 X60A986468J2021-11-252229
5Axon Fleet 3 Front Camera Video 2021-11-27 2156 X60A986468J2021-11-272156
6Axon Body 3 Video 2021-12-01 2154 X60A656565V2021-12-012154
7Axon Fleet 3 Interior Camera Video 2022-02-21 1920 X60A656565V2022-02-211920
8
9[Axon Body 3 Video 2021-08-26 2158 X60A656565V] [Axon Body 3 Video 2021-11-24 2231 X60A656565V] [Axon Body 3 Video 2021-11-25 2229 X60A986468J] [Axon Fleet 3 Front Camera Video 2021-11-27 2156 X60A986468J] [Axon Body 3 Video 2021-12-01 2154 X60A656565V] [Axon Fleet 3 Interior Camera Video 2022-02-21 1920 X60A656565V]
10
Sheet1
Cell Formulas
RangeFormula
B2:B7B2=MID($A2,SEARCH("????-??-??",$A2,1),10)
C2:C7C2=MID($A2,SEARCH("????-??-??",$A2,1)+11,4)
A9A9="[" & TEXTJOIN("] [",TRUE,A2:A7) & "]"
Click to expand...
Ohhh fun! Can you please explain it to me like I am 10 years old or so? This does seem like an overall better way to do this, I'm just not 100% how to implement. I greatly appreciate you taking a look!
 
Upvote 0
dakotacondon1 said:
Ohhh fun! Can you please explain it to me like I am 10 years old or so? This does seem like an overall better way to do this, I'm just not 100% how to implement. I greatly appreciate you taking a look!
Click to expand...
Oh wait! I think I got it! I might have a follow up question, please stand by! Wow, thank you!!!
 
Upvote 0
Herakles said:
Would this help? It extracts the Date and Military Time so that the sort can take place.

It also concatenates the data using ] ] as delimiters.

Book1
ABC
1DataDateMilitary Time
2Axon Body 3 Video 2021-08-26 2158 X60A656565V2021-08-262158
3Axon Body 3 Video 2021-11-24 2231 X60A656565V2021-11-242231
4Axon Body 3 Video 2021-11-25 2229 X60A986468J2021-11-252229
5Axon Fleet 3 Front Camera Video 2021-11-27 2156 X60A986468J2021-11-272156
6Axon Body 3 Video 2021-12-01 2154 X60A656565V2021-12-012154
7Axon Fleet 3 Interior Camera Video 2022-02-21 1920 X60A656565V2022-02-211920
8
9[Axon Body 3 Video 2021-08-26 2158 X60A656565V] [Axon Body 3 Video 2021-11-24 2231 X60A656565V] [Axon Body 3 Video 2021-11-25 2229 X60A986468J] [Axon Fleet 3 Front Camera Video 2021-11-27 2156 X60A986468J] [Axon Body 3 Video 2021-12-01 2154 X60A656565V] [Axon Fleet 3 Interior Camera Video 2022-02-21 1920 X60A656565V]
10
Sheet1
Cell Formulas
RangeFormula
B2:B7B2=MID($A2,SEARCH("????-??-??",$A2,1),10)
C2:C7C2=MID($A2,SEARCH("????-??-??",$A2,1)+11,4)
A9A9="[" & TEXTJOIN("] [",TRUE,A2:A7) & "]"
Click to expand...
Okay this works perfectly for what I need! The only thing is that the order is exactly backwards when I put those formulas into a new worksheet? Any idea what I did wrong?
 
Upvote 0
dakotacondon1 said:
Okay this works perfectly for what I need! The only thing is that the order is exactly backwards when I put those formulas into a new worksheet? Any idea what I did wrong?
Click to expand...
I am now realizing that you maybe meant this as a partner to the VBA code and that have to work together? Not sure how to alter that to fit this magic in...
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question Question
VBA generates word docs, request to amend code to include description of deadlines/tasks due
Replies
0
Views
352
ttp921
T
J
  • Question Question
VBA to Create Table and to Remove Duplicates
Replies
0
Views
1K
jessebh2003
J
J
  • Question Question
VBA Code for Macro - Search sheet for keywords and paste rows to new sheet
Replies
9
Views
3K
kevin9999
K
Martin_H
  • Question Question
Slightly modify the code [VBA]
Replies
1
Views
517
Joe4
Joe4
A
  • Solved
Macro that creates a word document (and then a PDF) very slow
2
Replies
11
Views
3K
Anthony47
A

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top