Macro for identifying distinct days from time stamp doesn't work

[Andy0311]

Hello,
I run a seat time report for 30 teachers which measures the time they spent online (distinct days).

Teacher	Course	Date	Time (hh:mm)	IP Address
Allison, Robert
	*Home Page/eMail/Forum	10/1/2019 5:39 AM	0:00	71.72.124.152
	*Home Page/eMail/Forum	10/1/2019 6:44 AM	0:00	71.72.124.152
	Math Integrated Math III (1-18)	10/1/2019 6:44 AM	0:03	71.72.124.152
	*Home Page/eMail/Forum	10/1/2019 6:47 AM	0:00	71.72.124.152
	Math Integrated Math III (1-18)	10/1/2019 6:47 AM	0:06	71.72.124.152
	*Home Page/eMail/Forum	10/1/2019 6:53 AM	0:00	71.72.124.152
	*Home Page/eMail/Forum	10/1/2019 9:08 AM	0:00	71.72.124.152
	Math Integrated Math III (1-18)	10/1/2019 9:08 AM	0:00	71.72.124.152
	*Home Page/eMail/Forum	10/1/2019 9:08 AM	0:01	71.72.124.152
	Math Integrated Math III (1-18)	10/1/2019 9:09 AM	0:00	71.72.124.152

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

The date column is a time stamp and I first have to take that data and separate the data the Text to Column function. Then I can take the date column and use Remove Duplicates, leaving me the unique days the teacher was online. My problem is that it works correctly for some teachers and incorrectly for others. Each teacher report is in a separate tab in my workbook. I need to know what I did wrong to make this macro work at times and at not. Any help would be appreciated. Here's the macro code:

Sub DistinctDays()
'
' DistinctDays Macro
'


'
Columns("D:F").Select
Range("D3").Activate
Selection.UnMerge
Columns("B:H").EntireColumn.AutoFit
Range("D6").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("A4").Select
Columns("J:J").ColumnWidth = 27.71
Range("J6").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.TextToColumns Destination:=Range("J6"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1)), TrailingMinusNumbers:=True
ActiveSheet.Range("$J$6:$J$511").RemoveDuplicates Columns:=1, Header:=xlNo
Columns("E:F").Select
Selection.Delete Shift:=xlToLeft
Columns("I:J").Select
Selection.Delete Shift:=xlToLeft
Range("H4").Select
ActiveCell.FormulaR1C1 = "=COUNTA(R[2]C:R[18]C)"
Range("H5").Select
End Sub

Thank you in advance.

Andy

 

[DataBlake]

a few things to help people understand what you're trying to accomplish:
- use code tags when posting code
- show an end example of it working and it not working
- explain "my problem is it works correctly"; what is "it" and how do we know if it works correctly?

in the mean time i'll try and decipher what your code is doing, but you should state the steps that the code is intending to do as someone may know a more efficient way of doing what you want. Without running your code i have no idea what we're trying to accomplish here aside from removing duplicate dates

 

[DataBlake]

this is what i have after running your code and i have a feeling its not whats intended because the beginning where it wants to select D:F and unmerge something doesn't happen when i copy/paste the data you provided.

Book1
	A	B	C	D	E	F	G	H
1	Teacher	Course	Date	Time
2				(hh:mm)
3	Allison, Robert
4		*Home Page/eMail/Forum	10/1/2019 5:39	0:00				4
5		*Home Page/eMail/Forum	10/1/2019 6:44	0:00
6		Math Integrated Math III (1-18)	10/1/2019 6:44	0:03				12:03
7		*Home Page/eMail/Forum	10/1/2019 6:47	0:00				12:00
8		Math Integrated Math III (1-18)	10/1/2019 6:47	0:06				12:06
9		*Home Page/eMail/Forum	10/1/2019 6:53	0:00				12:01
10		*Home Page/eMail/Forum	10/1/2019 9:08	0:00
11		Math Integrated Math III (1-18)	10/1/2019 9:08	0:00
12		*Home Page/eMail/Forum	10/1/2019 9:08	0:01
13		Math Integrated Math III (1-18)	10/1/2019 9:09	0:00
Sheet1

if this is any indication, can you explain in as much detail as possible what you wish to do using column letters, row numbers, sheet names, etc

 

[MARK858]

@Andy0311, can you also let us know what column letters the columns shown in your sample in post number one apply to in reality.

Thank you.

Edit: @BlakeSkate, just to let you know that I got the same results as you when running the code after a straight copy/paste into A1.

 

[Andy0311]

this is what i have after running your code and i have a feeling its not whats intended because the beginning where it wants to select D:F and unmerge something doesn't happen when i copy/paste the data you provided.

	A	B	C	D	E	F	G	H
