count the duplicate entries with the help of Macro

C

chirag_patel5141

New Member
Joined
Jun 4, 2016
Messages
10
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


[TABLE="width: 664"]
<tbody>[TR]
[TD]INSTRUMENT[/TD]
[TD]"SYMBOL"[/TD]
[TD]"EXPIRY_DT"[/TD]
[TD]OPEN[/TD]
[TD]HIGH[/TD]
[TD]LOW[/TD]
[TD]CLOSE[/TD]
[TD]OPEN_INT[/TD]
[TD]CHG_IN_OI"[/TD]
[TD]TIMESTAMP"[/TD]
[/TR]
[TR]
[TD]FUTIDX[/TD]
[TD]BANKNIFTY[/TD]
[TD="align: right"]31-Mar-16[/TD]
[TD="align: right"]15745[/TD]
[TD="align: right"]15965.7[/TD]
[TD="align: right"]15740[/TD]
[TD="align: right"]15941.55[/TD]
[TD="align: right"]2350350[/TD]
[TD="align: right"]194280[/TD]
[TD="align: right"]21-Mar-16[/TD]
[/TR]
[TR]
[TD]FUTIDX[/TD]
[TD]BANKNIFTY[/TD]
[TD="align: right"]28-Apr-16[/TD]
[TD="align: right"]15816[/TD]
[TD="align: right"]16036[/TD]
[TD="align: right"]15816[/TD]
[TD="align: right"]16017.55[/TD]
[TD="align: right"]294210[/TD]
[TD="align: right"]23010[/TD]
[TD="align: right"]21-Mar-16[/TD]
[/TR]
[TR]
[TD]FUTIDX[/TD]
[TD]BANKNIFTY[/TD]
[TD="align: right"]26-May-16[/TD]
[TD="align: right"]15876.1[/TD]
[TD="align: right"]16049.95[/TD]
[TD="align: right"]15850[/TD]
[TD="align: right"]16042.95[/TD]
[TD="align: right"]104490[/TD]
[TD="align: right"]840[/TD]
[TD="align: right"]21-Mar-16[/TD]
[/TR]
[TR]
[TD]FUTIDX[/TD]
[TD]DJIA[/TD]
[TD="align: right"]13-Apr-16[/TD]
[TD="align: right"]17460[/TD]
[TD="align: right"]17512.5[/TD]
[TD="align: right"]17440[/TD]
[TD="align: right"]17507.5[/TD]
[TD="align: right"]16950[/TD]
[TD="align: right"]3060[/TD]
[TD="align: right"]21-Mar-16[/TD]
[/TR]
[TR]
[TD]FUTIDX[/TD]
[TD]DJIA[/TD]
[TD="align: right"]20-May-16[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]16740[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]21-Mar-16[/TD]
[/TR]
[TR]
[TD]FUTIDX[/TD]
[TD]DJIA[/TD]
[TD="align: right"]17-Jun-16[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]16385[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]21-Mar-16[/TD]
[/TR]
[TR]
[TD]FUTIDX[/TD]
[TD]DJIA[/TD]
[TD="align: right"]16-Sep-16[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]16702.5[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]21-Mar-16[/TD]
[/TR]
[TR]
[TD]FUTIDX[/TD]
[TD]DJIA[/TD]
[TD="align: right"]16-Dec-16[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]18835[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]21-Mar-16[/TD]
[/TR]
[TR]
[TD]FUTIDX[/TD]
[TD]DJIA[/TD]
[TD="align: right"]17-Mar-17[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]18965[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]21-Mar-16[/TD]
[/TR]
[TR]
[TD]FUTIDX[/TD]
[TD]FTSE100[/TD]
[TD="align: right"]13-Apr-16[/TD]
[TD="align: right"]6398[/TD]
[TD="align: right"]6398[/TD]
[TD="align: right"]6398[/TD]
[TD="align: right"]6398[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]21-Mar-16[/TD]
[/TR]
[TR]
[TD]FUTIDX[/TD]
[TD]FTSE100[/TD]
[TD="align: right"]20-May-16[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]6091[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]21-Mar-16[/TD]
[/TR]
[TR]
[TD]FUTIDX[/TD]
[TD]FTSE100[/TD]
[TD="align: right"]17-Jun-16[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7242[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]21-Mar-16[/TD]
[/TR]
[TR]
[TD]FUTIDX[/TD]
[TD]FTSE100[/TD]
[TD="align: right"]16-Sep-16[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]6637[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]21-Mar-16[/TD]
[/TR]
[TR]
[TD]FUTIDX[/TD]
[TD]FTSE100[/TD]
[TD="align: right"]16-Dec-16[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]6570[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]21-Mar-16[/TD]
[/TR]
[TR]
[TD]FUTIDX[/TD]
[TD]FTSE100[/TD]
[TD="align: right"]17-Mar-17[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]6728[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]21-Mar-16[/TD]
[/TR]
[TR]
[TD]FUTIDX[/TD]
[TD]NIFTY[/TD]
[TD="align: right"]31-Mar-16[/TD]
[TD="align: right"]7623.5[/TD]
[TD="align: right"]7723.2[/TD]
[TD="align: right"]7615.2[/TD]
[TD="align: right"]7713.35[/TD]
[TD="align: right"]21508425[/TD]
[TD="align: right"]-751800[/TD]
[TD="align: right"]21-Mar-16[/TD]
[/TR]
[TR]
[TD]FUTIDX[/TD]
[TD]NIFTY[/TD]
[TD="align: right"]28-Apr-16[/TD]
[TD="align: right"]7664.9[/TD]
[TD="align: right"]7761.4[/TD]
[TD="align: right"]7658.4[/TD]
[TD="align: right"]7750.8[/TD]
[TD="align: right"]4290900[/TD]
[TD="align: right"]391575[/TD]
[TD="align: right"]21-Mar-16[/TD]
[/TR]
[TR]
[TD]FUTIDX[/TD]
[TD]NIFTY[/TD]
[TD="align: right"]26-May-16[/TD]
[TD="align: right"]7677.45[/TD]
[TD="align: right"]7773.4[/TD]
[TD="align: right"]7672.15[/TD]
[TD="align: right"]7763.7[/TD]
[TD="align: right"]1075425[/TD]
[TD="align: right"]77775[/TD]
[TD="align: right"]21-Mar-16[/TD]
[/TR]
[TR]
[TD]FUTIDX[/TD]
[TD]NIFTYINFRA[/TD]
[TD="align: right"]31-Mar-16[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2565[/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]21-Mar-16[/TD]
[/TR]
[TR]
[TD]FUTIDX[/TD]
[TD]NIFTYINFRA[/TD]
[TD="align: right"]28-Apr-16[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2440.95[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]21-Mar-16[/TD]
[/TR]
[TR]
[TD]FUTIDX[/TD]
[TD]NIFTYINFRA[/TD]
[TD="align: right"]26-May-16[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2330.45[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]21-Mar-16[/TD]
[/TR]
</tbody>[/TABLE]
 
Sort by date Sort by votes
the best solution I got till now is like this but its still not proper ... I have to run this macro in three part ..can someone make it in a single part ???

Thanx in Advance

Sub Part1()
'this macro is for numbering the duplicate value
Dim Rng As Range
Dim Dn As Range
Set Rng = Range(Range("B1"), Range("B" & Rows.Count).End(xlUp))
With CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
For Each Dn In Rng
If Not .Exists(Dn.Value) Then
.Add Dn.Value, 0
Else
If .Item(Dn.Value) = 0 Then
.Item(Dn.Value) = .Item(Dn.Value) + 1
Dn.Value = Dn.Value & "-" & WorksheetFunction.Roman(2, 0)
End If
End If
Next Dn
End With
Application.Run ("Part2")
End Sub
Sub Part2()
Dim Rng As Range
Dim Dn As Range
Set Rng = Range(Range("B1"), Range("B" & Rows.Count).End(xlUp))
With CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
For Each Dn In Rng
If Not .Exists(Dn.Value) Then
.Add Dn.Value, 0
Else
If .Item(Dn.Value) = 0 Then
.Item(Dn.Value) = .Item(Dn.Value) + 1
Dn.Value = Dn.Value & "-" & WorksheetFunction.Roman(3, 0)
End If
End If
Next Dn
End With
Application.Run ("Part3")
End Sub
Sub Part3()
Dim Rng As Range
Dim Dn As Range
Set Rng = Range(Range("B1"), Range("B" & Rows.Count).End(xlUp))
With CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
For Each Dn In Rng
If Not .Exists(Dn.Value) Then
.Add Dn.Value, 0
Else
If .Item(Dn.Value) = 0 Then
.Item(Dn.Value) = .Item(Dn.Value) + 1
Dn.Value = Dn.Value & "-" & WorksheetFunction.Roman(4, 0)
End If
End If
Next Dn
End With
End Sub
 
Upvote 0
Hi chirag_patel5141, welcome to the board.

Untested, but maybe something like this...

Code: 
Sub Part1()
'   This macro is for numbering the duplicate value.
    Dim Rng As Range
    Dim Dn As Range
    Set Rng = Range(Range("B1"), Range("B" & Rows.Count).End(xlUp))
    With CreateObject("scripting.dictionary")
        .CompareMode = vbTextCompare
        For Each Dn In Rng
            If Not .Exists(Dn.Value) Then
                .Add Dn.Value, 0
            Else
                If .Item(Dn.Value) = 0 Then
                    .Item(Dn.Value) = .Item(Dn.Value) + 1
'                   Dn.Value = Dn.Value & "-" & WorksheetFunction.Roman(2, 0)
                   Dn.Value.Resize(3, 0) = Dn.Value & "-" & WorksheetFunction.Roman(2, 0)
                End If
            End If
        Next Dn
    End With
End Sub

I hope this helps!
 
Upvote 0
Try this:-
Code: 
[COLOR="Navy"]Sub[/COLOR] MG05Jun25
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("B2"), Range("B" & 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"]Set[/COLOR] .Item(Dn.Value) = Union(.Item(Dn.Value), Dn)
            [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]Next[/COLOR] Dn
    
[COLOR="Navy"]Dim[/COLOR] K [COLOR="Navy"]As[/COLOR] Variant, p [COLOR="Navy"]As[/COLOR] Range, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] .keys
    c = 0
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] p [COLOR="Navy"]In[/COLOR] .Item(K)
         [COLOR="Navy"]If[/COLOR] .Item(K).Count > 1 [COLOR="Navy"]Then[/COLOR]
            c = c + 1
            p = p & "-" & WorksheetFunction.Roman(c)
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] p
[COLOR="Navy"]Next[/COLOR] K
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
Hi Mick,

tnx for ur time and effort, your code work but the problem I face is my data base is Big and Symbol of BANKNIFTY comes more than 100 times in that database ...so ur code keep counting that SYMBOL. But If u consider "EXPIRY_DT" column which is 31-Mar-16 , 28-Apr-16 & 26-May-16 than BANKNIFTY Symbol comes only 3 times in that Database so the BANKNIFTY count will be BANKNIFTY-I, BANKNIFTY-II & BANKNIFTY-III only ...
 
Upvote 0
Hi Mick,

Same with the SYMBOL DJIA as well ...that symbol also comes more than 100 times but if u consider "EXPIRY_DT" column than DJIA symbol comes only 6 times so I want a result like DJIA-I,DJIA-II,DJIA-III,DJIA-IV,DJIA-V & DJIA-VI...Same with SYMBOL "FTSE100" ...it also comes only 6 times ...NIFTY Symbol comes only 3 times...So I want result for NIFTY is like NIFTY-I,NIFTY-II & NIFTY-III ....
 
Upvote 0
Try this:-
This code will start the count again where a Name from the "SYMBOL" column repeats but is not part of the Previous group.
If this does not work for you , please show an example of the expected result for, for multi ranges of the same "Symbol" name.
Code: 
[COLOR="Navy"]Sub[/COLOR] MG06Jun50
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("B2"), Range("B" & 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"]Set[/COLOR] .Item(Dn.Value) = Union(.Item(Dn.Value), Dn)
            [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]Next[/COLOR] Dn
    
[COLOR="Navy"]Dim[/COLOR] K [COLOR="Navy"]As[/COLOR] Variant, p [COLOR="Navy"]As[/COLOR] Range, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] R [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] .keys
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] p [COLOR="Navy"]In[/COLOR] .Item(K).Areas
         c = 0
        [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] R [COLOR="Navy"]In[/COLOR] p
          [COLOR="Navy"]If[/COLOR] .Item(K).Count > 1 [COLOR="Navy"]Then[/COLOR]
            c = c + 1
            R = R & "-" & WorksheetFunction.Roman(c)
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] R
    [COLOR="Navy"]Next[/COLOR] p
