How to replace/clear duplicate matches based on 2 columns

[Jay_S1]

Hi,

Im hoping someone could help advise if its possible to create a formula to remove any matching dates & times for the Excel image attached. I just want to have only one exact date & time listed. E.g columns M & N below would only show 2 entries for 24/03/2023 @ 09:53:20 & 28/03/2023 @ 11:56:27.

M N

28/03/2023 09:53:20
24/03/2023 11:56:27
28/03/2023 09:53:20
24/03/2023 11:56:27

The issue im having is that I don't want to remove the whole row, as it has data that's required. But rather clear/remove, or substitute it (with "null") instead.

I hope that makes sense. As ive spent all day trying to find an answer with no joy at all and now starting to lose it.

Regards

Jay

 

[alansidman]

I am confused by your explanation. Suggest you take the picture and reload it using XL2BB and then for the same data show in XL2BB your expected results mocked up manually. It is not clear what your expected results should look like.

 

[Jay_S1]

Column M Date​	Column N Time​
28/03/2023​	09:53:20​
28/03/2023​	11:56:27​
28/03/2023​	09:53:20​
28/03/2023​	11:56:27​
28/03/2023​	11:56:27​
27/03/2023​	13:22:40​
27/03/2023​	14:56:55​
27/03/2023​	15:15:18​
27/03/2023​	13:22:40​
27/03/2023​	14:56:55​
21/03/2023​	15:55:25​
21/03/2023​	16:12:29​
21/03/2023​	15:55:25​
21/03/2023​	16:12:29​
21/03/2023​	15:55:25​
20/03/2023​	15:27:55​
20/03/2023​	15:27:55​
20/03/2023​	15:27:55​
20/03/2023​	15:27:55​
20/03/2023​	15:27:55​
17/03/2023​	10:06:37​
17/03/2023​	09:32:21​
17/03/2023​	10:06:37​
17/03/2023​	09:32:21​
17/03/2023​	10:06:37​
17/03/2023​	09:32:21​

I dont know if this helps but I have an Excel sheet where columns M & N have duplicated the time stamps across multiple rows. Like the top 2 rows above (highlighted in bold black and with the duplicated cells in red).

I hope to remove the duplicates (in red) to leave just one-time stamp (see below-desired results). The issue is I need to compare/match data across both columns (M&N) and clear those cells with duplicates, not the whole row of data (Leave all other columns unaffected). So the results would just show :

Column M Date​	Column N Time​
28/03/2023​	09:53:20​
28/03/2023​	11:56:27​
"Duplicate - Cleared/replaced"​	"Duplicate - Cleared/replaced"​
"Duplicate - Cleared/replaced"​	"Duplicate - Cleared/replaced"​
"Duplicate - Cleared/replaced"​	"Duplicate - Cleared/replaced"​
27/03/2023​	13:22:40​
27/03/2023​	14:56:55​
27/03/2023​	15:15:18​
"Duplicate - Cleared/replaced"​	"Duplicate - Cleared/replaced"​
"Duplicate - Cleared/replaced"​	"Duplicate - Cleared/replaced"​
21/03/2023​	15:55:25​
21/03/2023​	16:12:29​
"Duplicate - Cleared/replaced"​	"Duplicate - Cleared/replaced"​
"Duplicate - Cleared/replaced"​	"Duplicate - Cleared/replaced"​
"Duplicate - Cleared/replaced"​	"Duplicate - Cleared/replaced"​
20/03/2023​	15:27:55​
"Duplicate - Cleared/replaced"​	"Duplicate - Cleared/replaced"​
20/03/2023​	"Duplicate - Cleared/replaced"​
"Duplicate - Cleared/replaced"​	"Duplicate - Cleared/replaced"​
20/03/2023​	15:27:55​
17/03/2023​	10:06:37​
17/03/2023​	09:32:21​
"Duplicate - Cleared/replaced"​	"Duplicate - Cleared/replaced"​
"Duplicate - Cleared/replaced"​	"Duplicate - Cleared/replaced"​
"Duplicate - Cleared/replaced"​	"Duplicate - Cleared/replaced"​
"Duplicate - Cleared/replaced"​	"Duplicate - Cleared/replaced"​

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Assuming that those dates and times are 'real' dates/times (that is numerical), give this a try with a copy of your data.

Sub ClearDupes()
  Dim d As Object
  Dim a As Variant
  Dim i As Long
 
  Set d = CreateObject("Scripting.Dictionary")
  With Range("M2", Range("N" & Rows.Count).End(xlUp))
    a = .Value
    For i = 1 To UBound(a)
      If d.exists(Format(a(i, 1) + a(i, 2), "ddmmyyhh")) Then a(i, 1) = vbNullString: a(i, 2) = vbNullString
      d(Format(a(i, 1) + a(i, 2), "ddmmyyhh")) = 1
    Next i
    .Value = a
  End With
End Sub

Before:

Jay_S1.xlsm
	M	N
