Finding all the dates and copy them in separate place

Amit Shukla

New Member
Joined
Jan 14, 2020
Messages
7
Office Version
2007
Platform
Windows
I have a table in my excel where all the project task completion dates are put.
I need to find all the dates that are in the current month, next month and so on.
Need to filter are the dates in ascending order so that it is easy for a follow-up.
There can be a duplicate entry also as a single date can have two different project tasks completing.
Besides every date, in the adjacent column, it should also mention the project head (in this case is the location).
 

Attachments

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
4,660
Office Version
365
Platform
Windows
Welcome to the forum

Add-in XL2BB allows you to post sample data and expected results which makes it much easier for anyone trying to help to understand what you want and avoids the need to recreate data

To download XL2BB click on the icon above reply window and read all the instructions and how to deal with any problems that may arise
 

Amit Shukla

New Member
Joined
Jan 14, 2020
Messages
7
Office Version
2007
Platform
Windows
Thanks for the update.
Find the below:

xl2bb.xlam
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAM
2DELHICHENNAI
3Project 108-01-201517-01-201503-02-201528-02-201502-04-201513-05-201501-07-201527-08-201525-10-201512-01-201602-04-201630-06-201605-10-201618-01-201711-05-201709-09-201716-01-2018Project 113-01-201527-01-201518-02-201520-03-201527-04-201512-06-201505-08-201506-10-201515-12-201502-03-201627-05-201629-08-201609-12-201629-03-201725-07-201728-11-201711-04-2018
4Project 206-03-201515-03-201501-04-201526-04-201529-05-201509-07-201527-08-201523-10-201521-12-201509-03-201629-05-201626-08-201601-12-201616-03-201707-07-201705-11-201714-03-2018Project 211-03-201525-03-201516-04-201516-05-201523-06-201508-08-201501-10-201502-12-201510-02-201628-04-201623-07-201625-10-201604-02-201725-05-201720-09-201724-01-201807-06-2018
5Project 314-06-201523-06-201510-07-201504-08-201506-09-201517-10-201505-12-201531-01-201630-03-201617-06-201606-09-201604-12-201611-03-201724-06-201715-10-201713-02-201822-06-2018Project 319-06-201503-07-201525-07-201524-08-201501-10-201516-11-201509-01-201611-03-201620-05-201606-08-201631-10-201602-02-201715-05-201702-09-201729-12-201704-05-201815-09-2018
6Project 425-07-201503-08-201520-08-201514-09-201517-10-201527-11-201515-01-201612-03-201610-05-201628-07-201617-10-201614-01-201721-04-201704-08-201725-11-201726-03-201802-08-2018Project 430-07-201513-08-201504-09-201504-10-201511-11-201527-12-201519-02-201621-04-201630-06-201616-09-201611-12-201615-03-201725-06-201713-10-201708-02-201814-06-201826-10-2018
7Project 510-09-201519-09-201506-10-201531-10-201503-12-201513-01-201602-03-201628-04-201626-06-201613-09-201603-12-201602-03-201707-06-201720-09-201711-01-201812-05-201818-09-2018Project 515-09-201529-09-201521-10-201520-11-201528-12-201512-02-201606-04-201607-06-201616-08-201602-11-201627-01-201701-05-201711-08-201729-11-201727-03-201831-07-201812-12-2018
8Project 628-10-201506-11-201523-11-201518-12-201520-01-201601-03-201619-04-201615-06-201613-08-201631-10-201620-01-201719-04-201725-07-201707-11-201728-02-201829-06-201805-11-2018Project 602-11-201516-11-201508-12-201507-01-201614-02-201631-03-201624-05-201625-07-201603-10-201620-12-201616-03-201718-06-201728-09-201716-01-201814-05-201817-09-201829-01-2019
9Project 702-03-201611-03-201628-03-201622-04-201625-05-201605-07-201623-08-201619-10-201617-12-201606-03-201726-05-201723-08-201728-11-201713-03-201804-07-201802-11-201811-03-2019Project 707-03-201621-03-201612-04-201612-05-201619-06-201604-08-201627-09-201628-11-201606-02-201725-04-201720-07-201722-10-201701-02-201822-05-201817-09-201821-01-201904-06-2019
10Project 810-09-201619-09-201606-10-201631-10-201603-12-201613-01-201703-03-201729-04-201727-06-201714-09-201704-12-201703-03-201808-06-201821-09-201812-01-201913-05-201919-09-2019Project 815-09-201629-09-201621-10-201620-11-201628-12-201612-02-201707-04-201708-06-201717-08-201703-11-201728-01-201802-05-201812-08-201830-11-201828-03-201901-08-201913-12-2019
11Project 928-12-201606-01-201723-01-201717-02-201722-03-201702-05-201720-06-201716-08-201714-10-201701-01-201823-03-201820-06-201825-09-201808-01-201901-05-201930-08-201906-01-2020Project 902-01-201716-01-201707-02-201709-03-201716-04-201701-06-201725-07-201725-09-201704-12-201720-02-201817-05-201819-08-201829-11-201819-03-201915-07-201918-11-201931-03-2020
12Project 1031-01-201809-02-201826-02-201823-03-201825-04-201805-06-201824-07-201819-09-201817-11-201804-02-201926-04-201924-07-201929-10-201911-02-202003-06-202002-10-202008-02-2021Project 1005-02-201819-02-201813-03-201812-04-201820-05-201805-07-201828-08-201829-10-201807-01-201926-03-201920-06-201922-09-201902-01-202021-04-202017-08-202021-12-202004-05-2021
13Project 1125-03-201803-04-201820-04-201815-05-201817-06-201828-07-201815-09-201811-11-201809-01-201929-03-201918-06-201915-09-201921-12-201904-04-202026-07-202024-11-202002-04-2021Project 1130-03-201813-04-201805-05-201804-06-201812-07-201827-08-201820-10-201821-12-201801-03-201918-05-201912-08-201914-11-201924-02-202013-06-202009-10-202012-02-202126-06-2021
14Project 1216-05-201825-05-201811-06-201806-07-201808-08-201818-09-201806-11-201802-01-201902-03-201920-05-201909-08-201906-11-201911-02-202026-05-202016-09-202015-01-202124-05-2021Project 1221-05-201804-06-201826-06-201826-07-201802-09-201818-10-201811-12-201811-02-201922-04-201909-07-201903-10-201905-01-202016-04-202004-08-202030-11-202005-04-202117-08-2021
15Project 1330-06-201809-07-201826-07-201820-08-201822-09-201802-11-201821-12-201816-02-201916-04-201904-07-201923-09-201921-12-201927-03-202010-07-202031-10-202001-03-202108-07-2021Project 1305-07-201819-07-201810-08-201809-09-201817-10-201802-12-201825-01-201928-03-201906-06-201923-08-201917-11-201919-02-202031-05-202018-09-202014-01-202120-05-202101-10-2021
16Project 1430-08-201808-09-201825-09-201820-10-201822-11-201802-01-201920-02-201918-04-201916-06-201903-09-201923-11-201920-02-202027-05-202009-09-202031-12-202001-05-202107-09-2021Project 1404-09-201818-09-201810-10-201809-11-201817-12-201801-02-201927-03-201928-05-201906-08-201923-10-201917-01-202020-04-202031-07-202018-11-202016-03-202120-07-202101-12-2021
17Project 1527-10-201805-11-201822-11-201817-12-201819-01-201901-03-201919-04-201915-06-201913-08-201931-10-201920-01-202018-04-202024-07-202006-11-202027-02-202128-06-202104-11-2021Project 1501-11-201815-11-201807-12-201806-01-201913-02-201931-03-201924-05-201925-07-201903-10-201920-12-201915-03-202017-06-202027-09-202015-01-202113-05-202116-09-202128-01-2022
18Project 1620-04-201929-04-201916-05-201910-06-201913-07-201923-08-201911-10-201907-12-201904-02-202023-04-202013-07-202010-10-202015-01-202130-04-202121-08-202120-12-202128-04-2022Project 1625-04-201909-05-201931-05-201930-06-201907-08-201922-09-201915-11-201916-01-202026-03-202012-06-202006-09-202009-12-202021-03-202109-07-202104-11-202110-03-202222-07-2022
19Project 1716-05-201925-05-201911-06-201906-07-201908-08-201918-09-201906-11-201902-01-202001-03-202019-05-202008-08-202005-11-202010-02-202126-05-202116-09-202115-01-202224-05-2022Project 1721-05-201904-06-201926-06-201926-07-201902-09-201918-10-201911-12-201911-02-202021-04-202008-07-202002-10-202004-01-202116-04-202104-08-202130-11-202105-04-202217-08-2022
20Project 1805-06-201914-06-201901-07-201926-07-201928-08-201908-10-201926-11-201922-01-202021-03-202008-06-202028-08-202025-11-202002-03-202115-06-202106-10-202104-02-202213-06-2022Project 1810-06-201924-06-201916-07-201915-08-201922-09-201907-11-201931-12-201902-03-202011-05-202028-07-202022-10-202024-01-202106-05-202124-08-202120-12-202125-04-202206-09-2022
21Project 1921-09-201930-09-201917-10-201911-11-201914-12-201924-01-202013-03-202009-05-202007-07-202024-09-202014-12-202013-03-202118-06-202101-10-202122-01-202223-05-202229-09-2022Project 1926-09-201910-10-201901-11-201901-12-201908-01-202023-02-202017-04-202018-06-202027-08-202013-11-202007-02-202112-05-202122-08-202110-12-202107-04-202211-08-202223-12-2022
22Project 2003-01-202012-01-202029-01-202023-02-202027-03-202007-05-202025-06-202021-08-202019-10-202006-01-202128-03-202125-06-202130-09-202113-01-202206-05-202204-09-202211-01-2023Project 2008-01-202022-01-202013-02-202014-03-202021-04-202006-06-202030-07-202030-09-202009-12-202025-02-202122-05-202124-08-202104-12-202124-03-202220-07-202223-11-202206-04-2023
23
24
25
26Paste Here
27
28
Calc
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
4,660
Office Version
365
Platform
Windows
That is the same as your picture and only shows your data