[COLOR="Navy"]Next[/COLOR] K
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
Here is the Original Database.....

[TABLE="width: 1041"]
<colgroup><col><col><col><col span="12"></colgroup><tbody>[TR]
[TD]INSTRUMENT[/TD]
[TD]SYMBOL[/TD]
[TD]EXPIRY_DT[/TD]
[TD]STRIKE_PR[/TD]
[TD]OPTION_TYP[/TD]
[TD]OPEN[/TD]
[TD]HIGH[/TD]
[TD]LOW[/TD]
[TD]CLOSE[/TD]
[TD]SETTLE_PR[/TD]
[TD]CONTRACTS[/TD]
[TD]VAL_INLAKH[/TD]
[TD]OPEN_INT[/TD]
[TD]CHG_IN_OI[/TD]
[TD]TIMESTAMP[/TD]
[/TR]
[TR]
[TD]FUTIDX[/TD]
[TD]BANKNIFTY[/TD]
[TD="align: right"]30-Jun-16[/TD]
[TD="align: right"]0[/TD]
[TD]XX[/TD]
[TD="align: right"]17624.4[/TD]
[TD="align: right"]17738.5[/TD]
[TD="align: right"]17615[/TD]
[TD="align: right"]17655.85[/TD]
[TD="align: right"]17655.85[/TD]
[TD="align: right"]77226[/TD]
[TD="align: right"]409560.7[/TD]
[TD="align: right"]1724790[/TD]
[TD="align: right"]-39690[/TD]
[TD="align: right"]03-Jun-16[/TD]
[/TR]
[TR]
[TD]FUTIDX[/TD]
[TD]BANKNIFTY[/TD]
[TD="align: right"]28-Jul-16[/TD]
[TD="align: right"]0[/TD]
[TD]XX[/TD]
[TD="align: right"]17740[/TD]
[TD="align: right"]17800[/TD]
[TD="align: right"]17710[/TD]
[TD="align: right"]17725.7[/TD]
[TD="align: right"]17725.7[/TD]
[TD="align: right"]923[/TD]
[TD="align: right"]6554.36[/TD]
[TD="align: right"]86000[/TD]
[TD="align: right"]1360[/TD]
[TD="align: right"]03-Jun-16[/TD]
[/TR]
[TR]
[TD]FUTIDX[/TD]
[TD]BANKNIFTY[/TD]
[TD="align: right"]25-Aug-16[/TD]
[TD="align: right"]0[/TD]
[TD]XX[/TD]
[TD="align: right"]17799[/TD]
[TD="align: right"]17860[/TD]
[TD="align: right"]17780[/TD]
[TD="align: right"]17800[/TD]
[TD="align: right"]17800[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]313.69[/TD]
[TD="align: right"]5480[/TD]
[TD="align: right"]120[/TD]
[TD="align: right"]03-Jun-16[/TD]
[/TR]
[TR]
[TD]OPTIDX[/TD]
[TD]BANKNIFTY[/TD]
[TD="align: right"]30-Jun-16[/TD]
[TD="align: right"]14000[/TD]
[TD]CE[/TD]
[TD="align: right"]3617[/TD]
[TD="align: right"]3665.25[/TD]
[TD="align: right"]3600.5[/TD]
[TD="align: right"]3608[/TD]
[TD="align: right"]3608[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]89.94[/TD]
[TD="align: right"]54120[/TD]
[TD="align: right"]-240[/TD]
[TD="align: right"]03-Jun-16[/TD]
[/TR]
[TR]
[TD]OPTIDX[/TD]
[TD]BANKNIFTY[/TD]
[TD="align: right"]30-Jun-16[/TD]
[TD="align: right"]14500[/TD]
[TD]CE[/TD]
[TD="align: right"]3111.2[/TD]
[TD="align: right"]3111.2[/TD]
[TD="align: right"]3111.2[/TD]
[TD="align: right"]3111.2[/TD]
[TD="align: right"]3111.2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5.28[/TD]
[TD="align: right"]8190[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]03-Jun-16[/TD]
[/TR]
[TR]
[TD]OPTIDX[/TD]
[TD]BANKNIFTY[/TD]
[TD="align: right"]30-Jun-16[/TD]
[TD="align: right"]15000[/TD]
[TD]CE[/TD]
[TD="align: right"]2650.3[/TD]
[TD="align: right"]2680[/TD]
[TD="align: right"]2650.3[/TD]
[TD="align: right"]2668.05[/TD]
[TD="align: right"]2668.05[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]233.2[/TD]
[TD="align: right"]27570[/TD]
[TD="align: right"]1290[/TD]
[TD="align: right"]03-Jun-16[/TD]
[/TR]
[TR]
[TD]OPTIDX[/TD]
[TD]BANKNIFTY[/TD]
[TD="align: right"]30-Jun-16[/TD]
[TD="align: right"]15500[/TD]
[TD]CE[/TD]
[TD="align: right"]2227.85[/TD]
[TD="align: right"]2227.85[/TD]
[TD="align: right"]2189[/TD]
[TD="align: right"]2190.85[/TD]
[TD="align: right"]2190.85[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]15.93[/TD]
[TD="align: right"]27900[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]03-Jun-16[/TD]
[/TR]
[TR]
[TD]OPTIDX[/TD]
[TD]BANKNIFTY[/TD]
[TD="align: right"]30-Jun-16[/TD]
[TD="align: right"]15700[/TD]
[TD]CE[/TD]
[TD="align: right"]1964.15[/TD]
[TD="align: right"]1964.15[/TD]
[TD="align: right"]1964.15[/TD]
[TD="align: right"]1964.15[/TD]
[TD="align: right"]1964.15[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5.29[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]03-Jun-16[/TD]
[/TR]
[TR]
[TD]OPTIDX[/TD]
[TD]BANKNIFTY[/TD]
[TD="align: right"]30-Jun-16[/TD]
[TD="align: right"]16000[/TD]
[TD]CE[/TD]
[TD="align: right"]1699[/TD]
[TD="align: right"]1730.1[/TD]
[TD="align: right"]1660[/TD]
[TD="align: right"]1660[/TD]
[TD="align: right"]1660[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]318.54[/TD]
[TD="align: right"]53880[/TD]
[TD="align: right"]420[/TD]
[TD="align: right"]03-Jun-16[/TD]
[/TR]
[TR]
[TD]OPTIDX[/TD]
[TD]BANKNIFTY[/TD]
[TD="align: right"]30-Jun-16[/TD]
[TD="align: right"]16100[/TD]
[TD]CE[/TD]
[TD="align: right"]1561.8[/TD]
[TD="align: right"]1561.8[/TD]
[TD="align: right"]1561.8[/TD]
[TD="align: right"]1561.8[/TD]
[TD="align: right"]1561.8[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5.29[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]03-Jun-16[/TD]
[/TR]
[TR]
[TD]OPTIDX[/TD]
[TD]BANKNIFTY[/TD]
[TD="align: right"]30-Jun-16[/TD]
[TD="align: right"]16300[/TD]
[TD]CE[/TD]
[TD="align: right"]1444.4[/TD]
[TD="align: right"]1444.4[/TD]
[TD="align: right"]1444.4[/TD]
[TD="align: right"]1444.4[/TD]
[TD="align: right"]1444.4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5.32[/TD]
[TD="align: right"]240[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]03-Jun-16[/TD]
[/TR]
[TR]
[TD]OPTIDX[/TD]
[TD]BANKNIFTY[/TD]
[TD="align: right"]30-Jun-16[/TD]
[TD="align: right"]18600[/TD]
[TD]PE[/TD]
[TD="align: right"]1017.1[/TD]
[TD="align: right"]1017.1[/TD]
[TD="align: right"]1017.1[/TD]
[TD="align: right"]1017.1[/TD]
[TD="align: right"]1017.1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5.88[/TD]
[TD="align: right"]990[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]03-Jun-16[/TD]
[/TR]
[TR]
[TD]OPTIDX[/TD]
[TD]BANKNIFTY[/TD]
[TD="align: right"]28-Jul-16[/TD]
[TD="align: right"]16000[/TD]
[TD]CE[/TD]
[TD="align: right"]1710[/TD]
[TD="align: right"]1790[/TD]
[TD="align: right"]1710[/TD]
[TD="align: right"]1790[/TD]
[TD="align: right"]1790[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]78.04[/TD]
[TD="align: right"]6880[/TD]
[TD="align: right"]440[/TD]
[TD="align: right"]03-Jun-16[/TD]
[/TR]
[TR]
[TD]OPTIDX[/TD]
[TD]BANKNIFTY[/TD]
[TD="align: right"]28-Jul-16[/TD]
[TD="align: right"]17500[/TD]
[TD]CE[/TD]
[TD="align: right"]650[/TD]
[TD="align: right"]675[/TD]
[TD="align: right"]650[/TD]
[TD="align: right"]675[/TD]
[TD="align: right"]675[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]43.59[/TD]
[TD="align: right"]520[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]03-Jun-16[/TD]
[/TR]
[TR]
[TD]OPTIDX[/TD]
[TD]BANKNIFTY[/TD]
[TD="align: right"]28-Jul-16[/TD]
[TD="align: right"]18000[/TD]
[TD]CE[/TD]
[TD="align: right"]398.65[/TD]
[TD="align: right"]399[/TD]
[TD="align: right"]372.4[/TD]
[TD="align: right"]377.65[/TD]
[TD="align: right"]377.65[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]58.84[/TD]
[TD="align: right"]3120[/TD]
[TD="align: right"]160[/TD]
[TD="align: right"]03-Jun-16[/TD]
[/TR]
[TR]
[TD]OPTIDX[/TD]
[TD]BANKNIFTY[/TD]
[TD="align: right"]28-Jul-16[/TD]
[TD="align: right"]15000[/TD]
[TD]PE[/TD]
[TD="align: right"]42[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]66.17[/TD]
[TD="align: right"]520[/TD]
[TD="align: right"]120[/TD]
[TD="align: right"]03-Jun-16[/TD]
[/TR]
[TR]
[TD]OPTIDX[/TD]
[TD]BANKNIFTY[/TD]
[TD="align: right"]28-Jul-16[/TD]
[TD="align: right"]16000[/TD]
[TD]PE[/TD]
[TD="align: right"]94[/TD]
[TD="align: right"]97[/TD]
[TD="align: right"]86.2[/TD]
[TD="align: right"]89.55[/TD]
[TD="align: right"]89.55[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]218.87[/TD]
[TD="align: right"]5520[/TD]
[TD="align: right"]160[/TD]
[TD="align: right"]03-Jun-16[/TD]
[/TR]
[TR]
[TD]OPTIDX[/TD]
[TD]BANKNIFTY[/TD]
[TD="align: right"]28-Jul-16[/TD]
[TD="align: right"]16500[/TD]
[TD]PE[/TD]
[TD="align: right"]141[/TD]
[TD="align: right"]141[/TD]
[TD="align: right"]140[/TD]
[TD="align: right"]140[/TD]
[TD="align: right"]140[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]19.96[/TD]
[TD="align: right"]320[/TD]
[TD="align: right"]-80[/TD]
[TD="align: right"]03-Jun-16[/TD]
[/TR]
[TR]
[TD]OPTIDX[/TD]
[TD]BANKNIFTY[/TD]
[TD="align: right"]28-Jul-16[/TD]
[TD="align: right"]17000[/TD]
[TD]PE[/TD]
[TD="align: right"]277.2[/TD]
[TD="align: right"]277.2[/TD]
[TD="align: right"]236.15[/TD]
[TD="align: right"]256[/TD]
[TD="align: right"]256[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]48.31[/TD]
[TD="align: right"]640[/TD]
[TD="align: right"]120[/TD]
[TD="align: right"]03-Jun-16[/TD]
[/TR]
[TR]
[TD]OPTIDX[/TD]
[TD]BANKNIFTY[/TD]
[TD="align: right"]28-Jul-16[/TD]
[TD="align: right"]17500[/TD]
[TD]PE[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]7.2[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]03-Jun-16[/TD]
[/TR]
</tbody>[/TABLE]


And I want to Change SYMBOL Column in New Database like this ...

[TABLE="width: 1041"]
<colgroup><col><col><col><col span="12"></colgroup><tbody>[TR]
[TD]INSTRUMENT[/TD]
[TD]SYMBOL[/TD]
[TD]EXPIRY_DT[/TD]
[TD]STRIKE_PR[/TD]
[TD]OPTION_TYP[/TD]
[TD]OPEN[/TD]
[TD]HIGH[/TD]
[TD]LOW[/TD]
[TD]CLOSE[/TD]
[TD]SETTLE_PR[/TD]
[TD]CONTRACTS[/TD]
[TD]VAL_INLAKH[/TD]
[TD]OPEN_INT[/TD]
[TD]CHG_IN_OI[/TD]
[TD]TIMESTAMP[/TD]
[/TR]
[TR]
[TD]FUTIDX[/TD]
[TD]BANKNIFTY-I[/TD]
[TD="align: right"]30-Jun-16[/TD]
[TD="align: right"]0[/TD]
[TD]XX[/TD]
[TD="align: right"]17624.4[/TD]
[TD="align: right"]17738.5[/TD]
[TD="align: right"]17615[/TD]
[TD="align: right"]17655.85[/TD]
[TD="align: right"]17655.85[/TD]
[TD="align: right"]77226[/TD]
[TD="align: right"]409560.7[/TD]
[TD="align: right"]1724790[/TD]
[TD="align: right"]-39690[/TD]
[TD="align: right"]03-Jun-16[/TD]
[/TR]
[TR]
[TD]FUTIDX[/TD]
[TD]BANKNIFTY-II[/TD]
[TD="align: right"]28-Jul-16[/TD]
[TD="align: right"]0[/TD]
[TD]XX[/TD]
[TD="align: right"]17740[/TD]
[TD="align: right"]17800[/TD]
[TD="align: right"]17710[/TD]
[TD="align: right"]17725.7[/TD]
[TD="align: right"]17725.7[/TD]
[TD="align: right"]923[/TD]
[TD="align: right"]6554.36[/TD]
[TD="align: right"]86000[/TD]
[TD="align: right"]1360[/TD]
[TD="align: right"]03-Jun-16[/TD]
[/TR]
[TR]
[TD]FUTIDX[/TD]
[TD]BANKNIFTY-III[/TD]
[TD="align: right"]25-Aug-16[/TD]
[TD="align: right"]0[/TD]
[TD]XX[/TD]
[TD="align: right"]17799[/TD]
[TD="align: right"]17860[/TD]
[TD="align: right"]17780[/TD]
[TD="align: right"]17800[/TD]
[TD="align: right"]17800[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]313.69[/TD]
[TD="align: right"]5480[/TD]
[TD="align: right"]120[/TD]
[TD="align: right"]03-Jun-16[/TD]
[/TR]
[TR]
[TD]OPTIDX[/TD]
[TD]BANKNIFTY-I[/TD]
[TD="align: right"]30-Jun-16[/TD]
[TD="align: right"]14000[/TD]
[TD]CE[/TD]
[TD="align: right"]3617[/TD]
[TD="align: right"]3665.25[/TD]
[TD="align: right"]3600.5[/TD]
[TD="align: right"]3608[/TD]
[TD="align: right"]3608[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]89.94[/TD]
[TD="align: right"]54120[/TD]
[TD="align: right"]-240[/TD]
[TD="align: right"]03-Jun-16[/TD]
[/TR]
[TR]
[TD]OPTIDX[/TD]
[TD]BANKNIFTY-I[/TD]
[TD="align: right"]30-Jun-16[/TD]
[TD="align: right"]14500[/TD]
[TD]CE[/TD]
[TD="align: right"]3111.2[/TD]
[TD="align: right"]3111.2[/TD]
[TD="align: right"]3111.2[/TD]
[TD="align: right"]3111.2[/TD]
[TD="align: right"]3111.2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5.28[/TD]
[TD="align: right"]8190[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]03-Jun-16[/TD]
[/TR]
[TR]
[TD]OPTIDX[/TD]
[TD]BANKNIFTY-I[/TD]
[TD="align: right"]30-Jun-16[/TD]
[TD="align: right"]15000[/TD]
[TD]CE[/TD]
[TD="align: right"]2650.3[/TD]
[TD="align: right"]2680[/TD]
[TD="align: right"]2650.3[/TD]
[TD="align: right"]2668.05[/TD]
[TD="align: right"]2668.05[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]233.2[/TD]
[TD="align: right"]27570[/TD]
[TD="align: right"]1290[/TD]
[TD="align: right"]03-Jun-16[/TD]
[/TR]
[TR]
[TD]OPTIDX[/TD]
[TD]BANKNIFTY-I[/TD]
[TD="align: right"]30-Jun-16[/TD]
[TD="align: right"]15500[/TD]
[TD]CE[/TD]
[TD="align: right"]2227.85[/TD]
[TD="align: right"]2227.85[/TD]
[TD="align: right"]2189[/TD]
[TD="align: right"]2190.85[/TD]
[TD="align: right"]2190.85[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]15.93[/TD]
[TD="align: right"]27900[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]03-Jun-16[/TD]
[/TR]
[TR]
[TD]OPTIDX[/TD]
[TD]BANKNIFTY-I[/TD]
[TD="align: right"]30-Jun-16[/TD]
[TD="align: right"]15700[/TD]
[TD]CE[/TD]
[TD="align: right"]1964.15[/TD]
[TD="align: right"]1964.15[/TD]
[TD="align: right"]1964.15[/TD]
[TD="align: right"]1964.15[/TD]
[TD="align: right"]1964.15[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5.29[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]03-Jun-16[/TD]
[/TR]
[TR]
[TD]OPTIDX[/TD]
[TD]BANKNIFTY-I[/TD]
[TD="align: right"]30-Jun-16[/TD]
[TD="align: right"]16000[/TD]
[TD]CE[/TD]
[TD="align: right"]1699[/TD]
[TD="align: right"]1730.1[/TD]
[TD="align: right"]1660[/TD]
[TD="align: right"]1660[/TD]
[TD="align: right"]1660[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]318.54[/TD]
[TD="align: right"]53880[/TD]
[TD="align: right"]420[/TD]
[TD="align: right"]03-Jun-16[/TD]
[/TR]
[TR]
[TD]OPTIDX[/TD]
[TD]BANKNIFTY-I[/TD]
[TD="align: right"]30-Jun-16[/TD]
[TD="align: right"]16100[/TD]
[TD]CE[/TD]
[TD="align: right"]1561.8[/TD]
[TD="align: right"]1561.8[/TD]
[TD="align: right"]1561.8[/TD]
[TD="align: right"]1561.8[/TD]
[TD="align: right"]1561.8[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5.29[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]03-Jun-16[/TD]
[/TR]
[TR]
[TD]OPTIDX[/TD]
[TD]BANKNIFTY-I[/TD]
[TD="align: right"]30-Jun-16[/TD]
[TD="align: right"]16300[/TD]
[TD]CE[/TD]
[TD="align: right"]1444.4[/TD]
[TD="align: right"]1444.4[/TD]
[TD="align: right"]1444.4[/TD]
[TD="align: right"]1444.4[/TD]
[TD="align: right"]1444.4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5.32[/TD]
[TD="align: right"]240[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]03-Jun-16[/TD]
[/TR]
[TR]
[TD]OPTIDX[/TD]
[TD]BANKNIFTY-I[/TD]
[TD="align: right"]30-Jun-16[/TD]
[TD="align: right"]18600[/TD]
[TD]PE[/TD]
[TD="align: right"]1017.1[/TD]
[TD="align: right"]1017.1[/TD]
[TD="align: right"]1017.1[/TD]
[TD="align: right"]1017.1[/TD]
[TD="align: right"]1017.1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5.88[/TD]
[TD="align: right"]990[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]03-Jun-16[/TD]
[/TR]
[TR]
[TD]OPTIDX[/TD]
[TD]BANKNIFTY-II[/TD]
[TD="align: right"]28-Jul-16[/TD]
[TD="align: right"]16000[/TD]
[TD]CE[/TD]
[TD="align: right"]1710[/TD]
[TD="align: right"]1790[/TD]
[TD="align: right"]1710[/TD]
[TD="align: right"]1790[/TD]
[TD="align: right"]1790[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]78.04[/TD]
[TD="align: right"]6880[/TD]
[TD="align: right"]440[/TD]
[TD="align: right"]03-Jun-16[/TD]
[/TR]
[TR]
[TD]OPTIDX[/TD]
[TD]BANKNIFTY-II[/TD]
[TD="align: right"]28-Jul-16[/TD]
[TD="align: right"]17500[/TD]
[TD]CE[/TD]
[TD="align: right"]650[/TD]
[TD="align: right"]675[/TD]
[TD="align: right"]650[/TD]
[TD="align: right"]675[/TD]
[TD="align: right"]675[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]43.59[/TD]
[TD="align: right"]520[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]03-Jun-16[/TD]
[/TR]
[TR]
[TD]OPTIDX[/TD]
[TD]BANKNIFTY-II[/TD]
[TD="align: right"]28-Jul-16[/TD]
[TD="align: right"]18000[/TD]
[TD]CE[/TD]
[TD="align: right"]398.65[/TD]
[TD="align: right"]399[/TD]
[TD="align: right"]372.4[/TD]
[TD="align: right"]377.65[/TD]
[TD="align: right"]377.65[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]58.84[/TD]
[TD="align: right"]3120[/TD]
[TD="align: right"]160[/TD]
[TD="align: right"]03-Jun-16[/TD]
[/TR]
[TR]
[TD]OPTIDX[/TD]
[TD]BANKNIFTY-II[/TD]
[TD="align: right"]28-Jul-16[/TD]
[TD="align: right"]15000[/TD]
[TD]PE[/TD]
[TD="align: right"]42[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]66.17[/TD]
[TD="align: right"]520[/TD]
[TD="align: right"]120[/TD]
[TD="align: right"]03-Jun-16[/TD]
[/TR]
[TR]
[TD]OPTIDX[/TD]
[TD]BANKNIFTY-II[/TD]
[TD="align: right"]28-Jul-16[/TD]
[TD="align: right"]16000[/TD]
[TD]PE[/TD]
[TD="align: right"]94[/TD]
[TD="align: right"]97[/TD]
[TD="align: right"]86.2[/TD]
[TD="align: right"]89.55[/TD]
[TD="align: right"]89.55[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]218.87[/TD]
[TD="align: right"]5520[/TD]
[TD="align: right"]160[/TD]
[TD="align: right"]03-Jun-16[/TD]
[/TR]
[TR]
[TD]OPTIDX[/TD]
[TD]BANKNIFTY-II[/TD]
[TD="align: right"]28-Jul-16[/TD]
[TD="align: right"]16500[/TD]
[TD]PE[/TD]
[TD="align: right"]141[/TD]
[TD="align: right"]141[/TD]
[TD="align: right"]140[/TD]
[TD="align: right"]140[/TD]
[TD="align: right"]140[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]19.96[/TD]
[TD="align: right"]320[/TD]
[TD="align: right"]-80[/TD]
[TD="align: right"]03-Jun-16[/TD]
[/TR]
[TR]
[TD]OPTIDX[/TD]
[TD]BANKNIFTY-II[/TD]
[TD="align: right"]28-Jul-16[/TD]
[TD="align: right"]17000[/TD]
[TD]PE[/TD]
[TD="align: right"]277.2[/TD]
[TD="align: right"]277.2[/TD]
[TD="align: right"]236.15[/TD]
[TD="align: right"]256[/TD]
[TD="align: right"]256[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]48.31[/TD]
[TD="align: right"]640[/TD]
[TD="align: right"]120[/TD]
[TD="align: right"]03-Jun-16[/TD]
[/TR]
[TR]
[TD]OPTIDX[/TD]
[TD]BANKNIFTY-II[/TD]
[TD="align: right"]28-Jul-16[/TD]
[TD="align: right"]17500[/TD]
[TD]PE[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]7.2[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]03-Jun-16[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question Question
How to make it Highlight in Any color while selecting the name
Replies
1
Views
61
DanteAmor
DanteAmor
sparky2205
  • Question Question
COUNTIF with DATES
Replies
3
Views
163
sparky2205
sparky2205
J
  • Question Question
auto filling cells based on preset format
Replies
1
Views
77
HighAndWilder
H
A
  • Question Question
Assign Month number 1 to n based on sequential week range
Replies
6
Views
528
amolvijay
A
H
  • Solved
Copy or Transpose multiple columns into a single column.
Replies
2
Views
126
harzer
H

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