VBA to Delete Duplicate Info on Same Date

[BigDawg15]

Hello,

Please see the example below for two dates showing the teams who are playing on those dates. Column I numbers show the team matchups
that are duplicates. I am looking for a macro that would delete the second team matchup for each date. Column I was done manually to give
examples of matches.

Any help or ideas would be appreciated.

Thanks in advance,

BigDawg15

Also posted at:

[SOLVED:] VBA to Delete Duplicate Info on Same Date

Hello, Attached is a workbook with an example for two dates showing the teams who are playing on those dates. The far right column numbers show the team matchups that are duplicates. I am looking for a macro that would delete the second team matchup for each date. Any help or ideas...

www.vbaexpress.com

Dup Teams Example.xlsx
	A	B	C	D	E	F	G	H	I
1	Ref	Date	Team 1	Info	Display Activity	Division	Team 2	Game
2	44436_1	8/28/2021	Illinois		Illinois	Big Ten	Nebraska	Illinois vs Nebraska	1
3	44436_2	8/28/2021	Nebraska		Nebraska	Big Ten	Illinois	Nebraska vs Illinois	1
4	44436_3	8/28/2021	UTEP		UTEP	FBS Independents	New Mexico State	UTEP vs New Mexico State	2
5	44436_4	8/28/2021	Connecticut		Connecticut	Mountain West	Fresno State	Connecticut vs Fresno State	3
6	44436_5	8/28/2021	New Mexico State		New Mexico State	Conference USA	UTEP	New Mexico State vs UTEP	2
7	44436_6	8/28/2021	Fresno State		Fresno State	FBS Independents	UConn	Fresno State vs UConn	3
8	44436_7	8/28/2021	Hawai'i		Hawai'i	Pac-12	UCLA	Hawai'i vs UCLA	4
9	44436_8	8/28/2021	San José State		San José State	Big Sky	Southern Utah	San José State vs Southern Utah	9
10	44436_9	8/28/2021	UCLA		UCLA	Mountain West	Hawai'i	UCLA vs Hawai'i	4
11	44442_1	9/3/2021	Duke		Duke	Conference USA	Charlotte	Duke vs Charlotte	5
12	44442_2	9/3/2021	North Carolina		North Carolina	ACC	Virginia Tech	North Carolina vs Virginia Tech	6
13	44442_3	9/3/2021	Virginia Tech		Virginia Tech	ACC	North Carolina	Virginia Tech vs North Carolina	6
14	44442_4	9/3/2021	Wake Forest		Wake Forest	Conference USA	Old Dominion	Wake Forest vs Old Dominion	7
15	44442_5	9/3/2021	Kansas		Kansas	Missouri Valley	South Dakota	Kansas vs South Dakota	10
16	44442_6	9/3/2021	Michigan State		Michigan State	Big Ten	Northwestern	Michigan State vs Northwestern	8
17	44442_7	9/3/2021	Northwestern		Northwestern	Big Ten	Michigan State	Northwestern vs Michigan State	8
18	44442_8	9/3/2021	Charlotte		Charlotte	ACC	Duke	Charlotte vs Duke	5
19	44442_9	9/3/2021	Old Dominion		Old Dominion	ACC	Wake Forest	Old Dominion vs Wake Forest	7
20	44442_10	9/3/2021	Notre Dame		Notre Dame	ACC	Florida State	Notre Dame vs Florida State	8
21	44442_11	9/3/2021	Eastern Michigan		Eastern Michigan	Northeast	St Francis (PA)	Eastern Michigan vs St Francis (PA)	11
22	44442_12	9/3/2021	Colorado State		Colorado State	Missouri Valley	South Dakota State	Colorado State vs South Dakota State	12
23	44442_13	9/3/2021	Colorado		Colorado	Big Sky	Northern Colorado	Colorado vs Northern Colorado	13
24	44442_14	9/3/2021	Florida State		Florida State	FBS Independents	Notre Dame	Florida State vs Notre Dame	8
Sheet1

Cell Formulas
Range	Formula
E2:E24	E2	=IF(ISBLANK(B2),"",C2)
H2:H24	H2	=C2 & " vs " & G2

 

[DanteAmor]

In your example all duplicates can be "removed" except for this game, since "Connecticut" does not have the same name in the second game.

varios 05jun2021.xlsm
	A	B	C	D	E	F	G	H	I
1	Ref	Date	Team 1	Info	Display Activity	Division	Team 2	Game
2	44436_4	8/28/2021	Connecticut		Connecticut	Mountain West	Fresno State	Connecticut vs Fresno State	3
3	44436_6	8/28/2021	Fresno State		Fresno State	FBS Independents	UConn	Fresno State vs UConn	3
Hoja6

Try the following macro. The results of the unique games will be on sheet2 from cell A2.

Sub Delete_Matchup()
  Dim dic As Object
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, k As Long
  Dim game1 As String, game2 As String
  
  Set dic = CreateObject("Scripting.Dictionary")
  a = Sheets("Sheet1").Range("A2", Sheets("Sheet1").Range("H" & Rows.Count).End(3)).Value
  ReDim b(1 To UBound(a, 1), 1 To UBound(a, 2))
  
  For i = 1 To UBound(a, 1)
    game1 = a(i, 2) & "|" & a(i, 3) & "|" & a(i, 7)
    game2 = a(i, 2) & "|" & a(i, 7) & "|" & a(i, 3)
    If Not dic.exists(game1) And Not dic.exists(game2) Then
      dic(game1) = Empty
      k = k + 1
      For j = 1 To UBound(a, 2)
        b(k, j) = a(i, j)
      Next
    End If
  Next
  
  Sheets("Sheet2").Range("A2").Resize(k, UBound(b, 2)).Value = b
End Sub

 

[BigDawg15]

Dante,

Thank you for the reply. I want to keep one of the duplicates, and any that do not have a duplicate. So in my example, numbers 1, 2, 3 each have a duplicate.
I would like to remove one of them and keep the other. Doesn't matter which one is deleted. I also want to keep 10, 11, 12, & 13 because they do not have
a duplicate.

Thank you again for assistance.

BigDawg15

 

[DanteAmor]

Did you try the macro?

 

[BigDawg15]

At first I did not try the macro. I was going by what you had written. It did not sound like you had understood my request. I have now tried it and it seems to
be working as requested.

Thank you very much for your time and help,

Regards,

BigDawg15

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.

 

[alidroos]

I want to modify the code so that there are two columns of values that I want to sum their values for the duplicate
The result is as follows:

1000024	18-08-2021	10	1	2600	133203
1000024	12-08-2021	20	2	2600	1779099
1000024	17-06-2021	2	1	2600	7785743

​