Remove Duplicates in Keeping MAX Numerical/Alphanumeric Value from the Deleted Row's Duplicate

[Pinaceous]

In using Excell 2013 ->

Here is my Data:

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Column1
[/TD]
[TD]C2
[/TD]
[TD]C3
[/TD]
[TD]C4
[/TD]
[TD]C5
[/TD]
[TD]C6
[/TD]
[TD]C7
[/TD]
[TD]C8
[/TD]
[TD]C9
[/TD]
[TD]C10
[/TD]
[TD]C11
[/TD]
[TD]C12
[/TD]
[TD]C13
[/TD]
[TD]C14
[/TD]
[TD]C15
[/TD]
[TD]C16
[/TD]
[TD]C17
[/TD]
[TD]C18
[/TD]
[TD]C19
[/TD]
[TD]C20
[/TD]
[/TR]
[TR]
[TD]XY1559
[/TD]
[TD]ABC
[/TD]
[TD]XY
[/TD]
[TD]1559
[/TD]
[TD]1559
[/TD]
[TD]ABC
[/TD]
[TD]159
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[TD]3
[/TD]
[TD]4
[/TD]
[TD]5
[/TD]
[TD]6
[/TD]
[TD]7
[/TD]
[TD]8
[/TD]
[TD]9
[/TD]
[TD]10
[/TD]
[TD]11
[/TD]
[TD]12
[/TD]
[TD]13
[/TD]
[/TR]
[TR]
[TD]XY1559
[/TD]
[TD][/TD]
[TD]XY
[/TD]
[TD]1559
[/TD]
[TD]1645
[/TD]
[TD]ABC
[/TD]
[TD]250
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]XY1559
[/TD]
[TD][/TD]
[TD]XY
[/TD]
[TD]1559
[/TD]
[TD]1559
[/TD]
[TD]ABC
[/TD]
[TD]159
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


What I want by VBA Code is to 'Delete Duplicates' in using the 'Common Qualifier' in Column 1 in keeping the 'Maximum & Numerical Values' of its predecessor’s deleted rows with the same 'Qualifier'.

So then the Data after Macro execution; will result in the following table:

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Column1
[/TD]
[TD]C2
[/TD]
[TD]C3
[/TD]
[TD]C4
[/TD]
[TD]C5
[/TD]
[TD]C6
[/TD]
[TD]C7
[/TD]
[TD]C8
[/TD]
[TD]C9
[/TD]
[TD]C10
[/TD]
[TD]C11
[/TD]
[TD]C12
[/TD]
[TD]C13
[/TD]
[TD]C14
[/TD]
[TD]C15
[/TD]
[TD]C16
[/TD]
[TD]C17
[/TD]
[TD]C18
[/TD]
[TD]C19
[/TD]
[TD]C20
[/TD]
[/TR]
[TR]
[TD]XY1559
[/TD]
[TD]ABC
[/TD]
[TD]XY
[/TD]
[TD]1559
[/TD]
[TD]1645
[/TD]
[TD]ABC
[/TD]
[TD]250
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[TD]3
[/TD]
[TD]4
[/TD]
[TD]5
[/TD]
[TD]6
[/TD]
[TD]7
[/TD]
[TD]8
[/TD]
[TD]9
[/TD]
[TD]10
[/TD]
[TD]11
[/TD]
[TD]12
[/TD]
[TD]13
[/TD]
[/TR]
</tbody>[/TABLE]


If it seems like such a simple concept for you, I'll really appreciate your wisdom on this one.

Please Help!

R/
-Pin

 

[Pinaceous]

Mick,

If I wanted to add to your code the arrangement of the compiled information according to the ascending values by way of Column G, would this make sense at the end of it?

        Next Dn3

        [.Rows(dict.Count + 1 & ":" & 573).ClearContents

          'Sort by 5th column of the range
         .Sort Key1:=.Range("E1"), Order1:=xlAscending, Header:=xlNo]

    End With

     MsgBox "Run"

 End Sub

Many thanks,

-Pinaceous

 

