Excel vba find Duplicates and corresponding column value is minimum in family

C

chiragpandya

New Member
Joined
Feb 26, 2018
Messages
5
I want to write a code which will change the corresponding value to minimum of the duplicates set.
Sample Data

[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Name[/TD]
[TD="width: 64"]Number[/TD]
[/TR]
[TR]
[TD]Ana[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Rachel[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Jack[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]Rachel[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]Ana[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Jack[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]Ana[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Jack[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD]Rachel[/TD]
[TD="align: right"]6[/TD]
[/TR]
</tbody>[/TABLE]


Sample output
[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]Name[/TD]
[TD="class: xl65, width: 64"]Number[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]Ana[/TD]
[TD="class: xl66, width: 64"]1[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]Rachel[/TD]
[TD="class: xl66, width: 64"]4[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]Jack[/TD]
[TD="class: xl66, width: 64"]7[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]Rachel[/TD]
[TD="class: xl66, width: 64"]4[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]Ana[/TD]
[TD="class: xl66, width: 64"]1[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]Jack[/TD]
[TD="class: xl66, width: 64"]7[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]Ana[/TD]
[TD="class: xl66, width: 64"]1[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]Jack[/TD]
[TD="class: xl66, width: 64"]7[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]Rachel[/TD]
[TD="class: xl66, width: 64"]4
[/TD]
[/TR]
</tbody>[/TABLE]


Can someone suggest?

Thanks.
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Sort by date Sort by votes
Try this:-
Code: 
[COLOR="Navy"]Sub[/COLOR] MG27Feb33
[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]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), 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.Offset(, 1)
    [COLOR="Navy"]Else[/COLOR]
        [COLOR="Navy"]If[/COLOR] Dn.Offset(, 1).Value < .Item(Dn.Value) [COLOR="Navy"]Then[/COLOR] _
        [COLOR="Navy"]Set[/COLOR] .Item(Dn.Value) = Dn.Offset(, 1)
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    Dn.Offset(, 1).Value = .Item(Dn.Value)
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Last edited:
Upvote 0
Thanks a lot Mick, Works like a charm.
MickG said:
Try this:-
Code: 
[COLOR=Navy]Sub[/COLOR] MG27Feb33
[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]
[COLOR=Navy]Set[/COLOR] Rng = Range(Range("A1"), 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.Offset(, 1)
    [COLOR=Navy]Else[/COLOR]
        [COLOR=Navy]If[/COLOR] Dn.Offset(, 1).Value < .Item(Dn.Value) [COLOR=Navy]Then[/COLOR] _
        [COLOR=Navy]Set[/COLOR] .Item(Dn.Value) = Dn.Offset(, 1)
    [COLOR=Navy]End[/COLOR] If
[COLOR=Navy]Next[/COLOR]
[COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] Dn [COLOR=Navy]In[/COLOR] Rng
    Dn.Offset(, 1).Value = .Item(Dn.Value)
[COLOR=Navy]Next[/COLOR] Dn
[COLOR=Navy]End[/COLOR] With
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]
Regards Mick
Click to expand...
 
Upvote 0
Can you help me modify the code to include a condition. If flag is yes then that is the number to be considered in dictionary. I wrote another loop to go through the flag and update the dictionary but it gives me an property not supported error.
Sample Data

[TABLE="width: 192"]
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]
Name[/TD]
[TD="width: 64"]Number[/TD]
[TD="width: 64"] Flag[/TD]
[/TR]
[TR]
[TD]Ana[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Rachel[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jack[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Rachel[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Ana[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jack[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Ana[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jack[/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Rachel[/TD]
[TD="align: right"]6[/TD]
[TD] Y[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Sample output

[TABLE="width: 192"]
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Name[/TD]
[TD="width: 64"]Number[/TD]
[TD="width: 64"] Flag[/TD]
[/TR]
[TR]
[TD]Ana[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Rachel[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jack[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Rachel[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Ana[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jack[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Ana[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jack[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Rachel[/TD]
[TD="align: right"]6[/TD]
[TD] Y[/TD]
[/TR]
</tbody>[/TABLE]


chiragpandya said:
Thanks a lot Mick, Works like a charm.
Click to expand...
 
Upvote 0
Try this:-
Code: 
[COLOR="Navy"]Sub[/COLOR] MG28Feb17
[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] Q [COLOR="Navy"]As[/COLOR] Variant, fd [COLOR="Navy"]As[/COLOR] Boolean
[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]
        [COLOR="Navy"]If[/COLOR] UCase(Dn.Offset(, 2).Value) = "Y" [COLOR="Navy"]Then[/COLOR] fd = True
        .Add Dn.Value, Array(Dn.Offset(, 1), fd)
        
    [COLOR="Navy"]Else[/COLOR]
       Q = .Item(Dn.Value)
        [COLOR="Navy"]If[/COLOR] Q(1) = False [COLOR="Navy"]Then[/COLOR]
           [COLOR="Navy"]If[/COLOR] Dn.Offset(, 1).Value < Q(0) And Q(1) = False [COLOR="Navy"]Then[/COLOR]
            [COLOR="Navy"]Set[/COLOR] Q(0) = Dn.Offset(, 1)
            [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]If[/COLOR] UCase(Dn.Offset(, 2).Value) = "Y" [COLOR="Navy"]Then[/COLOR]
            Q(1) = True
            [COLOR="Navy"]Set[/COLOR] Q(0) = Dn.Offset(, 1)
        [COLOR="Navy"]End[/COLOR] If
        .Item(Dn.Value) = Q
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    Dn.Offset(, 1).Value = .Item(Dn.Value)
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
Thanks a lot Mick. You are a lifesaver.

MickG said:
Try this:-
Code: 
[COLOR=Navy]Sub[/COLOR] MG28Feb17
[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] Q [COLOR=Navy]As[/COLOR] Variant, fd [COLOR=Navy]As[/COLOR] Boolean
[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]
        [COLOR=Navy]If[/COLOR] UCase(Dn.Offset(, 2).Value) = "Y" [COLOR=Navy]Then[/COLOR] fd = True
        .Add Dn.Value, Array(Dn.Offset(, 1), fd)
        
    [COLOR=Navy]Else[/COLOR]
       Q = .Item(Dn.Value)
        [COLOR=Navy]If[/COLOR] Q(1) = False [COLOR=Navy]Then[/COLOR]
           [COLOR=Navy]If[/COLOR] Dn.Offset(, 1).Value < Q(0) And Q(1) = False [COLOR=Navy]Then[/COLOR]
            [COLOR=Navy]Set[/COLOR] Q(0) = Dn.Offset(, 1)
            [COLOR=Navy]End[/COLOR] If
        [COLOR=Navy]End[/COLOR] If
        [COLOR=Navy]If[/COLOR] UCase(Dn.Offset(, 2).Value) = "Y" [COLOR=Navy]Then[/COLOR]
            Q(1) = True
            [COLOR=Navy]Set[/COLOR] Q(0) = Dn.Offset(, 1)
        [COLOR=Navy]End[/COLOR] If
        .Item(Dn.Value) = Q
    [COLOR=Navy]End[/COLOR] If
[COLOR=Navy]Next[/COLOR]
[COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] Dn [COLOR=Navy]In[/COLOR] Rng
    Dn.Offset(, 1).Value = .Item(Dn.Value)
[COLOR=Navy]Next[/COLOR] Dn
[COLOR=Navy]End[/COLOR] [COLOR=Navy]With[/COLOR]
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]
Regards Mick
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Trying to get a formula to show last 5 most recent text values in a column corresponding to a name
2
Replies
10
Views
86
Cubist
Cubist
C
  • Solved
VBA Check for duplicate value and change if duplicate.
Replies
7
Views
635
Alex Blakenburg
A
B
  • Question Question
Moving Cursor to Current Date (Column) but same row that I was in before clicking hyperlink or vba button
Replies
1
Views
89
kevin9999
K
C
  • Question Question
Create a new row for every date in row and create multiple copies of data to new worksheet based on cell number value for each date in the row VBA
Replies
0
Views
184
Chris258
C
J
  • Question Question
A VBA Code to Transfer rows of cells across Sheets based on Certain Column Value
Replies
2
Views
383
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,226,730
Messages
6,192,705
Members
453,748
Latest member
akhtarf3

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top