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

[Pinaceous]

In using Excell 2013 ->

Here is my Data:

Column 1 | C2 | C3 | C4 | C5 | C6 | C7 |C8| C9 |C10|C11|C12|C13|C14|C15|C16|C17|C18|C19| C20|

XY 1559 | ABC | XY | 1559 | 1559 | ABC | 159 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11| 12 | 13 |

XY 1559 | | XY | 1559 | 1645 | ABC | 250 | | | | | | | | | | | | | |

XY 1559 | | XY | 1559 | 1559 | ABC | 159 | | | | | | | | | | | | | |


________________________________________________

What I would theoretically like; by way of VBA Method is to Delete Duplicates by way of the Common Qualifier in Column 1 in keeping the Maximum Numerical Values of its predecessor’s rows with the same like Qualifier.

So then the Data will look after Macro execution; Control + Shift + Whatever, to then look like this below, in following this example for a large data base:</SPAN>


Column 1| C2 | C3 | C4 | C5 | C6 | C7 |C8|C9|C10|C11|C12|C13|C14|C15|C16|C17|C18|C19|C20|

XY 1559 | ABC | XY | 1559 | 1645 | ABC | 250 | 1 | 2| 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 |



It seems like such a simple concept, but I'm getting no success with this one. Please Help!

 

[Marcelo Branco]

See if this does what you need