[Pinaceous]

Hi Mick,

The code then loops to Table 3:-
If values in Table 3 are found in Table 1 but not found in Table 2 then the Max value of column "I" and columns (C to H) of Table 3 are replaced in Table 1.

The other 'rule'/'behavior' I wanted to add or verify is if this Max value can also include Column "G" in addition to Column "I" from the above wording?

It seems like it based on your explanation due to the looping, I just wanted to make sure, it is not clear to me.

Thanks.

R/
-Pinaceous

 

[Pinaceous]

Hi Mick,

Column G is actually the time in 24hrs. So can there be a rule that you could set for two different time values in Column C with a difference of greater than 1200 as being taken then for two different values, regardless of where they are placed in the tables?

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Column C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[TD]I
[/TD]
[TD]J
[/TD]
[TD]K
[/TD]
[TD]L
[/TD]
[TD]M
[/TD]
[TD]N
[/TD]
[TD]O
[/TD]
[TD]P
[/TD]
[TD]Q
[/TD]
[TD]R
[/TD]
[TD]S
[/TD]
[TD]T
[/TD]
[TD]U
[/TD]
[TD]V
[/TD]
[/TR]
[TR]
[TD]XY1559
[/TD]
[TD]ABC
[/TD]
[TD]XY
[/TD]
[TD]1559
[/TD]
[TD]0001
[/TD]
[TD]ABC
[/TD]
[TD]159
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[TD]3
[/TD]
[TD]4
[/TD]
[TD]5
[/TD]
[TD]6
[/TD]
[TD]7
[/TD]
[TD]8
[/TD]
[TD]9
[/TD]
[TD]10
[/TD]
[TD]11
[/TD]
[TD]12
[/TD]
[TD]13
[/TD]
[/TR]
[TR]
[TD]XY1559
[/TD]
[TD]ABC
[/TD]
[TD]XY
[/TD]
[TD]1559
[/TD]
[TD]1559
[/TD]
[TD]ABC
[/TD]
[TD]159
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[TD]3
[/TD]
[TD]4
[/TD]
[TD]5
[/TD]
[TD]6
[/TD]
[TD]7
[/TD]
[TD]8
[/TD]
[TD]9
[/TD]
[TD]10
[/TD]
[TD]11
[/TD]
[TD]12
[/TD]
[TD]13
[/TD]
[/TR]
</tbody>[/TABLE]



-Pin

 

[MickG]

So, If when the code looks at Table 1 & Table 3 for Max value column (I), then if irrespective of the values in Table one being replaced by values in Table 3, if Column "G" ,"Difference" values are greater than 1200 then you would like to see both sets of values in Table 1. ????
Will there ever be a situation where column "C" value will appear more than once in any one of the tables.????

There are so many bits in this requirement that I don't understand. !!!
Example Tables 2 and 3 do not show any data after column (I),. Is there any ???? and is it the same in all Tables, because your latest post shows 2 sets of data with different "G" values one of which , supposedly was taken from Table 3 to update Table 1, which in table 3 shows no values after column "I" !!!!!!!

 

[Pinaceous]

Hi Mick,
I understand that this can be complicated to explain, especially since I screwed up my last posting, of which I do apologize in advance. This is as worse as it gets. I promise. =)
Let me try and explain it better.

Basically;
Column C is the ‘Common Qualifier’ ie. XY1559.
Column G is the time in 24hrs.
Let’s call the produced ‘Table 1’ after the CODE is executed the ‘Result Table’.
Now when talking about; ‘Table 1’, ‘Table 2’ & ‘Table 3’ they represent what’s on the sheet before the CODE is executed.

Code Rule;
Can you set a rule that does not delete the same ‘Common Qualifier’ if it is found to be a difference in time value (regardless of its introduction by Table 1, Table 2 or Table 3) greater than 1200 in being treated as a separate entry?

