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

 

[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;


[TABLE="class: grid, width: 500, align: center"]
<TBODY>[TR]
[TD]Discription:
[/TD]
[TD]Discription:
[/TD]
[TD]Col. C
[/TD]
[TD]C. D
[/TD]
[TD]C. E
[/TD]
[TD]C. F
[/TD]
[TD]C. G
[/TD]
[TD]C. H
[/TD]
[TD]C. I
[/TD]
[TD]C. J
[/TD]
[TD]C. K
[/TD]
[TD]C. L
[/TD]
[TD]C. M
[/TD]
[TD]C. N
[/TD]
[TD]C. O
[/TD]
[TD]C. P
[/TD]
[TD]C. Q
[/TD]
[TD]C. R
[/TD]
[TD]C. S
[/TD]
[TD]C. T
[/TD]
[TD]C. U
[/TD]
[TD]C.
V
[/TD]
[/TR]
[TR]
[TD](Priority 2)
[/TD]
[TD]Row.1 (Range 2-202)
[/TD]
[TD]XY 1559
[/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](Priority 1)
[/TD]
[TD]R. 2 (Range 203-403)
[/TD]
[TD]XY 1559
[/TD]
[TD]ABC
[/TD]
[TD]XY
[/TD]
[TD]1559
[/TD]
[TD]1559
[/TD]
[TD]ABC
[/TD]
[TD]150
[/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](Priority 2)
[/TD]
[TD]R. 3 (Range 404-573)
[/TD]
[TD]XY1559
[/TD]
[TD]ABC
[/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]


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;


[TABLE="class: grid, width: 500, align: center"]
<TBODY>[TR]
[TD]Col. C
[/TD]
[TD]C. D
[/TD]
[TD]C. E
[/TD]
[TD]C. F
[/TD]
[TD]C. G
[/TD]
[TD]C. H
[/TD]
[TD]C. I
[/TD]
[TD]C. J
[/TD]
[TD]C. K
[/TD]
[TD]C. L
[/TD]
[TD]C. M
[/TD]
[TD]C. N
[/TD]
[TD]C. O
[/TD]
[TD]C. P
[/TD]
[TD]C. Q
[/TD]
[TD]C. R
[/TD]
[TD]C. S
[/TD]
[TD]C. T
[/TD]
[TD]C. U
[/TD]
[TD]C. V
[/TD]
[/TR]
[TR]
[TD]XY
[/TD]
[TD]1559
[/TD]
[TD]ABC
[/TD]
[TD]XY
[/TD]
[TD]1559
[/TD]
[TD]1559
[/TD]
[TD]ABC
[/TD]
[TD]150
[/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]
[/TR]
</TBODY>[/TABLE]


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....


[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]User-Input Data
[/TD]
[TD]Table 1
[/TD]
[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]Priority 2
[/TD]
[TD]Rows.Range 02-202
[/TD]
[TD]XY1559
[/TD]
[TD]XYZ
[/TD]
[TD]XY
[/TD]
[TD]1559
[/TD]
[TD]1230
[/TD]
[TD]XYZ
[/TD]
[TD]200
[/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][/TD]
[TD]Rows.Range 02-202
[/TD]
[TD]AB1234
[/TD]
[TD]ABG
[/TD]
[TD]AB
[/TD]
[TD]1234
[/TD]
[TD]1330
[/TD]
[TD]ABG
[/TD]
[TD]198
[/TD]
[TD]10
[/TD]
[TD]11
[/TD]
[TD]12
[/TD]
[TD]13
[/TD]
[TD]14
[/TD]
[TD]15
[/TD]
[TD]16
[/TD]
[TD]17
[/TD]
[TD]18
[/TD]
[TD]19
[/TD]
[TD]20
[/TD]
[TD]21
[/TD]
[TD]22
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Rows.Range 02-202
[/TD]
[TD]CD635
[/TD]
[TD]CDR
[/TD]
[TD]CD
[/TD]
[TD]635
[/TD]
[TD]1450
[/TD]
[TD]CDR
[/TD]
[TD]250
[/TD]
[TD]8
[/TD]
[TD]8
[/TD]
[TD]8
[/TD]
[TD]8
[/TD]
[TD]8
[/TD]
[TD]8
[/TD]
[TD]8
[/TD]
[TD]8
[/TD]
[TD]8
[/TD]
[TD]8
[/TD]
[TD]8
[/TD]
[TD]8
[/TD]
[TD]8
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Rows.Range 02-202
[/TD]
[TD]EF1748
[/TD]
[TD]EFW
[/TD]
[TD]EF
[/TD]
[TD]1748
[/TD]
[TD]1640
[/TD]
[TD]EFW
[/TD]
[TD]532
[/TD]
[TD]4
[/TD]
[TD]4
[/TD]
[TD]4
[/TD]
[TD]4
[/TD]
[TD]4
[/TD]
[TD]4
[/TD]
[TD]4
[/TD]
[TD]4
[/TD]
[TD]4
[/TD]
[TD]4
[/TD]
[TD]4
[/TD]
[TD]4
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Rows.Range 02-202
[/TD]
[TD]YZ546
[/TD]
[TD]YZQ
[/TD]
[TD]YZ
[/TD]
[TD]546
[/TD]
[TD]1745
[/TD]
[TD]YZQ
[/TD]
[TD]890
[/TD]
[TD]5
[/TD]
[TD]5
[/TD]
[TD]5
[/TD]
[TD]5
[/TD]
[TD]5
[/TD]
[TD]5
[/TD]
[TD]5
[/TD]
[TD]5
[/TD]
[TD]5
[/TD]
[TD]5
[/TD]
[TD]5
[/TD]
[TD]5
[/TD]
[TD]5
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Real-time Data
[/TD]
[TD]Table 2
[/TD]
[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]Priority 1

[/TD]
[TD]Rows.Range 202-402
[/TD]
[TD]XY1559
[/TD]
[TD]XYZ
[/TD]
[TD]XY
[/TD]
[TD]1559
[/TD]
[TD]1230
[/TD]
[TD]XYZ
[/TD]
[TD]88
[/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][/TD]
[TD]Rows.Range 202-402
[/TD]
[TD]AB1234
[/TD]
[TD]ABG
[/TD]
[TD]AB
[/TD]
[TD]1234
[/TD]
[TD]1330
[/TD]
[TD]ABG
[/TD]
[TD]354
[/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][/TD]
[TD]Rows.Range 202-402
[/TD]
[TD]CD635
[/TD]
[TD]CDR
[/TD]
[TD]CD
[/TD]
[TD]635
[/TD]
[TD]1450
[/TD]
[TD]CDR
[/TD]
[TD]546
[/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][/TD]
[TD]Rows.Range 202-402
[/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]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Rows.Range 202-402
[/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]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Initial Data
[/TD]
[TD]Table 3
[/TD]
[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]Priority2
[/TD]
[TD]Rows.Range 403-573
[/TD]
[TD]XY1559
[/TD]
[TD]XYZ
[/TD]
[TD]XY
[/TD]
[TD]1559
[/TD]
[TD]1230
[/TD]
[TD]XYZ
[/TD]
[TD]200
[/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][/TD]
[TD]Rows.Range 403-573
[/TD]
[TD]AB1234
[/TD]
[TD]ABG
[/TD]
[TD]AB
[/TD]
[TD]1234
[/TD]
[TD]1330
[/TD]
[TD]ABG
[/TD]
[TD]198
[/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][/TD]
[TD]Rows.Range 403-573
[/TD]
[TD]CD635
[/TD]
[TD]CDR
[/TD]
[TD]CD
[/TD]
[TD]635
[/TD]
[TD]1450
[/TD]
[TD]CDR
[/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][/TD]
[TD]Rows.Range 403-573
[/TD]
[TD]EF1748
[/TD]
[TD]EFW
[/TD]
[TD]EF
[/TD]
[TD]1748
[/TD]
[TD]1640
[/TD]
[TD]EFW
[/TD]
[TD]432
[/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][/TD]
[TD]Rows.Range 403-573
[/TD]
[TD]YZ546
[/TD]
[TD]YZQ
[/TD]
[TD]YZ
[/TD]
[TD]546
[/TD]
[TD]1745
[/TD]
[TD]YZQ
[/TD]
[TD]257
[/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]



Here is the 'Result' of which will display the User-Input Data, in representing the top table's data:
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]User-Input Data
[/TD]
[TD][/TD]
[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]Priority 2
[/TD]
[TD]Rows.Range 02-202
[/TD]
[TD]XY1559
[/TD]
[TD]XYZ
[/TD]
[TD]XY
[/TD]
[TD]1230
[/TD]
[TD]XYZ
[/TD]
[TD]88
[/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]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Rows.Range 02-202
[/TD]
[TD]AB1234
[/TD]
[TD]ABG
[/TD]
[TD]AB
[/TD]
[TD]1330
[/TD]
[TD]ABG
[/TD]
[TD]354
[/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]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Rows.Range 02-202
[/TD]
[TD]CD635
[/TD]
[TD]CDR
[/TD]
[TD]CD
[/TD]
[TD]1450
[/TD]
[TD]CDR
[/TD]
[TD]546
[/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]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Rows.Range 02-202
[/TD]
[TD]EF1748
[/TD]
[TD]EFW
[/TD]
[TD]EF
[/TD]
[TD]1640
[/TD]
[TD]EFW
[/TD]
[TD]532
[/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]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Rows.Range 02-202
[/TD]
[TD]YZ546
[/TD]
[TD]YZQ
[/TD]
[TD]YZ
[/TD]
[TD]1745
[/TD]
[TD]YZQ
[/TD]
[TD]890
[/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]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



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:

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Initial Data
[/TD]
[TD]Table 1
[/TD]
[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]Priority 2
[/TD]
[TD]Rows.Range 02-202
[/TD]
[TD]XY1559
[/TD]
[TD]XYZ
[/TD]
[TD]XY
[/TD]
[TD]1559
[/TD]
[TD]1230
[/TD]
[TD]XYZ
[/TD]
[TD]88
[/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][/TD]
[TD]Rows.Range 02-202
[/TD]
[TD]AB1234
[/TD]
[TD]ABG
[/TD]
[TD]AB
[/TD]
[TD]1234
[/TD]
[TD]1330
[/TD]
[TD]ABG
[/TD]
[TD]354
[/TD]
[TD]10
[/TD]
[TD]11
[/TD]
[TD]12
[/TD]
[TD]13
[/TD]
[TD]14
[/TD]
[TD]15
[/TD]
[TD]16
[/TD]
[TD]17
[/TD]
[TD]18
[/TD]
[TD]19
[/TD]
[TD]20
[/TD]
[TD]21
[/TD]
[TD]22
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Rows.Range 02-202
[/TD]
[TD]CD635
[/TD]
[TD]CDR
[/TD]
[TD]CD
[/TD]
[TD]635
[/TD]
[TD]1450
[/TD]
[TD]CDR
[/TD]
[TD]546
[/TD]
[TD]8
[/TD]
[TD]8
[/TD]
[TD]8
[/TD]
[TD]8
[/TD]
[TD]8
[/TD]
[TD]8
[/TD]
[TD]8
[/TD]
[TD]8
[/TD]
[TD]8
[/TD]
[TD]8
[/TD]
[TD]8
[/TD]
[TD]8
[/TD]
[TD]8
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Rows.Range 02-202
[/TD]
[TD]EF1748
[/TD]
[TD]EFW
[/TD]
[TD]EF
[/TD]
[TD]1748
[/TD]
[TD]1640
[/TD]
[TD]EFW
[/TD]
[TD]532
[/TD]
[TD]4
[/TD]
[TD]4
[/TD]
[TD]4
[/TD]
[TD]4
[/TD]
[TD]4
[/TD]
[TD]4
[/TD]
[TD]4
[/TD]
[TD]4
[/TD]
[TD]4
[/TD]
[TD]4
[/TD]
[TD]4
[/TD]
[TD]4
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Rows.Range 02-202
[/TD]
[TD]YZ546
[/TD]
[TD]YZQ
[/TD]
[TD]YZ
[/TD]
[TD]546
[/TD]
[TD]1745
[/TD]
[TD]YZQ
[/TD]
[TD]890
[/TD]
[TD]5
[/TD]
[TD]5
[/TD]
[TD]5
[/TD]
[TD]5
[/TD]
[TD]5
[/TD]
[TD]5
[/TD]
[TD]5
[/TD]
[TD]5
[/TD]
[TD]5
[/TD]
[TD]5
[/TD]
[TD]5
[/TD]
[TD]5
[/TD]
[TD]5
[/TD]
[/TR]
</tbody>[/TABLE]



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