Sub bTest()
    Dim i As Long, j As Long, MaxCol5 As Long
    Dim vData As Variant, v As Variant, lin As Long
    Dim dict As Object
    
    Set dict = CreateObject("Scripting.Dictionary")
    dict.comparemode = vbTextCompare
    
    With Sheets("Sheet1").Range("C2:V573")
        vData = .Value
        
        For i = 1 To .Rows.Count
            If vData(i, 1) <> "" Then
                MaxCol5 = Evaluate("=MAX(IF(C2:C573=""" & vData(i, 1) & """,0+G2:G573))")
                vData(i, 1) = IIf(MaxCol5 - Val(vData(i, 5)) > 1200, Trim(vData(i, 1)), Trim(vData(i, 1)) & " ")
                If dict.Exists(vData(i, 1)) Then
                    For j = 2 To .Columns.Count
                        If .Cells(i, j) > dict.Item(vData(i, 1)).Cells(1, j) Then _
                            dict.Item(vData(i, 1)).Cells(1, j) = .Cells(i, j)
                    Next j
                Else
                    dict.Add vData(i, 1), .Rows(i).Cells
                End If
            End If
        Next i
                
        For Each v In dict.keys
            lin = lin + 1
            .Range("A" & lin).Resize(, .Columns.Count).Value = dict.Item(v).Cells.Value
        Next v
        
        .Rows(dict.Count + 1 & ":" & 573).ClearContents
    End With
End Sub

M.

 

[Pinaceous]

Hi M.

It looks like the trial runs that I ran are 100% accurate and successful! I'll have to KIT an let you know my progress.

Your Codes Are Second To None!

Keep on writing the good fight!

All the best!

R/
-P

 

[Marcelo Branco]

Good news!

Thank you for the kind words.

M.

 

[Pinaceous]

Hi M,

Do you think you can add an extra function to this code, at the end, to then finally sort by ascending value, from smallest to largest according to the values of Column 5(G) expanded selection to all of the columns present, or will I better off just recording a macro for that?

Thanks,

-P

 

[Marcelo Branco]

You can use the macro recorder.

M.

 

[Pinaceous]

Ok,
Thank you!

 

[Pinaceous]

Hi Marco,

How's it going? Your code works beautifully.

I just would like to know how I would extend the range to be C2:V604?

I tried it myself but I am unsuccessful in all my attempts.

I'd really appreciate, if you could show me the light.

Many thanks in advance.

R/
-P

 

[Pinaceous]

Hi Marcelo,

What I wanted to ask you, is if you can modify this code to include the following rule:

Where, if and only if, there are values specifically posted for Column(s) G & I; between Rows 201-401; they are to over ride the MAX VALUE rule and be the VALUES taken; regardless if the VALUES are LESS or MORE then all the other Rows (being then 1-200;402-573).

I know, its be awhile, and your help would really be very much appreciated.

Thanks Marcelo, in advance.

R/
Pinaceous

 

[Pinaceous]

Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+Shift+D
'


      Dim i As Long, j As Long, MaxCol5 As Long
    Dim vData As Variant, v As Variant, lin As Long
    Dim dict As Object
    
    Set dict = CreateObject("Scripting.Dictionary")
    dict.comparemode = vbTextCompare
    
    With Sheets("Sheet1").Range("C2:Z573")
        vData = .Value
        
        For i = 1 To .Rows.Count
            If vData(i, 1) <> "" Then
                MaxCol5 = Evaluate("=MAX(IF(Sheet1!C2:C573=""" & vData(i, 1) & _
                    """,IF(ISNUMBER(0+Sheet1!G2:G573),0+Sheet1!G2:G573)))")
                vData(i, 1) = IIf(MaxCol5 - Val(vData(i, 5)) > 1200, Trim(vData(i, 1)), Trim(vData(i, 1)) & " ")
                If dict.Exists(vData(i, 1)) Then
                    For j = 2 To .Columns.Count
                        If .Cells(i, j) > dict.Item(vData(i, 1)).Cells(1, j) Then _
                            dict.Item(vData(i, 1)).Cells(1, j) = .Cells(i, j)
                    Next j
                Else
                    dict.Add vData(i, 1), .Rows(i).Cells
                End If
            End If
        Next i
                
        For Each v In dict.keys
            lin = lin + 1
            .Range("A" & lin).Resize(, .Columns.Count).Value = dict.Item(v).Cells.Value
        Next v
        
        .Rows(dict.Count + 1 & ":" & 573).ClearContents
        
        'Sort by 5th column of the range
        .Sort Key1:=.Range("E1"), Order1:=xlAscending, Header:=xlNo
    End With
    
 
 End Sub

 

[Pinaceous]

Hi Marcelo,

I've posted several threads, to see if I someone can come close to scripting &/or understanding what I want and I'm getting nowhere.

I'm posting this anyway, to see maybe if I can regain your invaluable input.


There is one variation on this rule pertaining to the common qualifier found, I’d like for you to write into your code of which I’d like to explain by the following tables:

Representing Data From:<o></o> Column C<o></o> D<o></o> E<o></o> F<o></o> G<o></o> H<o></o> I<o></o> J<o></o> K<o></o> L<o></o> M<o></o> N<o></o> O<o></o> P<o></o> Q<o></o> R<o></o> S<o></o> T<o></o> U<o></o> V<o></o> Rows.2-172<o></o> XY155<o></o> ABC<o></o> XY<o></o> 155<o></o> 1559<o></o> ABC<o></o> 259<o></o> 1<o></o> 2<o></o> 3<o></o> 4<o></o> 5<o></o> 6<o></o> 7<o></o> 8<o></o> 9<o></o> 10<o></o> 11<o></o> 12<o></o> 13<o></o> Rows.2-172<o></o> AB199<o></o> CSE<o></o> AB<o></o> 199<o></o> 1845<o></o> CSE<o></o> 250<o></o> 14<o></o> 15<o></o> 16<o></o> 17<o></o> 18<o></o> 19<o></o> 20<o></o> 21<o></o> 22<o></o> 23<o></o> 24<o></o> 25<o></o> 26<o></o> Rows.173-372<o></o> XY155<o></o> XY<o></o> 155<o></o> 1459<o></o> ABC<o></o> 159<o></o> Rows.173-372<o></o> AB199<o></o> AB<o></o> 199<o></o> 1945<o></o> CSE<o></o> 350<o></o> Rows.373-573<o></o> XY155<o></o> XY<o></o> 155<o></o> 1559<o></o> ABC<o></o> 259<o></o> Rows.373-573<o></o> AB199<o></o> AB<o></o> 199<o></o> 1845<o></o> CSE<o></o> 250<o></o> <tbody> </tbody>

<tbody>
</tbody>

So, if the 'Common Qualifier' in Column C is also found within the Range of Rows.173-373 it’s values for Columns G & I only are to be taken regardless of any of the other values listed with the same 'Qualifier'.


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

Representing:<o></o> Column C<o></o> D<o></o> E<o></o> F<o></o> G<o></o> H<o></o> I<o></o> J<o></o> K<o></o> L<o></o> M<o></o> N<o></o> O<o></o> P<o></o> Q<o></o> R<o></o> S<o></o> T<o></o> U<o></o> V<o></o> Result of Code: now populated in Rows.2-172<o></o> XY155<o></o> ABC<o></o> XY<o></o> 159<o></o> 1459<o></o> ABC<o></o> 159<o></o> 1<o></o> 2<o></o> 3<o></o> 4<o></o> 5<o></o> 6<o></o> 7<o></o> 8<o></o> 9<o></o> 10<o></o> 11<o></o> 12<o></o> 13<o></o> Result of Code: now populated in Rows.2-172<o></o> AB199<o></o> CSE<o></o> AB<o></o> 199<o></o> 1945<o></o> ABC<o></o> 350<o></o> 14<o></o> 15<o></o> 16<o></o> 17<o></o> 18<o></o> 19<o></o> 20<o></o> 21<o></o> 22<o></o> 23<o></o> 24<o></o> 25<o></o> 26<o></o> <tbody> </tbody>

<tbody>
</tbody>

Where you can see that Columns G & I values are displayed as a result because they were found in Rows.173-373.

Likewise, if there is no qualifier present in Rows.173-372;

Representing Data From:<o></o> Column C<o></o> D<o></o> E<o></o> F<o></o> G<o></o> H<o></o> I<o></o> J<o></o> K<o></o> L<o></o> M<o></o> N<o></o> O<o></o> P<o></o> Q<o></o> R<o></o> S<o></o> T<o></o> U<o></o> V<o></o> Rows.2-172<o></o> XY155<o></o> ABC<o></o> XY<o></o> 155<o></o> 1559<o></o> ABC<o></o> 259<o></o> 1<o></o> 2<o></o> 3<o></o> 4<o></o> 5<o></o> 6<o></o> 7<o></o> 8<o></o> 9<o></o> 10<o></o> 11<o></o> 12<o></o> 13<o></o> Rows.2-172<o></o> AB199<o></o> CSE<o></o> AB<o></o> 199<o></o> 1845<o></o> CSE<o></o> 250<o></o> 14<o></o> 15<o></o> 16<o></o> 17<o></o> 18<o></o> 19<o></o> 20<o></o> 21<o></o> 22<o></o> 23<o></o> 24<o></o> 25<o></o> 26<o></o> Rows.173-372<o></o> XY155<o></o> XY<o></o> 155<o></o> 1459<o></o> ABC<o></o> 159<o></o> Rows.173-372<o></o> Rows.373-573<o></o> XY155<o></o> XY<o></o> 155<o></o> 1559<o></o> ABC<o></o> 259<o></o> Rows.373-573<o></o> AB199<o></o> AB<o></o> 199<o></o> 1845<o></o> CSE<o></o> 250<o></o> <tbody> </tbody>

<tbody>
</tbody>

Then the result would be;

Representing Data From:<o></o> Column C<o></o> D<o></o> E<o></o> F<o></o> G<o></o> H<o></o> I<o></o> J<o></o> K<o></o> L<o></o> M<o></o> N<o></o> O<o></o> P<o></o> Q<o></o> R<o></o> S<o></o> T<o></o> U<o></o> V<o></o> Rows.2-172<o></o> XY155<o></o> ABC<o></o> XY<o></o> 155<o></o> 1459<o></o> ABC<o></o> 159<o></o> 1<o></o> 2<o></o> 3<o></o> 4<o></o> 5<o></o> 6<o></o> 7<o></o> 8<o></o> 9<o></o> 10<o></o> 11<o></o> 12<o></o> 13<o></o> Rows.2-172<o></o> AB199<o></o> CSE<o></o> AB<o></o> 199<o></o> 1845<o></o> CSE<o></o> 250<o></o> 14<o></o> 15<o></o> 16<o></o> 17<o></o> 18<o></o> 19<o></o> 20<o></o> 21<o></o> 22<o></o> 23<o></o> 24<o></o> 25<o></o> 26<o></o> <tbody> </tbody>

<tbody>
</tbody>

This just adds to the work that you provided and it would be great if
you could finish it off.



Please Help!



R/

-Pin