So the ‘Result Table’ can appear;
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Column C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[TD]I
[/TD]
[TD]J
[/TD]
[TD]K
[/TD]
[TD]L
[/TD]
[TD]M
[/TD]
[TD]N
[/TD]
[TD]O
[/TD]
[TD]P
[/TD]
[TD]Q
[/TD]
[TD]R
[/TD]
[TD]S
[/TD]
[TD]T
[/TD]
[TD]U
[/TD]
[TD]V
[/TD]
[/TR]
[TR]
[TD]XY1559
[/TD]
[TD]ABC
[/TD]
[TD]XY
[/TD]
[TD]1559
[/TD]
[TD]0001
[/TD]
[TD]ABC
[/TD]
[TD]159
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[TD]3
[/TD]
[TD]4
[/TD]
[TD]5
[/TD]
[TD]6
[/TD]
[TD]7
[/TD]
[TD]8
[/TD]
[TD]9
[/TD]
[TD]10
[/TD]
[TD]11
[/TD]
[TD]12
[/TD]
[TD]13
[/TD]
[/TR]
[TR]
[TD]XY1559
[/TD]
[TD]ABC
[/TD]
[TD]XY
[/TD]
[TD]1559
[/TD]
[TD]1732
[/TD]
[TD]ABC
[/TD]
[TD]250
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[TD]3
[/TD]
[TD]4
[/TD]
[TD]5
[/TD]
[TD]6
[/TD]
[TD]7
[/TD]
[TD]8
[/TD]
[TD]9
[/TD]
[TD]10
[/TD]
[TD]11
[/TD]
[TD]12
[/TD]
[TD]13
[/TD]
[/TR]
</tbody>[/TABLE]


Where here there are two XY1559’s represented in the ‘Result Table’ because Column G was greater than a difference of 1200, so it treated them as two separate entries.

To answer your questions;

So, If when the code looks at Table 1 & Table 3 for Max value column (I), then if irrespective of the values in Table one being replaced by values in Table 3, if Column "G" ,"Difference" values are greater than 1200 then you would like to see both sets of values in Table 1. ????

So, yes, when the code is looking at Table 1 & Table 3 for the Max values for Column (I), it will do so, but if there is a difference in value greater than 1200 by Column G, it will represent them as two separate entries being represented in the ‘Result Table’ above.

Example Tables 2 and 3 do not show any data after column (I),. Is there any ????

There is actually no posted data after column (I) for both Table 2 & Table 3. There is data posted after column (I) for Table 1, this is due to the user.


I hope that this clarifies this particular ‘rule’.

Many continued thanks, & I have full confidence in you, that you can do it!

R/
Pinaceous

 

[MickG]

Give this a try:-
Results on sheet2

[COLOR=Navy]Sub[/COLOR] MG30Apr11
'[COLOR=Green][B]Option2[/B][/COLOR]
[COLOR=Navy]Dim[/COLOR] Rng1 [COLOR=Navy]As[/COLOR] Range, Dn [COLOR=Navy]As[/COLOR] Range, n [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] t, Dn2 [COLOR=Navy]As[/COLOR] Range, Dn3 [COLOR=Navy]As[/COLOR] Range
[COLOR=Navy]Dim[/COLOR] Rng2 [COLOR=Navy]As[/COLOR] Range, Rng3 [COLOR=Navy]As[/COLOR] Range, Rng4 [COLOR=Navy]As[/COLOR] Range, Fd [COLOR=Navy]As[/COLOR] Boolean, Q [COLOR=Navy]As[/COLOR] Variant
[COLOR=Navy]Set[/COLOR] Rng1 = Range("C2:C202")
[COLOR=Navy]Set[/COLOR] Rng2 = Range("C203:C402")
[COLOR=Navy]Set[/COLOR] Rng3 = Range("C403:C503")


[COLOR=Navy]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] Dn [COLOR=Navy]In[/COLOR] Rng1
    [COLOR=Navy]If[/COLOR] Not IsEmpty(Dn.Value) [COLOR=Navy]Then[/COLOR]
        .Item(Dn.Value) = Array(Dn, Fd)
    [COLOR=Navy]End[/COLOR] If
