count the duplicate entries with the help of Macro

[chirag_patel5141]

Hi All,
I want to Count the Duplicate entries in the column "SYMBOL" with the refeance of column "EXPIRY_DT" and also want to number them like...
IF 31-Mar-16 than BANKNIFTY-I , 28-Apr-16 than BANKNIFTY-II & 26-MAY-16 than BANKNIFTY-III

INSTRUMENT	"SYMBOL"	"EXPIRY_DT"	OPEN	HIGH	LOW	CLOSE	OPEN_INT	CHG_IN_OI"	TIMESTAMP"
FUTIDX	BANKNIFTY	31-Mar-16	15745	15965.7	15740	15941.55	2350350	194280	21-Mar-16
FUTIDX	BANKNIFTY	28-Apr-16	15816	16036	15816	16017.55	294210	23010	21-Mar-16
FUTIDX	BANKNIFTY	26-May-16	15876.1	16049.95	15850	16042.95	104490	840	21-Mar-16
FUTIDX	DJIA	13-Apr-16	17460	17512.5	17440	17507.5	16950	3060	21-Mar-16
FUTIDX	DJIA	20-May-16	0	0	0	16740	0	0	21-Mar-16
FUTIDX	DJIA	17-Jun-16	0	0	0	16385	30	0	21-Mar-16
FUTIDX	DJIA	16-Sep-16	0	0	0	16702.5	30	0	21-Mar-16
FUTIDX	DJIA	16-Dec-16	0	0	0	18835	0	0	21-Mar-16
FUTIDX	DJIA	17-Mar-17	0	0	0	18965	0	0	21-Mar-16
FUTIDX	FTSE100	13-Apr-16	6398	6398	6398	6398	75	75	21-Mar-16
FUTIDX	FTSE100	20-May-16	0	0	0	6091	0	0	21-Mar-16
FUTIDX	FTSE100	17-Jun-16	0	0	0	7242	0	0	21-Mar-16
FUTIDX	FTSE100	16-Sep-16	0	0	0	6637	0	0	21-Mar-16
FUTIDX	FTSE100	16-Dec-16	0	0	0	6570	0	0	21-Mar-16
FUTIDX	FTSE100	17-Mar-17	0	0	0	6728	0	0	21-Mar-16
FUTIDX	NIFTY	31-Mar-16	7623.5	7723.2	7615.2	7713.35	21508425	-751800	21-Mar-16
FUTIDX	NIFTY	28-Apr-16	7664.9	7761.4	7658.4	7750.8	4290900	391575	21-Mar-16
FUTIDX	NIFTY	26-May-16	7677.45	7773.4	7672.15	7763.7	1075425	77775	21-Mar-16
FUTIDX	NIFTYINFRA	31-Mar-16	0	0	0	2565	150	0	21-Mar-16
FUTIDX	NIFTYINFRA	28-Apr-16	0	0	0	2440.95	0	0	21-Mar-16
FUTIDX	NIFTYINFRA	26-May-16	0	0	0	2330.45	0	0	21-Mar-16

<tbody>
</tbody>

 

[chirag_patel5141]

In above example If Symbol of BANKNIFTY comes with any other EXPIRY_DT (date), for example 29-Sep-16 than the New Symbol will be BANKNIFTY-IV ..

 

[MickG]

Not Quite Right !!!!

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG06Jun39
[COLOR="Navy"]Dim[/COLOR] Dn      [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Rng     [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dic     [COLOR="Navy"]As[/COLOR] Object
[COLOR="Navy"]Dim[/COLOR] k       [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Dim[/COLOR] p       [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Dim[/COLOR] c       [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
 
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("B2"), Range("B" & Rows.Count).End(xlUp))
 [COLOR="Navy"]Set[/COLOR] Dic = CreateObject("Scripting.Dictionary")
    Dic.CompareMode = 1
   [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
            [COLOR="Navy"]If[/COLOR] Not Dic.exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
                [COLOR="Navy"]Set[/COLOR] Dic(Dn.Value) = CreateObject("Scripting.Dictionary")
            [COLOR="Navy"]End[/COLOR] If
        
        [COLOR="Navy"]If[/COLOR] Not Dic(Dn.Value).exists(Dn.Offset(, 1).Value) [COLOR="Navy"]Then[/COLOR]
                Dic(Dn.Value).Add (Dn.Offset(, 1).Value), Dn
        [COLOR="Navy"]Else[/COLOR]
                [COLOR="Navy"]Set[/COLOR] Dic(Dn.Value).Item(Dn.Offset(, 1).Value) = _
                Union(Dic(Dn.Value).Item(Dn.Offset(, 1).Value), Dn)
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] k [COLOR="Navy"]In[/COLOR] Dic.Keys
       c = 0
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] p [COLOR="Navy"]In[/COLOR] Dic(k)
        c = c + 1
         [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Dic(k).Item(p)
           Dn.Value = Dn.Value & "-" & WorksheetFunction.Roman(c)
         [COLOR="Navy"]Next[/COLOR] Dn
    [COLOR="Navy"]Next[/COLOR] p
[COLOR="Navy"]Next[/COLOR] k
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[chirag_patel5141]

Tnx MickG for all ur time and effort ...that code works perfect and done the trick ....tnx a ton man ....

 

[MickG]

You're welcome