Removing conditional duplicate rows using comparative data from multiple columns

[alamar59]

Hi experts I am new to vba and looking for a way to modify data in a large file (extract below) as explained below.

[TABLE="class: grid, width: 651"]
<tbody>[TR]
[TD]Record ref[/TD]
[TD]Date created[/TD]
[TD]Type[/TD]
[TD]Stage[/TD]
[TD]Status[/TD]
[TD]Department[/TD]
[TD]Client[/TD]
[TD]Cost[/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD]AAM000001[/TD]
[TD]01/04/2015[/TD]
[TD]APP[/TD]
[TD]Closed - Funded[/TD]
[TD]Application v1 - Approved[/TD]
[TD]CEEEE[/TD]
[TD]FUN00023[/TD]
[TD]487,993.00[/TD]
[TD]436,692.00[/TD]
[/TR]
[TR]
[TD]AAM000001[/TD]
[TD]01/04/2015[/TD]
[TD]AWA[/TD]
[TD]Closed - Funded[/TD]
[TD]Award - Approved[/TD]
[TD]CEEEE[/TD]
[TD]FUN00023[/TD]
[TD]354,714.00[/TD]
[TD]354,714.00[/TD]
[/TR]
[TR]
[TD]AAM000004[/TD]
[TD]20/04/2015[/TD]
[TD]APP[/TD]
[TD]Closed - Not Pursuing[/TD]
[TD]Application v1 - Draft[/TD]
[TD]CEEEE[/TD]
[TD]FUN00016[/TD]
[TD]126,635.31[/TD]
[TD]101,308.23[/TD]
[/TR]
[TR]
[TD]AAM000005[/TD]
[TD]29/04/2015[/TD]
[TD]APP[/TD]
[TD]Closed - Funded[/TD]
[TD]Application v1 - Approved[/TD]
[TD]CEIRR[/TD]
[TD]FUN00001[/TD]
[TD]372,240.30[/TD]
[TD]284,203.87[/TD]
[/TR]
[TR]
[TD]AAM000005[/TD]
[TD]29/04/2015[/TD]
[TD]AWA[/TD]
[TD]Closed - Funded[/TD]
[TD]Award - Approved[/TD]
[TD]CEIRR[/TD]
[TD]FUN00001[/TD]
[TD]372,240.30[/TD]
[TD]284,203.87[/TD]
[/TR]
[TR]
[TD]AAM000006[/TD]
[TD]29/04/2015[/TD]
[TD]APP[/TD]
[TD]Closed - Funded[/TD]
[TD]Application v1 - Approved[/TD]
[TD]CEEEE[/TD]
[TD]FUN00016[/TD]
[TD]65,274.56[/TD]
[TD]85,359.34[/TD]
[/TR]
[TR]
[TD]AAM000006[/TD]
[TD]29/04/2015[/TD]
[TD]APP[/TD]
[TD]Closed - Funded[/TD]
[TD]Application v1 - Approved[/TD]
[TD]CEEEE[/TD]
[TD]FUN00363[/TD]
[TD]31,296.44[/TD]
[TD]42,042.66[/TD]
[/TR]
[TR]
[TD]AAM000006[/TD]
[TD]29/04/2015[/TD]
[TD]AWA[/TD]
[TD]Closed - Funded[/TD]
[TD]Award - Approved[/TD]
[TD]CEEEE[/TD]
[TD]FUN00016[/TD]
[TD]65,274.56[/TD]
[TD]85,359.34[/TD]
[/TR]
[TR]
[TD]AAM000006[/TD]
[TD]29/04/2015[/TD]
[TD]AWA[/TD]
[TD]Closed - Funded[/TD]
[TD]Award - Approved[/TD]
[TD]CEEEE[/TD]
[TD]FUN00363[/TD]
[TD]31,296.44[/TD]
[TD]42,042.66[/TD]
[/TR]
[TR]
[TD]AAM000007[/TD]
[TD]11/05/2015[/TD]
[TD]APP[/TD]
[TD]Closed - Rejected[/TD]
[TD]Application v1 - Approved[/TD]
[TD]DTARC[/TD]
[TD]FUN00018[/TD]
[TD]30,244.79[/TD]
[TD]23,947.48[/TD]
[/TR]
[TR]
[TD]AAM000008[/TD]
[TD]11/05/2015[/TD]
[TD]APP[/TD]
[TD]Closed - Rejected[/TD]
[TD]Application v2 - Approved[/TD]
[TD]DTARC[/TD]
[TD]FUN00001[/TD]
[TD]933,624.73[/TD]
[TD]466,737.01[/TD]
[/TR]
[TR]
[TD]AAM000009[/TD]
[TD]18/05/2015[/TD]
[TD]APP[/TD]
[TD]Closed - Rejected[/TD]
[TD]Application v2 - Not approved[/TD]
[TD]ASPHM[/TD]
[TD]ZZZTST01[/TD]
[TD]0.00[/TD]
[TD]0.00[/TD]
[/TR]
[TR]
[TD]AAM000010[/TD]
[TD]18/05/2015[/TD]
[TD]APP[/TD]
[TD]Closed - Rejected[/TD]
[TD]Application v1 - Approved[/TD]
[TD]EDICE[/TD]
[TD]FUN00377[/TD]
[TD]10,078.24[/TD]
[TD]9,956.92[/TD]
[/TR]
[TR]
[TD]AAM000011[/TD]
[TD]18/05/2015[/TD]
[TD]APP[/TD]
[TD]Closed - Rejected[/TD]
[TD]Application v1 - Approved[/TD]
[TD]DTDES[/TD]
[TD]FUN00052[/TD]
[TD]56,861.24[/TD]
[TD]9,985.30[/TD]
[/TR]
[TR]
[TD]AAM000012[/TD]
[TD]18/05/2015[/TD]
[TD]APP[/TD]
[TD]Closed - Rejected[/TD]
[TD]Application v1 - Approved[/TD]
[TD]DTARC[/TD]
[TD]FUN00052[/TD]
[TD]20,066.58[/TD]
[TD]9,800.00[/TD]
[/TR]
[TR]
[TD]AAM000013[/TD]
[TD]18/05/2015[/TD]
[TD]APP[/TD]
[TD]Closed - Rejected[/TD]
[TD]Application v1 - Approved[/TD]
[TD]ASCBS[/TD]
[TD]FUN00058[/TD]
[TD]357,239.19[/TD]
[TD]206,933.00[/TD]
[/TR]
[TR]
[TD]AAM000014[/TD]
[TD]20/05/2015[/TD]
[TD]APP[/TD]
[TD]Closed - Funded[/TD]
[TD]Application v1 - Approved[/TD]
[TD]HHBEH[/TD]
[TD]FUN00017[/TD]
[TD]1,963.54[/TD]
[TD]1,108.00[/TD]
[/TR]
[TR]
[TD]AAM000014[/TD]
[TD]20/05/2015[/TD]
[TD]AWA[/TD]
[TD]Closed - Funded[/TD]
[TD]Award - Approved[/TD]
[TD]HHBEH[/TD]
[TD]FUN00017[/TD]
[TD]1,963.54[/TD]
[TD]600.00[/TD]
[/TR]
[TR]
[TD]AAM000015[/TD]
[TD]20/05/2015[/TD]
[TD]APP[/TD]
[TD]Closed - Rejected[/TD]
[TD]Application v1 - Approved[/TD]
[TD]MHHEL[/TD]
[TD]FUN00052[/TD]
[TD]50,636.68[/TD]
[TD]10,000.00[/TD]
[/TR]
[TR]
[TD]AAM000016[/TD]
[TD]22/05/2015[/TD]
[TD]APP[/TD]
[TD]Closed - Rejected[/TD]
[TD]Application v1 - Approved[/TD]
[TD]CEEEE[/TD]
[TD]FUN00023[/TD]
[TD]672,954.64[/TD]
[TD]538,363.67[/TD]
[/TR]
[TR]
[TD]AAM000017[/TD]
[TD]27/05/2015[/TD]
[TD]APP[/TD]
[TD]Closed - Rejected[/TD]
[TD]Application v3 - Approved[/TD]
[TD]ASBIO[/TD]
[TD]FUN00001[/TD]
[TD]290,926.26[/TD]
[TD]270,316.25[/TD]
[/TR]
[TR]
[TD]AAM000018[/TD]
[TD]27/05/2015[/TD]
[TD]APP[/TD]
[TD]Closed - Rejected[/TD]
[TD]Application v1 - Approved[/TD]
[TD]CECPT[/TD]
[TD]FUN00001[/TD]
[TD]212,434.00[/TD]
[TD]178,076.00[/TD]
[/TR]
[TR]
[TD]AAM000019[/TD]
[TD]28/05/2015[/TD]
[TD]APP[/TD]
[TD]Closed - Funded[/TD]
[TD]Application v1 - Approved[/TD]
[TD]CEIRR[/TD]
[TD]FUN00378[/TD]
[TD]55,816.96[/TD]
[TD]44,747.38[/TD]
[/TR]
[TR]
[TD]AAM000019[/TD]
[TD]28/05/2015[/TD]
[TD]AWA[/TD]
[TD]Closed - Funded[/TD]
[TD]Award - Approved[/TD]
[TD]CEIRR[/TD]
[TD]FUN00378[/TD]
[TD]55,816.96[/TD]
[TD]44,666.44[/TD]
[/TR]
[TR]
[TD]AAM000020[/TD]
[TD]02/06/2015[/TD]
[TD]AWA[/TD]
[TD]Closed - Funded[/TD]
[TD]Award - Approved[/TD]
[TD]CECOM[/TD]
[TD]FUN00379[/TD]
[TD]56,990.94[/TD]
[TD]56,990.94[/TD]
[/TR]
[TR]
[TD]AAM000020[/TD]
[TD]02/06/2015[/TD]
[TD]APP[/TD]
[TD]Closed - Funded[/TD]
[TD]Application v1 - Approved[/TD]
[TD]CECOM[/TD]
[TD]FUN00379[/TD]
[TD]56,990.94[/TD]
[TD]56,990.94[/TD]
[/TR]
[TR]
[TD]AAM000021[/TD]
[TD]02/06/2015[/TD]
[TD]APP[/TD]
[TD]Closed - Funded[/TD]
[TD]Application v1 - Approved[/TD]
[TD]HHACG[/TD]
[TD]FUN00382[/TD]
[TD]9,395.21[/TD]
[TD]9,080.45[/TD]
[/TR]
[TR]
[TD]AAM000021[/TD]
[TD]02/06/2015[/TD]
[TD]AWA[/TD]
[TD]Closed - Funded[/TD]
[TD]Award - Approved[/TD]
[TD]HHACG[/TD]
[TD]FUN00382[/TD]
[TD]9,395.21[/TD]
[TD]10,000.00[/TD]
[/TR]
[TR]
[TD]AAM000022[/TD]
[TD]03/06/2015[/TD]
[TD]APP[/TD]
[TD]Open - In Development[/TD]
[TD]Application v1 - Draft[/TD]
[TD]HHRES[/TD]
[TD]FUN00001[/TD]
[TD]244,069.96[/TD]
[TD]199,353.94[/TD]
[/TR]
[TR]
[TD]AAM001090[/TD]
[TD]04/01/2017[/TD]
[TD]OUT[/TD]
[TD]Open - Contract Pending[/TD]
[TD]Outline v1 - Approved[/TD]
[TD]HHRES[/TD]
[TD]FUN00397[/TD]
[TD]502,192.29[/TD]
[TD]487,370.97[/TD]
[/TR]
[TR]
[TD]AAM001090[/TD]
[TD]04/01/2017[/TD]
[TD]APP[/TD]
[TD]Open - Contract Pending[/TD]
[TD]Application v3 - Approved[/TD]
[TD]HHRES[/TD]
[TD]FUN00397[/TD]
[TD]579,796.81[/TD]
[TD]566,722.65[/TD]
[/TR]
[TR]
[TD]AAM001090[/TD]
[TD]04/01/2017[/TD]
[TD]AWA[/TD]
[TD]Open - Contract Pending[/TD]
[TD]Award - Approved[/TD]
[TD]HHRES[/TD]
[TD]FUN00397[/TD]
[TD]579,796.81[/TD]
[TD]566,696.18[/TD]
[/TR]
[TR]
[TD]AAM001149[/TD]
[TD]03/02/2017[/TD]
[TD]APP[/TD]
[TD]Open - Submitted to funder[/TD]
[TD]Application v2 - Draft[/TD]
[TD]BSLHM[/TD]
[TD]FUN00862[/TD]
[TD]568,131.74[/TD]
[TD]568,131.74[/TD]
[/TR]
[TR]
[TD]AAM001149[/TD]
[TD]03/02/2017[/TD]
[TD]APP[/TD]
[TD]Open - Submitted to funder[/TD]
[TD]Application v2 - Draft[/TD]
[TD]READM[/TD]
[TD]FUN00862[/TD]
[TD]238,483.00[/TD]
[TD]238,326.00[/TD]
[/TR]
</tbody>[/TABLE]

