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

NameNumber
Ana1
Rachel4
Jack7
Rachel5
Ana2
Jack8
Ana3
Jack9
Rachel6

<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>


Sample output
NameNumber
Ana1
Rachel4
Jack7
Rachel4
Ana1
Jack7
Ana1
Jack7
Rachel4

<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>


Can someone suggest?

Thanks.
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
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


Name		Number Flag
Ana1
Rachel4
Jack7
Rachel5
Ana2
Jack8
Ana3
Jack9
Rachel6 Y

<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>
</tbody>

Sample output

NameNumber Flag
Ana1
Rachel6
Jack7
Rachel6
Ana1
Jack7
Ana1
Jack7
Rachel6 Y

<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>
</tbody>


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

C
  • Question
Find duplicates when pulling data from other sheets
Replies
3
Views
176
Trebor76
T
W
  • Question
Excel Formula or VBA to return the corresponding values based on several criteria
Replies
0
Views
281
woah
W
K
  • Locked
  • Question
Search button with the ability to find, edit and add value +VBA
Replies
3
Views
293
Fluff
Fluff
A
  • Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
1K
Attrazion
A
T
  • Question
Duplication of Excel sheets to a newly created file
Replies
0
Views
250
Toyall
T

Forum statistics

Threads
1,214,979
Messages
6,122,552
Members
449,088
Latest member
davidcom

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