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!

 

[Pinaceous]

Again, Outstanding Work!!!!

 

[Marcelo Branco]

Okay M. This is Great. Just, How do I restrict this Macro to only execute between Excel's Column's C to Column V => in area of $C$1:$V$574?

Try this (assumes data in Sheet1)

Sub aTest()
    Dim i As Long, j 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("C1:V574")
        vData = .Value
        
        For i = 1 To .Rows.Count
            If vData(i, 1) <> "" Then
                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), .Range(.Cells(i, 1), .Cells(i, .Columns.Count)).Cells
                End If
            End If
        Next i
        
        For Each v In dict.keys
            lin = lin + 1
            .Range("C" & lin).Resize(, .Columns.Count).Value = dict.Item(v).Cells.Value
        Next v
        
        .Rows(dict.Count + 1 & ":" & 574).ClearContents
    
    End With
        
End Sub

M.

 

[Marcelo Branco]

oops...

Please correct this code line

For Each v In dict.keys
            lin = lin + 1
            [COLOR=#ff0000].Range("C" & lin)[/COLOR].Resize(, .Columns.Count).Value = dict.Item(v).Cells.Value
Next v

to

For Each v In dict.keys
            lin = lin + 1
            [COLOR=#0000ff].Range("A" & lin)[/COLOR].Resize(, .Columns.Count).Value = dict.Item(v).Cells.Value
 Next v

M.

 

[Pinaceous]

Hi Marcelo,

I tried out the two variations. As it looks, I actually had better success with .Range("C" & lin) than .Range("A" & lin).
For some reason the data is better represented with Range “C” as you can see below in the before and after tables.
"Before Table"; "C" & "A", respectively;

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	2345	ABC	250	1	2	3	4	5	6	7	8	9	10	11	12	13
YZ 1444	EFG	YZ	1444	1834	EFG	222	1		3		5		7		9		11		13
AB 1111	HIJ	AB	1111	1422	JKL	423		2		4		6		8		10		12
CD 123	KEL	CD	123	2021	MNO	142	5	4	3
XY 1559	ABC	XY	1559	0009	ABC	275	13	12	11	10	9	8	7
XY 1559		XY	1559	2355	ABC	250
YZ 1444		YZ	1444	1855	EFG	222
AB 1111		AB	1111	1456	JKL	423
CD 123		CD	123	2045	MNO	142
XY 1559		XY	1559	0028	ABC	275
XY 1559		XY	1559	2345	ABC	250
YZ 1444		YZ	1444	1834	EFG	222
AB 111		AB	1111	1422	JKL	423
CD 123		CD	123	2021	MNO	142
XY 1559		XY	1559	0009	ABC	275

<tbody>
</tbody>

After "C"

Column 1	C2	C3	C4	C5	C6	C7	C8	C9	C10	C11	C12	C13	C14	C15	C16	C17	C18	C19	C20
XY ABC	ABC	XY	ABC	XY	ABC	2355	ABC	260	13	12	11	10	9	8	9	10	11	12	13
YZ 1444	EFG	YZ	1444	YZ	EFG	1855	EFG	232	3		5		7		9		11		13
AB 1111	HIJ	AB	1111	AB	JKL	1456	JKL	433		4		6		8		10		12
CD 123	KEL	CD	123	CD	MNO	2045	MNO	172	3
	ABC

<tbody>
</tbody>

After "A"

Column 1	C2	C3	C4	C5	C6	C7	C8	C9	C10	C11	C12	C13	C14	C15	C16	C17	C18	C19	C20
XY	ABC	XY	ABC	2355	ABC	275	13	12	11	10	9	8	9	10	11	12	13
YZ	1444	YZ	EFG	1855	EFG	222	3		5		7		9		11		13
AB	1111	AB	JKL	1456	JKL	423		4		6		8		10		12
CD	123	CD	MNO	2045	MNO	142	3

<tbody>
</tbody>

Ideally, I would like the data to compile after execution to behave:
"After Table"

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	0028	ABC	275	13	12	11	10	9	8	7
AB 1111	HIJ	AB	1111	1456	JKL	423		2		4		6		8		10		12
CD 123	KEL	CD	123	2045	MNO	142	5	4	3
YZ 1444	EFG	YZ	1444	1856	EFG	222	1		3		5		7		9		11		13
XY1559	ABC	XY	1559	2355	ABC	250	1	2	3	4	5	6	7	8	9	10	11	12	13

<tbody>
</tbody>

Where here this "After Table" above represents after the execution is applied to the original "Before Table" data.
Here you will have to add an additional seeded code, either into the original code, which works fine in both taking the MAX value and in its after representation, or to this evolving code that you later suggested.
This additional seeded code must include an "if and only if" exception, written into the current code, where it treats the difference of numerical value of Column 5 greater than 1200 as a separate entry.
Please take note that Column 1 is really Column C in the code, consecutively forward, making Column 5 really Column G for the restricted domain of the Code being C2 to V573.
Also, please note that it will sort the "new" information according to an ascending values in this Column 5; (my Excel's Column G).
Many thanks in advance.
R/
Pinaceous

 

[Marcelo Branco]

Sorry, I don't understand what you mean by

"This additional seeded code must include an "if and only if" exception, written into the current code, where it treats the difference of numerical value of Column 5 greater than 1200 as a separate entry."

Difference to/from what?

----------------------------------------

Sorry, my code above is not correct. My bad

Try (adapted to range C2:V573)

Sub aTest()
    Dim i As Long, j 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
                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 Marcelo,

Thanks for the updated code, as far as the additional requirement I need in the code I'll try and explain it by these two tables:

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	2325	ABC	159	1	2	3	4	5	6	7	8	9	10	11	12	13
XY 1559		XY	1559	2359	ABC	250
XY 1559		XY	1559	2325	ABC	159
XY 1559	ABC	XY	1559	0009	ABC	199	1	2	3	4	5	6	7	8	9	10	11	12	13
XY 1559		XY	1559	0025	ABC	243
XY 1559		XY	1559	0009	ABC	199

<tbody>
</tbody>

Okay, the top table above represents before the applied code.

The bottom table here represents the executed code with the "if and only if" exception, written into the current code or the original, where it treats the difference of numerical values of Column 5 greater than 1200 as a separate entry.

Please note that Column 1 is really where Column C starts in the code, consecutively forward, making Column 5 really Column G for the restricted domain of the code being C2:V5733.

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	0025	ABC	243	1	2	3	4	5	6	7	8	9	10	11	12	13
XY 1559	ABC	XY	1559	2359	ABC	250	1	2	3	4	5	6	7	8	9	10	11	12	13

<tbody>
</tbody>

Also take note that it arranges/sorts the data in ascending order according to Column 5.

Thanks M.

- P

 

[Marcelo Branco]

Still not clear for me - what you mean by difference of values?

Or are you saying that rows with column 5 equal or greater than 1200 should be grouped differently than rows with column 5 less than 1200?

M.

 

[Pinaceous]

Hi M,

It is a little confussing I'd admit, let's use the last Entry Data Table's and Table Result Above to help me explain in math terms how I might get my point accross.

So for the above entry, its last Table Result is represented as:

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	0025	ABC	243	1	2	3	4	5	6	7	8	9	10	11	12	13
XY 1559	ABC	XY	1559	2359	ABC	250	1	2	3	4	5	6	7	8	9	10	11	12	13

<TBODY>
</TBODY>

Here, we see that in Column 5 Table Result, for its first entry is 0025 and its second entry is 2359. Here there difference between these two MAX sets is greater than 1200, so Excel will therefore group these values as two different entries and will not delete it. Here, this difference is infact [2359(MAX for this range of data)-0025(MAX for this range of data)='s] 2334, which is much larger than 1200, so Excel will now group these two entries separately.

Please let me know, if you would like me to repharse.

Thanks,

-P

 

[Pinaceous]

Thank you!

 

[Marcelo Branco]

A question:
the values in column 5 are Text or Numbers formatted as 00000?

To check in an empty cell put
=ISNUMBER(G3)
tell us what is returned

M.