What are your expected results ?
 

Amit Shukla

New Member
Joined
Jan 14, 2020
Messages
7
Office Version
2007
Platform
Windows
I need all the dates which are in this month and for the next 11 months with a blank cell every month change. It will also show the location details.

So, the result will look something like this:

xl2bb.xlam
BCDEFGH
25
26All dates from current month02-01-2020Delhi
2703-01-2020Delhi
2806-01-2020Delhi
2912-01-2020Kerala
3020-01-2020Delhi
3122-01-2020Delhi
3224-01-2020Kerala
3329-01-2020Delhi
34
3504-02-2020Delhi
3611-02-2020Delhi
3711-02-2020Kerala
3820-02-2020
3923-02-2020
40
4101-03-2020
4213-03-2020
4321-03-2020
4427-03-2020
4527-03-2020
46
Calc
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
4,660
Office Version
365
Platform
Windows
The code below produces a list exactly like the list requested in post#5
- it assumes that the data is set out EXACTLY like the data you provided
- it assumes that there is an empty column between each region
- it assumes that region names are in row 2
- it assumes that the data is contiguous within each region (ie no empty rows or columns)

The code looks for "CHENNAI" "DELHI" "KERALA" in row 2 to determine which ranges to search for dates
Amend name of sheet containing your data in this line
VBA Code:
Set Data = Sheets("Sheet1")
The Input Box should return the correct starting date if running the code this month
- amending the date in the input box to 01-May-2020 returns dates for 12 months starting from 1 May 2020