1	Teacher	Course	Date	Time
2				(hh:mm)
3	Allison, Robert
4		*Home Page/eMail/Forum	10/1/2019 5:39	0:00				4
5		*Home Page/eMail/Forum	10/1/2019 6:44	0:00
6		Math Integrated Math III (1-18)	10/1/2019 6:44	0:03				12:03
7		*Home Page/eMail/Forum	10/1/2019 6:47	0:00				12:00
8		Math Integrated Math III (1-18)	10/1/2019 6:47	0:06				12:06
9		*Home Page/eMail/Forum	10/1/2019 6:53	0:00				12:01
10		*Home Page/eMail/Forum	10/1/2019 9:08	0:00
11		Math Integrated Math III (1-18)	10/1/2019 9:08	0:00
12		*Home Page/eMail/Forum	10/1/2019 9:08	0:01
13		Math Integrated Math III (1-18)	10/1/2019 9:09	0:00

<tbody>
</tbody>

Sheet1​

if this is any indication, can you explain in as much detail as possible what you wish to do using column letters, row numbers, sheet names, etc

Thanks for your help. The above sheet actually starts in column B. A is hidden. Before recording my macro, I saw that columns D, E, and F were merged. That's where the date and time stamp is. I can't separate the date from the time on merged columns so in unmerging the date and time stamp was in Col D, and E and F were blank columns and I deleted them. I copied column D outside the data set and ran Text to Columns. Then I removed the duplicate dates. Here is what the intended result looks like, shown in red:

Teacher	Course	Date	Time (hh:mm)	IP Address		3
Allison, Robert
	*Home Page/eMail/Forum	10/1/2019 5:39 AM	0:00	71.72.124.152		10/1/2019 12:00 AM	5:39:46	AM
	*Home Page/eMail/Forum	10/1/2019 6:44 AM	0:00	71.72.124.152		10/2/2019 12:00 AM	6:44:14	AM
	Math Integrated Math III (1-18)	10/2/2019 6:44 AM	0:03	71.72.124.152		10/3/2019 12:00 AM	6:44:19	AM
	*Home Page/eMail/Forum	10/2/2019 6:47 AM	0:00	71.72.124.152			6:47:45	AM
	Math Integrated Math III (1-18)	10/2/2019 6:47 AM	0:06	71.72.124.152			6:47:50	AM
	*Home Page/eMail/Forum	10/3/2019 6:53 AM	0:00	71.72.124.152			6:53:03	AM
	*Home Page/eMail/Forum	10/3/2019 9:08 AM	0:00	71.72.124.152			9:08:17	AM
	Math Integrated Math III (1-18)	10/3/2019 9:08 AM	0:00	71.72.124.152			9:08:21	AM
	*Home Page/eMail/Forum	10/3/2019 9:08 AM	0:01	71.72.124.152			9:08:53	AM

<tbody>
</tbody>

The intended result is a column showing the number of distinct days online. This sample data shows only 15 rows, but can be over 1000.

 

[Andy0311]

Thanks for your help. The above sheet actually starts in column B. A is hidden. Before recording my macro, I saw that columns D, E, and F were merged. That's where the date and time stamp is. I can't separate the date from the time on merged columns so in unmerging the date and time stamp was in Col D, and E and F were blank columns and I deleted them. I copied column D outside the data set and ran Text to Columns. Then I removed the duplicate dates. Here is what the intended result looks like, shown in red:

Teacher	Course	Date	Time (hh:mm)	IP Address		3
Allison, Robert
	*Home Page/eMail/Forum	10/1/2019 5:39 AM	0:00	71.72.124.152		10/1/2019 12:00 AM	5:39:46	AM
	*Home Page/eMail/Forum	10/1/2019 6:44 AM	0:00	71.72.124.152		10/2/2019 12:00 AM	6:44:14	AM
	Math Integrated Math III (1-18)	10/2/2019 6:44 AM	0:03	71.72.124.152		10/3/2019 12:00 AM	6:44:19	AM
	*Home Page/eMail/Forum	10/2/2019 6:47 AM	0:00	71.72.124.152			6:47:45	AM
	Math Integrated Math III (1-18)	10/2/2019 6:47 AM	0:06	71.72.124.152			6:47:50	AM
	*Home Page/eMail/Forum	10/3/2019 6:53 AM	0:00	71.72.124.152			6:53:03	AM
	*Home Page/eMail/Forum	10/3/2019 9:08 AM	0:00	71.72.124.152			9:08:17	AM
	Math Integrated Math III (1-18)	10/3/2019 9:08 AM	0:00	71.72.124.152			9:08:21	AM
	*Home Page/eMail/Forum	10/3/2019 9:08 AM	0:01	71.72.124.152			9:08:53	AM