1	Date	Time
2	28/03/2023	9:53:20
3	28/03/2023	11:56:27
4	28/03/2023	9:53:20
5	28/03/2023	11:56:27
6	28/03/2023	11:56:27
7	27/03/2023	13:22:40
8	27/03/2023	14:56:55
9	27/03/2023	15:15:18
10	27/03/2023	13:22:40
11	27/03/2023	14:56:55
12	21/03/2023	15:55:25
13	21/03/2023	16:12:29
14	21/03/2023	15:55:25
15	21/03/2023	16:12:29
16	21/03/2023	15:55:25
17	20/03/2023	15:27:55
18	20/03/2023	15:27:55
19	20/03/2023	15:27:55
20	20/03/2023	15:27:55
21	20/03/2023	15:27:55
22	17/03/2023	10:06:37
23	17/03/2023	9:32:21
24	17/03/2023	10:06:37
25	17/03/2023	9:32:21
26	17/03/2023	10:06:37
27	17/03/2023	9:32:21
Sheet3

After:

Jay_S1.xlsm
	M	N
1	Date	Time
2	28/03/2023	9:53:20
3	28/03/2023	11:56:27
4
5
6
7	27/03/2023	13:22:40
8	27/03/2023	14:56:55
9	27/03/2023	15:15:18
10
11
12	21/03/2023	15:55:25
13	21/03/2023	16:12:29
14
15
16
17	20/03/2023	15:27:55
18
19
20
21
22	17/03/2023	10:06:37
23	17/03/2023	9:32:21
24
25
26
27
Sheet3

 

[Jay_S1]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Assuming that those dates and times are 'real' dates/times (that is numerical), give this a try with a copy of your data.

Sub ClearDupes()
  Dim d As Object
  Dim a As Variant
  Dim i As Long
 
  Set d = CreateObject("Scripting.Dictionary")
  With Range("M2", Range("N" & Rows.Count).End(xlUp))
    a = .Value
    For i = 1 To UBound(a)
      If d.exists(Format(a(i, 1) + a(i, 2), "ddmmyyhh")) Then a(i, 1) = vbNullString: a(i, 2) = vbNullString
      d(Format(a(i, 1) + a(i, 2), "ddmmyyhh")) = 1
    Next i
    .Value = a
  End With
End Sub

Before:

Jay_S1.xlsm
	M	N
1	Date	Time
2	28/03/2023	9:53:20
3	28/03/2023	11:56:27
4	28/03/2023	9:53:20
5	28/03/2023	11:56:27
6	28/03/2023	11:56:27
7	27/03/2023	13:22:40
8	27/03/2023	14:56:55
9	27/03/2023	15:15:18
10	27/03/2023	13:22:40
11	27/03/2023	14:56:55
12	21/03/2023	15:55:25
13	21/03/2023	16:12:29
14	21/03/2023	15:55:25
15	21/03/2023	16:12:29
16	21/03/2023	15:55:25
17	20/03/2023	15:27:55
18	20/03/2023	15:27:55
19	20/03/2023	15:27:55
20	20/03/2023	15:27:55
21	20/03/2023	15:27:55
22	17/03/2023	10:06:37
23	17/03/2023	9:32:21
24	17/03/2023	10:06:37
25	17/03/2023	9:32:21
26	17/03/2023	10:06:37
27	17/03/2023	9:32:21
Sheet3

After:

Jay_S1.xlsm
	M	N
1	Date	Time
2	28/03/2023	9:53:20
3	28/03/2023	11:56:27
4
5
6
7	27/03/2023	13:22:40
8	27/03/2023	14:56:55
9	27/03/2023	15:15:18
10
11
12	21/03/2023	15:55:25
13	21/03/2023	16:12:29
14
15
16
17	20/03/2023	15:27:55
18
19
20
21
22	17/03/2023	10:06:37
23	17/03/2023	9:32:21
24
25
26
27
Sheet3

Thanks very much for this - it work perfectly. I don't want to push it...but is there an a chance this could be converted to a power query m code and not VBA? I've tried myself and even asked chatgp but failed miserably. On the off chance you can I tried adding a mini sheet
with a bit more detail but it wouldnt let me (so I added a better image). But thanks again for your help.

 

[Jay_S1]

 

[Peter_SSs]

Thanks very much for this - it work perfectly.

You're welcome.

I don't want to push it...but is there an a chance this could be converted to a power query m code and not VBA?

I will leave that for somebody else to address.

I tried adding a mini sheet
with a bit more detail but it wouldnt let me

Exactly what do you mean by "it wouldn't let me"?

 

[alansidman]

As requested

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Time", type time}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Date", "Time"}, {{"Data", each _, type table [Date=nullable date, Time=nullable time]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Data],"Row",1,1)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Row"}, {"Row"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Date1", each if [Row] = 1 then [Date] else null),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Time1", each if [Row]=1 then [Time] else null),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Data"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Removed Columns",{"Date1", "Time1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"Date1", "Date"}, {"Time1", "Time"}})
in
    #"Renamed Columns"