This line prevents the same date being repeated for the same region
- delete line if you want dates repeated for same region
VBA Code:
ws.Range("A:B").RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes

VBA Code:
Sub GetDates()
    Dim Region As Variant, Cel As Range, CelAbove As Range, FromDate As Date, ToDate As Date
    Dim ws As Worksheet, Data As Worksheet, r As Long
    Set Data = Sheets("Sheet1")
    On Error Resume Next
'get dates
    FromDate = DateSerial(Year(Date), Month(Date), 1)
    FromDate = InputBox("OK to contine or amend date", "FIRST DAY OF MONTH", Format(FromDate, "dd-mmm-yy"))
    ToDate = DateSerial(Year(FromDate) + 1, Month(FromDate), 1)
'new worksheet
    Application.ScreenUpdating = False
    Set ws = Sheets.Add(before:=Sheets(1))
    With ws.Range("A1:B1")
        .Value = Array("Date", "Place")
        .ColumnWidth = 20
    End With
'add dates
    For Each Region In Array("CHENNAI", "DELHI", "KERALA")
        For Each Cel In Data.Range("2:2").Find(Region).CurrentRegion
           If Cel > FromDate And Cel < ToDate Then
                ws.Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(, 2).Value = Array(Cel, Region)
            End If
        Next Cel
    Next Region
'sort dates
    ws.Sort.SortFields.Add2 Key:=Range("A2"), SortOn:=xlSortOnValues, Order:=xlAscending
    ws.Sort.SortFields.Add2 Key:=Range("B2"), SortOn:=xlSortOnValues, Order:=xlAscending
    With ws.Sort
        .SetRange Range("A:B")
        .Header = xlYes
        .Apply
    End With
    ws.Range("A:B").RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes        'SEE NOTE
    ws.Range("A:A").NumberFormat = "dd-mmm-yy"
'Insert Space
    For r = ws.Cells(Rows.Count, 1).End(xlUp) To 3 Step -1
        Set Cel = ws.Cells(r, 1)
        Set CelAbove = Cel.Offset(-1)
        If Month(Cel) <> Month(CelAbove) Then Cel.Resize(, 2).Insert Shift:=xlDown
    Next r
End Sub
 

Amit Shukla