I am needing some VBA code that I can apply to the above spreadsheet to accomplish the following:

1. Search column 1 (record ref) and identify duplicates
2. Check column 6 (Department) and column 7 (Client) to confirm if the records are actually duplicates
3. If department and client have same value in their respective columns then consider the rows to be duplicates and delete the row(s) with the following rules using the Type column: Delete duplicate columns where (a) “AWA” has the highest priority to remain in the table, then “APP” then “OUT”.

So in the case of Record AAM000001 the first row containing Type APP would be deleted by the vba code.
In the case of AAM001090 the rows with Types OUT and APP would be deleted.
In the case of AAM000006 the two rows containing Type APP would be deleted as there are two different client numbers
In the case of AAM001149 no rows would be deleted as there are two different Department labels.

 

[ravishankar]

Excel has built in function to remove duplicates with your specifications. Goto Data menu > Data tools > Delete duplicates > choose columns and headers > try it on a copy of your workbook in case data is lost due to wrong choices.
If you still need a macro, we shall work on it.
Ravi shankar

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG23Sep27
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] txt [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] Typ [COLOR="Navy"]As[/COLOR] Variant, T [COLOR="Navy"]As[/COLOR] Variant, Rr [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] K [COLOR="Navy"]As[/COLOR] Variant, nRng [COLOR="Navy"]As[/COLOR] Range, Num [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] R [COLOR="Navy"]As[/COLOR] Range, Typs [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] Nam [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    txt = Join(Application.Index(Dn.Resize(, 7).Value, 0, Array(1, 6, 7)), ",")
        [COLOR="Navy"]If[/COLOR] Not .Exists(txt) [COLOR="Navy"]Then[/COLOR]
            .Add txt, Dn
        [COLOR="Navy"]Else[/COLOR]
            [COLOR="Navy"]Set[/COLOR] .Item(txt) = Union(.Item(txt), Dn)
        [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
Typ = Array("AWA", "APP", "OUT")
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] .keys
  Typs = "": Nam = ""
  [COLOR="Navy"]If[/COLOR] .Item(K).Count > 1 [COLOR="Navy"]Then[/COLOR]
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] T [COLOR="Navy"]In[/COLOR] Typ
        [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Rr [COLOR="Navy"]In[/COLOR] .Item(K).Offset(, 2)
            Typs = Typs & IIf(Typs = "", Rr, "," & Rr)
        [COLOR="Navy"]Next[/COLOR] Rr
        
        [COLOR="Navy"]If[/COLOR] InStr(Typs, T) [COLOR="Navy"]Then[/COLOR]
            Nam = T
            [COLOR="Navy"]Exit[/COLOR] For
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] T
        
        [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] R [COLOR="Navy"]In[/COLOR] .Item(K).Offset(, 2)
            [COLOR="Navy"]If[/COLOR] Not R = Nam [COLOR="Navy"]Then[/COLOR]
                [COLOR="Navy"]If[/COLOR] nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR] [COLOR="Navy"]Set[/COLOR] nRng = R Else [COLOR="Navy"]Set[/COLOR] nRng = Union(nRng, R)
            [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]Next[/COLOR] R
        
 [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] K