<tbody>
</tbody>

The intended result is a column showing the number of distinct days online. This sample data shows only 15 rows, but can be over 1000.

In my sample data, the result(3 days) worked. My problem is that sometimes all days are not counted the the result isn't correct. I ran the macro the other day and the number of distinct days should have been 20 but only 15 were counted. Thank you for all your help, and advice for posting properly. I am still learning the protocols.

 

[Andy0311]

@Andy0311, can you also let us know what column letters the columns shown in your sample in post number one apply to in reality.

Thank you.

Edit: @BlakeSkate, just to let you know that I got the same results as you when running the code after a straight copy/paste into A1.

Here the columns in my sample data. Please take a look at what I sent BlakeSkate if you have time. Thanks again for your help.

B	C	D	E	F		I	J	K
Teacher	Course	Date	Time (hh:mm)	IP Address		3
Allison, Robert
	*Home Page/eMail/Forum	10/1/2019 5:39 AM	0:00	71.72.124.152		10/1/2019 12:00 AM	5:39:46	AM
	*Home Page/eMail/Forum	10/1/2019 6:44 AM	0:00	71.72.124.152		10/2/2019 12:00 AM	6:44:14	AM
	Math Integrated Math III (1-18)	10/2/2019 6:44 AM	0:03	71.72.124.152		10/3/2019 12:00 AM	6:44:19	AM
	*Home Page/eMail/Forum	10/2/2019 6:47 AM	0:00	71.72.124.152			6:47:45	AM
	Math Integrated Math III (1-18)	10/2/2019 6:47 AM	0:06	71.72.124.152			6:47:50	AM
	*Home Page/eMail/Forum	10/3/2019 6:53 AM	0:00	71.72.124.152			6:53:03	AM
	*Home Page/eMail/Forum	10/3/2019 9:08 AM	0:00	71.72.124.152			9:08:17	AM
	Math Integrated Math III (1-18)	10/3/2019 9:08 AM	0:00	71.72.124.152			9:08:21	AM
	*Home Page/eMail/Forum	10/3/2019 9:08 AM	0:01	71.72.124.152			9:08:53	AM

<colgroup><col><col><col><col><col><col><col><col span="2"></colgroup><tbody>
</tbody>

 

[DataBlake]

Any chance you can upload your workbook to cloud storage like dropbox or google drive?
I can't seem to replicate the merged cells
If you can upload it please include a second sheet that DOESN'T work for you with your current code.
If you can't upload it what cells specifically are merged in your example? just the header? give me a range of cells that are merged.

my guess to why your macro does not work on your other sheets is that the ranges of cells are different
it would be no problem to convert your macro into a dynamic one, but i need to make sure that were looking at the same data first.

 

[MARK858]

I have the same concerns as BlakeSkate at the moment but just out of interest what happens if you run the code below on a copy of your actual data.

Sub DistinctDays2()
    Dim myrow As Long, arr, mycell

    Application.ScreenUpdating = False
    Columns("D:F").UnMerge
    Columns("B:H").EntireColumn.AutoFit

    Range("D6:D" & Range("D" & Rows.Count).End(xlUp).Row).Copy Range("J6")

    Columns("J:J").ColumnWidth = 27.71


    Range("J6:J" & Range("J" & Rows.Count).End(xlUp).Row).TextToColumns Destination:=Range("J6"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 1)), TrailingMinusNumbers:=True

    
    myrow = Cells(Rows.Count, "J").End(xlUp).Row - 5
    arr = Range("J6").Resize(myrow)
    With CreateObject("scripting.dictionary")
        For Each mycell In arr
            mycell = Trim(mycell)
            If Not .Exists(mycell) Then
                .Add mycell, Empty
                arr(.Count, 1) = mycell
            End If
        Next mycell
        Range("J6").Resize(myrow).ClearContents
        Range("J6").Resize(.Count) = arr
    End With

    Columns("E:F").Delete Shift:=xlToLeft
    Columns("I:J").Delete Shift:=xlToLeft
    Range("H4").FormulaR1C1 = "=COUNTA(R[2]C:R[18]C)"

    Application.ScreenUpdating = True
End Sub

 

[Andy0311]

Any chance you can upload your workbook to cloud storage like dropbox or google drive?
I can't seem to replicate the merged cells
If you can upload it please include a second sheet that DOESN'T work for you with your current code.
If you can't upload it what cells specifically are merged in your example? just the header? give me a range of cells that are merged.

my guess to why your macro does not work on your other sheets is that the ranges of cells are different
it would be no problem to convert your macro into a dynamic one, but i need to make sure that were looking at the same data first.

I can save my workbook in Google Drive, or send the file to you as an email. What should I do to share it with you? Thank you. Andy