New Member
Joined
Jan 14, 2020
Messages
7
Office Version
2007
Platform
Windows
Thanks for the Help Yongle; beautiful and simple code
A couple of things that are not happening:
1. Sorting of date after filtration; ignore the spaces in between
2. Locations are not static, they may vary depending on the current project, so table heading is auto-selected. What if there is one more location added next month?
3. The code generated a fresh sheet every time. so can we specify a particular cell where the data can come in the same sheet?
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
4,660
Office Version
365
Platform
Windows
beautiful and simple code
Thank you :)
- code should never be obsure
- it should be instantly easy to understand what is going on ( for whoever reads it)

1. Sorting of date after filtration; ignore the spaces in between
Against my test data, the code generated a list in date sequence with a space after the last entry each month
- are you saying that the code is not doing that when you run it ?
- or are you saying something else :unsure:

2. Locations are not static, they may vary depending on the current project, so table heading is auto-selected. What if there is one more location added next month?
Are you saying that you want the code to
- look for any cell containing a value in row 2
- use each one of those values as a location
- use each location to determine the range

3. The code generated a fresh sheet every time. so can we specify a particular cell where the data can come in the same sheet?
Do you want the old data cleared and the new data to start from the same cell each time ?
- if so what is the sheet name and which cell should hold the first date value ?
 

Amit Shukla

New Member
Joined
Jan 14, 2020
Messages
7
Office Version
2007
Platform
Windows
Thank you :)
- code should never be obsure
- it should be instantly easy to understand what is going on ( for whoever reads it)


Against my test data, the code generated a list in date sequence with a space after the last entry each month
- are you saying that the code is not doing that when you run it ?
- or are you saying something else :unsure:


Are you saying that you want the code to
- look for any cell containing a value in row 2
- use each one of those values as a location
- use each location to determine the range


Do you want the old data cleared and the new data to start from the same cell each time ?
- if so what is the sheet name and which cell should hold the first date value ?
Yes, sorting is not happening. We can ignore the space and try sorting of data.
The location on the row 2 is needed as that can change anytime or if the project is completed.
As shown above the result can come on the same sheet somewhere. For Eg. starting at F26 or somewhere specific.

My question here is, can we consider every location wise extraction of dates and then sort after compiling all the location result, it will be easier.
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
4,660
Office Version
365
Platform
Windows
My result
SortDates.jpg


Sheet containing the dates - amend sheet name in this line
Set Data = Sheets("Sheet1")

Results are placed 5 rows below last used cell in column B as determined by this line
Set Result = Data.Cells(Rows.Count, 2).End(xlUp).Offset(5)

Test on a COPY of your data
VBA Code:
Sub GetDates()
    Dim Region As Range, Cel As Range, CelAbove As Range, Result As Range
    Dim ws As Worksheet, Data As Worksheet, r As Long, FromDate As Date, ToDate As Date
    Set Data = Sheets("Sheet1")                                         'which sheet contains the dates ???
    Set Result = Data.Cells(Rows.Count, 2).End(xlUp).Offset(5)          'result go here
    Data.Activate
    On Error Resume Next
'get dates
    FromDate = DateSerial(Year(Date), Month(Date), 1)
    FromDate = InputBox("OK to contine or amend date", "FIRST DAY OF MONTH", Format(FromDate, "dd-mmm-yy"))
    ToDate = DateSerial(Year(FromDate) + 1, Month(FromDate), 1)
    Application.ScreenUpdating = False
    Set ws = Sheets.Add
'add dates
    For Each Region In Data.Range("A2", Data.Cells(2, Columns.Count).End(xlToLeft))
        If Region <> "" Then
            For Each Cel In Region.CurrentRegion
                If Cel > FromDate And Cel < ToDate Then ws.Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(, 2).Value = Array(Cel, Region)
            Next Cel
        End If
    Next Region
'sort dates
    With ws.Range("A1").CurrentRegion
        .Sort [A1], xlAscending
        .Resize(, 1).NumberFormat = "dd-mmm-yy"
        .RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes
    End With
'insert space
    For r = ws.Cells(Rows.Count, 1).End(xlUp) To 2 Step -1
        Set Cel = ws.Cells(r, 1)
        Set CelAbove = Cel.Offset(-1)
        If Month(Cel) <> Month(CelAbove) Then Cel.Resize(, 2).Insert Shift:=xlDown
    Next r
'clear previous result and replace with latest result
    Result.Resize(Rows.Count - Result.Row, 2).ClearContents
    ws.UsedRange.Copy Result
    Application.DisplayAlerts = False: ws.Delete: Application.DisplayAlerts = True
End Sub
 

Forum statistics

Threads
1,081,950
Messages
5,362,334
Members
400,673
Latest member
mikansang

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top