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

[Pinaceous]

In using Excell 2013 ->

Here is my Data:

Column1	C2	C3	C4	C5	C6	C7	C8	C9	C10	C11	C12	C13	C14	C15	C16	C17	C18	C19	C20
XY1559	ABC	XY	1559	1559	ABC	159	1	2	3	4	5	6	7	8	9	10	11	12	13
XY1559		XY	1559	1645	ABC	250
XY1559		XY	1559	1559	ABC	159

<tbody>
</tbody>

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:

Column1	C2	C3	C4	C5	C6	C7	C8	C9	C10	C11	C12	C13	C14	C15	C16	C17	C18	C19	C20
XY1559	ABC	XY	1559	1645	ABC	250	1	2	3	4	5	6	7	8	9	10	11	12	13

<tbody>
</tbody>

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

Please Help!

R/
-Pin

 

[MickG]

Try this:-
Your actual Data assumed to start "A2"

[COLOR="Navy"]Sub[/COLOR] MG25Apr58
[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] nRng [COLOR="Navy"]As[/COLOR] Range
[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
    [COLOR="Navy"]If[/COLOR] Not .Exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
        .Add Dn.Value, Dn
    [COLOR="Navy"]Else[/COLOR]
        [COLOR="Navy"]For[/COLOR] n = 2 To 20
            [COLOR="Navy"]If[/COLOR] Dn(1, n) > .Item(Dn.Value)(1, n) [COLOR="Navy"]Then[/COLOR]
                .Item(Dn.Value)(1, n) = Dn(1, n)
            [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]Next[/COLOR] n
        [COLOR="Navy"]If[/COLOR] nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
            [COLOR="Navy"]Set[/COLOR] nRng = Dn
        [COLOR="Navy"]Else[/COLOR]
            [COLOR="Navy"]Set[/COLOR] nRng = Union(nRng, Dn)
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]If[/COLOR] Not nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR] nRng.EntireRow.Delete
[COLOR="Navy"]End[/COLOR] With


[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Pinaceous]

Wonderful Mike! Can you taylor your code to set a priority sequence WRT the MAX VALUE rule for Column I (was Col. 7 w/last table) into a sequence? Here is a table as an example;

Discription:

Discription:

Col. C

C. D

C. E

C. F

C. G

C. H

C. I

C. J

C. K

C. L

C. M

C. N

C. O

C. P

C. Q

C. R

C. S

C. T

C. U

C. V

(Priority 2)

Row.1 (Range 2-202)

XY 1559

ABC

XY

1559

1559

ABC

159

1

2

3

4

5

6

7

8

9

10

11

12

13

(Priority 1)

R. 2 (Range 203-403)

XY 1559

ABC

XY

1559

1559

ABC

150

(Priority 2)

R. 3 (Range 404-573)

XY1559

ABC

XY

1559

1559

ABC

159

<TBODY>
</TBODY>

Where the Priority sequence can offset the MAX VALUE rule, in its value being HIGHER or LOWER, where here Priority 1's data for Row's Range 203-403' overrides the MAX VALUE rule of which is really set for the Priority 2's being in Row's of Range Row 2-202 & Rows 404-573.

To result in;

Col. C	C. D	C. E	C. F	C. G	C. H	C. I	C. J	C. K	C. L	C. M	C. N	C. O	C. P	C. Q	C. R	C. S	C. T	C. U	C. V
XY	1559	ABC	XY	1559	1559	ABC	150	1	2	3	4	5	6	7	8	9	10	11	12

<TBODY>
</TBODY>

Many Thanks In Advance.

R/
Pinaceous

 

[MickG]

Can you show a more comprehensive set of actual data and the results expected, as at the moment its not too clear.
1) I'm not sure whether columns "A/B" data is actually data or just a reference to the the rows "priority".
2) Not sure why you only show one line for results (Min) and not also a results for "Max" (Priority1).
3) Not sure if you want to deal with "Priorities 1 & 2 " as separate sets of data.
Hopefully this will be clearer from a new sat of data.

 