[COLOR=Navy]Next[/COLOR] Dn


[COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] Dn2 [COLOR=Navy]In[/COLOR] Rng2
   [COLOR=Navy]If[/COLOR] .Exists(Dn2.Value) [COLOR=Navy]Then[/COLOR]
        Q = .Item(Dn2.Value)
        [COLOR=Navy]If[/COLOR] Abs(Dn2.Offset(, 4) - Q(0).Offset(, 4)) > 1200 [COLOR=Navy]Then[/COLOR]
           [COLOR=Navy]Set[/COLOR] Q(0) = Union(Q(0), Dn2)
        [COLOR=Navy]Else[/COLOR]
            [COLOR=Navy]Set[/COLOR] Q(0) = Dn2
            Q(1) = True
        [COLOR=Navy]End[/COLOR] If
       .Item(Dn2.Value) = Q
    [COLOR=Navy]End[/COLOR] If
[COLOR=Navy]Next[/COLOR] Dn2


[COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] Dn3 [COLOR=Navy]In[/COLOR] Rng3
   [COLOR=Navy]If[/COLOR] Not IsEmpty(Dn3.Value) [COLOR=Navy]Then[/COLOR]
       [COLOR=Navy]If[/COLOR] .Exists(Dn3.Value) [COLOR=Navy]Then[/COLOR]
            Q = .Item(Dn3.Value)
                 [COLOR=Navy]If[/COLOR] Abs(Dn3.Offset(, 4) - Q(0).Offset(, 4)) > 1200 [COLOR=Navy]Then[/COLOR]
                          [COLOR=Navy]Set[/COLOR] Q(0) = Union(Q(0), Dn3)
                 [COLOR=Navy]ElseIf[/COLOR] Dn3.Offset(, 6) > Q(0).Offset(, 6) And Q(1) = False [COLOR=Navy]Then[/COLOR]
                        [COLOR=Navy]Set[/COLOR] Q(0) = Dn3
                [COLOR=Navy]End[/COLOR] If
          .Item(Dn3.Value) = Q
      [COLOR=Navy]End[/COLOR] If
    [COLOR=Navy]End[/COLOR] If
[COLOR=Navy]Next[/COLOR] Dn3


[COLOR=Navy]Dim[/COLOR] K [COLOR=Navy]As[/COLOR] Variant, R [COLOR=Navy]As[/COLOR] Range, c [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long[/COLOR]
c = 1
[COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] K [COLOR=Navy]In[/COLOR] .keys
    [COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] R [COLOR=Navy]In[/COLOR] .Item(K)(0)
        c = c + 1
        [COLOR=Navy]For[/COLOR] n = 1 To 9
            Sheets("Sheet2").Cells(c, n) = R.Offset(, n - 3)
        [COLOR=Navy]Next[/COLOR] n
    [COLOR=Navy]Next[/COLOR] R
[COLOR=Navy]Next[/COLOR] K
MsgBox "Run"
[COLOR=Navy]End[/COLOR] [COLOR=Navy]With[/COLOR]
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]

Regards Mick

 

[Pinaceous]

Hi Mick!

It looks like it works really GREAT!

The only thing is that I need the data to remain on the present Sheet, in my case being Sheet 5, where the table information is being imported & then exported (I got this part in another macro).

I'm requesting that at the end of your 'CODE', if you could order the arrangement of the compiled data, of which produces the 'Results Table' according to the ascending values by way of Column G (the time in 24hrs) & in therefore clearing the contents of all the previous tables extrapolated data.

Congratulations on a great job! =)

Thank you,
-Pinaceous

 

[Pinaceous]

Hi Mick,

I'm actually starting another thread, of which I'm inviting you to participate.

Question 1. Remove Duplicates in Keeping Numerical/Alphanumeric Value from a Specific Deleted Row's Duplicate Range

It approaches this whole situation differently, and to much relief it is much more clearer to understand, as I broke it down to two basic rules w/o Max Value compensation.

Hope to hear your comments there, & many thanks for your help with this.

R/
Pin