[COLOR="Navy"]End[/COLOR] With
 [COLOR="Navy"]If[/COLOR] Not nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR] nRng.EntireRow.Delete
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Fluff]

Another option

Sub DeleteDupes()
   Dim Cl As Range, Rng As Range
   Dim Txt As String
   
   With CreateObject("scripting.dictionary")
      For Each Cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
         Txt = Join(Application.Index(Cl.Resize(, 7).Value, 0, Array(1, 6, 7)), "|")
         If Not .Exists(Txt) Then
            .Add Txt, Array(Cl.Offset(, 2).Value, Cl)
         ElseIf InStr(1, "AWA|APP|OUT", Cl.Offset(, 2).Value) < InStr(1, "AWA|APP|OUT", .Item(Txt)(0)) Then
            If Rng Is Nothing Then Set Rng = .Item(Txt)(1) Else Set Rng = Union(Rng, .Item(Txt)(1))
            .Item(Txt) = Array(Cl.Offset(, 2).Value, Cl)
         Else
            If Rng Is Nothing Then Set Rng = Cl Else Set Rng = Union(Rng, Cl)
         End If
      Next Cl
   End With
   If Not Rng Is Nothing Then Rng.EntireRow.Delete
End Sub

 

[alamar59]

Hi ravishankar - Thank you for suggestion but it didn't work as the duplicates are not exact.

 

[alamar59]

Hi Mick thank you this did work

 

[alamar59]

Hi Fluff This worked as well. Thanks for taking the time to share.

 

[MickG]

You're welcome