[Pinaceous]

Hi Mick,
That is understandable. I'll try and be more clearer in the following example.

Thank you.

R/
-Pinaceous

 

[Pinaceous]

Okay, here it goes....

User-Input Data	Table 1	Column C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
Priority 2	Rows.Range 02-202	XY1559	XYZ	XY	1559	1230	XYZ	200	1	2	3	4	5	6	7	8	9	10	11	12	13
	Rows.Range 02-202	AB1234	ABG	AB	1234	1330	ABG	198	10	11	12	13	14	15	16	17	18	19	20	21	22
	Rows.Range 02-202	CD635	CDR	CD	635	1450	CDR	250	8	8	8	8	8	8	8	8	8	8	8	8	8
	Rows.Range 02-202	EF1748	EFW	EF	1748	1640	EFW	532	4	4	4	4	4	4	4	4	4	4	4	4	4
	Rows.Range 02-202	YZ546	YZQ	YZ	546	1745	YZQ	890	5	5	5	5	5	5	5	5	5	5	5	5	5

<tbody>
</tbody>

Real-time Data

Table 2

Column C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

Priority 1

Rows.Range 202-402

XY1559

XYZ

XY

1559

1230

XYZ

88

Rows.Range 202-402

AB1234

ABG

AB

1234

1330

ABG

354

Rows.Range 202-402

CD635

CDR

CD

635

1450

CDR

546

Rows.Range 202-402

Rows.Range 202-402

<tbody>
</tbody>

Initial Data

Table 3

Column C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

Priority2

Rows.Range 403-573

XY1559

XYZ

XY

1559

1230

XYZ

200

Rows.Range 403-573

AB1234

ABG

AB

1234

1330

ABG

198

Rows.Range 403-573

CD635

CDR

CD

635

1450

CDR

250

Rows.Range 403-573

EF1748

EFW

EF

1748

1640

EFW

432

Rows.Range 403-573

YZ546

YZQ

YZ

546

1745

YZQ

257

<tbody>
</tbody>

Here is the 'Result' of which will display the User-Input Data, in representing the top table's data:

User-Input Data

Column C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

Priority 2

Rows.Range 02-202

XY1559

XYZ

XY

1230

XYZ

88

Rows.Range 02-202

AB1234

ABG

AB

1330

ABG

354

Rows.Range 02-202

CD635

CDR

CD

1450

CDR

546

Rows.Range 02-202

EF1748

EFW

EF

1640

EFW

532

Rows.Range 02-202

YZ546

YZQ

YZ

1745

YZQ

890

<tbody>
</tbody>

Explaining the Results:

Where (88) is taken b/c: XY1559 - Is a Priority 1 overide (displaying ie. a Lower Value)
Where (354) is taken b/c: AB1234 - Is a Priority 1 overide (displaying ie. a High Value)
Where (546) is taken b/c: CD635 - Is a Priority 1 overide (displaying ie. a Higher Value)
Where (532) is taken b/c: EF1748 - Is a Priority 2 MAX VALUE - b/c the Priority 1 Real-Time Data didn't get posted yet.
Where (890) is taken b/c: YZ546 - Is a Priority 2 MAX VALUE - b/c the Priority 1 Real-Time Data didn't get posted yet.


I hope you can understand this example. Here there are two web queries importing data, shown here by 'Table 2' & 'Table 3' of which is time dependent. This information gets compiled by your 'CODE' in representing 'Table 1' of which is then copied and pasted on 'Sheet 1' for the user to input data.

Also, I hope you can write a 'CODE' to encompass this priority scale of information.

Many thanks in advance.

R/
Paul Kelly

 

[Pinaceous]

Sorry Mick,

My Result table is messed up; I tried to change it but the 10 minute rule expired on me while I was editing it' after I already posted it.

The Result Table should read:

Initial Data	Table 1	Column C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
Priority 2	Rows.Range 02-202	XY1559	XYZ	XY	1559	1230	XYZ	88	1	2	3	4	5	6	7	8	9	10	11	12	13
	Rows.Range 02-202	AB1234	ABG	AB	1234	1330	ABG	354	10	11	12	13	14	15	16	17	18	19	20	21	22
	Rows.Range 02-202	CD635	CDR	CD	635	1450	CDR	546	8	8	8	8	8	8	8	8	8	8	8	8	8
	Rows.Range 02-202	EF1748	EFW	EF	1748	1640	EFW	532	4	4	4	4	4	4	4	4	4	4	4	4	4
	Rows.Range 02-202	YZ546	YZQ	YZ	546	1745	YZQ	890	5	5	5	5	5	5	5	5	5	5	5	5	5

<tbody>
</tbody>

Where here after the code it becomes 'Table 1' all over again.

Thanks,

-Pinaceous

 

[Pinaceous]

Hi Mick,

I just wanted to add that Column A & B, is not part of the tables, and is not represented here. What is written there, where they would normally be on the excel sheet is just for informational purposes only.

The number data starts on Column C and ends on Column V.

Thanks.

 

[MickG]

This code is based on your 3 tables being in one sheet set in there designated ranges.
The code sets the first table as Base table, then compares the other two tables to it.
If table 1 values (Column c) are found in table 2 then the values columns (C to I) in table 2 are placed in Table 1.
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.
So at the moment the Code Only modifies table1 without pasting it to another sheet.
This code produces the correct answer but I'm not sure if its for the right reasons. !!!!

[COLOR=Navy]Sub[/COLOR] MG27Apr30
[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]For[/COLOR] n = 1 To 6
              Q(0).Offset(, n).Value = Dn2.Offset(, n).Value
            [COLOR=Navy]Next[/COLOR] n
           Q(1) = True
       .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] Rng2
      [COLOR=Navy]If[/COLOR] Not IsEmpty(Dn3.Value) [COLOR=Navy]Then[/COLOR]
       [COLOR=Navy]If[/COLOR] .exists(Dn3.Value) And .Item(Dn3.Value)(1) = False [COLOR=Navy]Then[/COLOR]
           [COLOR=Navy]If[/COLOR] Dn3.Offset(, 6) > .Item(Dn3.Value)(0).Offset(, 6) [COLOR=Navy]Then[/COLOR]
            [COLOR=Navy]For[/COLOR] n = 1 To 6
                .Item(Dn3.Value)(0).Offset(, n).Value = Dn3.Offset(, n).Value
            [COLOR=Navy]Next[/COLOR] n
           [COLOR=Navy]End[/COLOR] If
        [COLOR=Navy]End[/COLOR] If
      [COLOR=Navy]End[/COLOR] If
    [COLOR=Navy]Next[/COLOR] Dn3
[COLOR=Navy]End[/COLOR] With
MsgBox "Run"
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]

Regards Mick

 

[Pinaceous]

Hi Mick!


I thought there would be an alternative way to approach this and it seems like you achieved it. I really like the idea of a loop cycle, I didn't think it was possible. I congratulate you!

To give you some more insight this sheet allows three sets of data to be compiled, independent from the user, and a screen shot to be taken being the result of your code in producing Table 1 by way of VBA is next copied and pasted onto Sheet 1 for the user to input there data in Columns (J to V), of which was Table 1.

Table 3 is a web query that first introduces the proposed data for the user onto this sheet and gets complied and ordered first/initially by your code and then by VBA sequencing gets copied and pasted onto Sheet 1 for the user to input there data and/or change any of the information, if they see fit.

Table 2 is also a web query that posts the actual or corrected data from Table 3 because it is time dependent, so it is subjected to change, therefore is not represented much in the beginning of the day, but is more presented towards the end of the day as the information becomes verified, in reproducing Columns (C to I).

It is important for me to help you understand where the reasons are coming from & I hope this clears it up a bit.

Many thanks,
-Pinaceous