Concatenating Using VBA based on Expiry Date

Z

zaska

Well-known Member
Joined
Oct 24, 2010
Messages
1,046
Hi...

<table border="0" cellpadding="0" cellspacing="0" width="302"><col style="mso-width-source:userset;mso-width-alt:3803;width:78pt" width="104"> <col style="mso-width-source:userset;mso-width-alt:4059;width:83pt" width="111"> <col style="mso-width-source:userset;mso-width-alt:3181;width:65pt" width="87"> <tbody><tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;width:78pt" height="20" width="104"> <table border="0" cellpadding="0" cellspacing="0" width="302"><col style="mso-width-source:userset;mso-width-alt:3803;width:78pt" width="104"> <col style="mso-width-source:userset;mso-width-alt:4059;width:83pt" width="111"> <col style="mso-width-source:userset;mso-width-alt:3181;width:65pt" width="87"> <tbody><tr style="height:15.0pt" height="20"> <td style="height:15.0pt;width:78pt" height="20" width="104">FUTSTK</td> <td style="width:83pt" width="111">VOLTAS</td> <td class="xl65" style="width:65pt" align="right" width="87">28-Jul-11</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">FUTSTK</td> <td>VOLTAS</td> <td class="xl65" align="right">29-Sep-11</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">FUTSTK</td> <td>WELCORP</td> <td class="xl65" align="right">28-Jul-11</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">FUTSTK</td> <td>WELCORP</td> <td class="xl65" align="right">25-Aug-11</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">FUTSTK</td> <td>WELCORP</td> <td class="xl65" align="right">29-Sep-11</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">FUTSTK</td> <td>WIPRO</td> <td class="xl65" align="right">28-Jul-11</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">FUTSTK</td> <td>WIPRO</td> <td class="xl65" align="right">29-Sep-11</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">FUTSTK</td> <td>YESBANK</td> <td class="xl65" align="right">28-Jul-11</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">FUTSTK</td> <td>YESBANK</td> <td class="xl65" align="right">25-Aug-11</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">FUTSTK</td> <td>YESBANK</td> <td class="xl65" align="right">29-Sep-11</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">FUTSTK</td> <td>ZEEL</td> <td class="xl65" align="right">28-Jul-11</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">FUTSTK</td> <td>ZEEL</td> <td class="xl65" align="right">25-Aug-11</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">FUTSTK</td> <td>ZEEL</td> <td class="xl65" align="right">29-Sep-11</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">OPTIDX</td> <td>BANKNIFTY</td> <td class="xl65" align="right">28-Jul-11</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">OPTIDX</td> <td>BANKNIFTY</td> <td class="xl65" align="right">28-Jul-11</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">OPTIDX</td> <td>BANKNIFTY</td> <td class="xl65" align="right">28-Jul-11</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">OPTIDX</td> <td>BANKNIFTY</td> <td class="xl65" align="right">28-Jul-11</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">OPTIDX</td> <td>BANKNIFTY</td> <td class="xl65" align="right">28-Jul-11</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">OPTIDX</td> <td>BANKNIFTY</td> <td class="xl65" align="right">28-Jul-11</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">OPTIDX</td> <td>BANKNIFTY</td> <td class="xl65" align="right">28-Jul-11</td> </tr> </tbody></table></td> <td class="xl63" style="border-left:none;width:83pt" width="111">
</td> <td class="xl63" style="border-left:none;width:65pt" width="87">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;border-top:none" height="20">
</td> <td class="xl63" style="border-top:none;border-left:none">
</td> <td class="xl64" style="border-top:none;border-left:none" align="right">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;border-top:none" height="20">
</td> <td class="xl63" style="border-top:none;border-left:none">
</td> <td class="xl64" style="border-top:none;border-left:none" align="right">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;border-top:none" height="20">I want to accomplish the following from the above table.

1. Delete all the Data for the "OPTIDX" Symbols. I want data only for the rows having "FUTSTK"

2. Append "-I" for 1st expiry date i.e 28-Jul-2011 and "-II" for 2nd Expiry Date i.e 29-Sep-2011 and "-III" for 3rd Expiry date.

Desired Output

VOLTAS-I
VOLTAS-II
WELCORP-I
WELCORP-II
WELCORP-III
WIPRO-I
WIPRO-II
YESBANK-I
YESBANK-II
YESBANK-III

Thank you


</td> <td class="xl63" style="border-top:none;border-left:none">
</td> <td class="xl64" style="border-top:none;border-left:none" align="right">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;border-top:none" height="20">
</td> <td class="xl63" style="border-top:none;border-left:none">
</td> <td class="xl64" style="border-top:none;border-left:none" align="right">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;border-top:none" height="20">
</td> <td class="xl63" style="border-top:none;border-left:none">
</td> <td class="xl64" style="border-top:none;border-left:none" align="right">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;border-top:none" height="20">
</td> <td class="xl63" style="border-top:none;border-left:none">
</td> <td class="xl64" style="border-top:none;border-left:none" align="right">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;border-top:none" height="20">
</td> <td class="xl63" style="border-top:none;border-left:none">
</td> <td class="xl64" style="border-top:none;border-left:none" align="right">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;border-top:none" height="20">
</td> <td class="xl63" style="border-top:none;border-left:none">
</td> <td class="xl64" style="border-top:none;border-left:none" align="right">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;border-top:none" height="20">
</td> <td class="xl63" style="border-top:none;border-left:none">
</td> <td class="xl64" style="border-top:none;border-left:none" align="right">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;border-top:none" height="20">
</td> <td class="xl63" style="border-top:none;border-left:none">
</td> <td class="xl64" style="border-top:none;border-left:none" align="right">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;border-top:none" height="20">
</td> <td class="xl63" style="border-top:none;border-left:none">
</td> <td class="xl64" style="border-top:none;border-left:none" align="right">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;border-top:none" height="20">
</td> <td class="xl63" style="border-top:none;border-left:none">
</td> <td class="xl64" style="border-top:none;border-left:none" align="right">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;border-top:none" height="20">
</td> <td class="xl63" style="border-top:none;border-left:none">
</td> <td class="xl64" style="border-top:none;border-left:none" align="right">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;border-top:none" height="20">
</td> <td class="xl63" style="border-top:none;border-left:none">
</td> <td class="xl64" style="border-top:none;border-left:none" align="right">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;border-top:none" height="20">
</td> <td class="xl63" style="border-top:none;border-left:none">
</td> <td class="xl64" style="border-top:none;border-left:none" align="right">
</td> </tr> </tbody></table>
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Sort by date Sort by votes
If VOLTAS is in column B, this formula would be helpful. Put it in row 1 and drag downward.

=CHOOSE(1+COUNTIF($B$1:$B1, $B1),"", "I", "II", "III", "IV", "V", "VI", "VII", "VIII", "IX", "X")

AutoFilter can be used to exclude OPTDIX .
 
Upvote 0
Try this with a copy of your sheet

Code: 
Sub test()
Dim LR As Long, i As Long
Application.ScreenUpdating = False
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = LR To 1 Step -1
    If Range("A" & i).Value <> "FUTSTK" Then Rows(i).Delete
Next i
LR = Range("A" & Rows.Count).End(xlUp).Row
Columns("C").Insert
Range("C1").Value = "-I"
For i = 2 To LR
    If Range("B" & i).Value = Range("B" & i - 1).Value Then
        Range("C" & i).Value = Range("C" & i - 1).Value & "I"
    Else
        Range("C" & i).Value = "-I"
    End If
Next i
For i = 1 To LR
    Range("B" & i).Value = Range("B" & i).Value & Range("C" & i).Value
Next i
Columns("C").Delete
Application.ScreenUpdating = True
End Sub
 
Upvote 0
Sir,

Thank you it worked for me.

I used the filter but i don't know how to use it in VBA code. I want to delete all the rows which don't contain FUTSK data.

Can i use array formulas for the same ? ( If possible).

Now i have to create two new columns one for using your formula and the other for concatenating.

Is there any other way to do it in the single column?

Thanks
 
Upvote 0
@ Vog

Sir,

I am sorry , i haven't seen ur post..Ur Code worked like a charm. I have one more request.

<table border="0" cellpadding="0" cellspacing="0" width="1073"><col style="mso-width-source:userset;mso-width-alt:2742;width:56pt" width="75"> <col style="mso-width-source:userset;mso-width-alt:3766;width:77pt" width="103"> <col style="mso-width-source:userset;mso-width-alt:3108;width:64pt" width="85"> <col style="width:48pt" span="9" width="64"> <col style="mso-width-source:userset;mso-width-alt:3072;width:63pt" width="84"> <col style="mso-width-source:userset;mso-width-alt:3145;width:65pt" width="86"> <col style="width:48pt" width="64"> <tbody><tr style="height:15.0pt" height="20"> <td style="height:15.0pt;width:56pt" height="20" width="75">INSTRUMENT</td> <td style="width:77pt" width="103">SYMBOL</td> <td style="width:64pt" width="85">EXPIRY_DT</td> <td style="width:48pt" width="64">STRIKE_PR</td> <td style="width:48pt" width="64">OPTION_TYP</td> <td style="width:48pt" width="64">OPEN</td> <td style="width:48pt" width="64">HIGH</td> <td style="width:48pt" width="64">LOW</td> <td style="width:48pt" width="64">CLOSE</td> <td style="width:48pt" width="64">SETTLE_PR</td> <td style="width:48pt" width="64">CONTRACTS</td> <td style="width:48pt" width="64">VAL_INLAKH</td> <td style="width:63pt" width="84">OPEN_INT</td> <td style="width:65pt" width="86">CHG_IN_OI</td> <td style="width:48pt" width="64">TIMESTAMP</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">FUTIDX</td> <td>BANKNIFTY</td> <td class="xl63" align="right">28-Jul-11</td> <td align="right">0</td> <td>XX</td> <td align="right">11299.9</td> <td align="right">11346.95</td> <td align="right">11212.25</td> <td align="right">11295.5</td> <td align="right">11295.5</td> <td align="right">39508</td> <td align="right">111327.1</td> <td align="right">969625</td> <td align="right">44900</td> <td class="xl63" align="right">01-Jul-11</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">FUTIDX</td> <td>BANKNIFTY</td> <td class="xl63" align="right">25-Aug-11</td> <td align="right">0</td> <td>XX</td> <td align="right">11340.05</td> <td align="right">11354.75</td> <td align="right">11240</td> <td align="right">11322.35</td> <td align="right">11322.35</td> <td align="right">640</td> <td align="right">1807.97</td> <td align="right">22625</td> <td align="right">5575</td> <td class="xl63" align="right">01-Jul-11</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">FUTIDX</td> <td>BANKNIFTY</td> <td class="xl63" align="right">29-Sep-11</td> <td align="right">0</td> <td>XX</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> <td align="right">11512.15</td> <td align="right">11519.8</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> <td class="xl63" align="right">01-Jul-11</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">FUTIDX</td> <td>CNXIT</td> <td class="xl63" align="right">28-Jul-11</td> <td align="right">0</td> <td>XX</td> <td align="right">6668.5</td> <td align="right">6729.95</td> <td align="right">6660</td> <td align="right">6692.8</td> <td align="right">6692.8</td> <td align="right">81</td> <td align="right">270.98</td> <td align="right">15650</td> <td align="right">400</td> <td class="xl63" align="right">01-Jul-11</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">FUTIDX</td> <td>CNXIT</td> <td class="xl63" align="right">25-Aug-11</td> <td align="right">0</td> <td>XX</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> <td align="right">6211</td> <td align="right">6770.3</td> <td align="right">0</td> <td align="right">0</td> <td align="right">50</td> <td align="right">0</td> <td class="xl63" align="right">01-Jul-11</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">FUTIDX</td> <td>CNXIT</td> <td class="xl63" align="right">29-Sep-11</td> <td align="right">0</td> <td>XX</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> <td align="right">6782.3</td> <td align="right">6831.8</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> <td class="xl63" align="right">01-Jul-11</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">FUTIDX</td> <td>MINIFTY</td> <td class="xl63" align="right">28-Jul-11</td> <td align="right">0</td> <td>XX</td> <td align="right">5685</td> <td align="right">5699</td> <td align="right">5616.1</td> <td align="right">5638</td> <td align="right">5638</td> <td align="right">31575</td> <td align="right">35668</td> <td align="right">677440</td> <td align="right">-3180</td> <td class="xl63" align="right">01-Jul-11</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">FUTIDX</td> <td>MINIFTY</td> <td class="xl63" align="right">25-Aug-11</td> <td align="right">0</td> <td>XX</td> <td align="right">5697.4</td> <td align="right">5710</td> <td align="right">5633</td> <td align="right">5654.7</td> <td align="right">5654.7</td> <td align="right">2389</td> <td align="right">2707.2</td> <td align="right">98760</td> <td align="right">1280</td> <td class="xl63" align="right">01-Jul-11</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">FUTIDX</td> <td>MINIFTY</td> <td class="xl63" align="right">29-Sep-11</td> <td align="right">0</td> <td>XX</td> <td align="right">5710.9</td> <td align="right">5719</td> <td align="right">5651</td> <td align="right">5672.75</td> <td align="right">5672.75</td> <td align="right">335</td> <td align="right">381.05</td> <td align="right">4080</td> <td align="right">4080</td> <td class="xl63" align="right">01-Jul-11</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">FUTIDX</td> <td>NFTYMCAP50</td> <td class="xl63" align="right">28-Jul-11</td> <td align="right">0</td> <td>XX</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> <td align="right">2630.6</td> <td align="right">2443.5</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> <td class="xl63" align="right">01-Jul-11</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">FUTIDX</td> <td>NFTYMCAP50</td> <td class="xl63" align="right">25-Aug-11</td> <td align="right">0</td> <td>XX</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> <td align="right">2460.4</td> <td align="right">2461.9</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> <td class="xl63" align="right">01-Jul-11</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">FUTIDX</td> <td>NFTYMCAP50</td> <td class="xl63" align="right">29-Sep-11</td> <td align="right">0</td> <td>XX</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> <td align="right">2476.6</td> <td align="right">2484.25</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> <td class="xl63" align="right">01-Jul-11</td> </tr> </tbody></table>
I want to add '' FUTIDX '' data along with "FUTSTK" and delete the unnecessary columns.

Desired out put

<table border="0" cellpadding="0" cellspacing="0" width="647"><col style="mso-width-source:userset;mso-width-alt:3766;width:77pt" width="103"> <col style="mso-width-source:userset;mso-width-alt:3291;width:68pt" width="90"> <col style="mso-width-source:userset;mso-width-alt:2596;width:53pt" width="71"> <col style="width:48pt" span="3" width="64"> <col style="mso-width-source:userset;mso-width-alt:3913;width:80pt" width="107"> <col style="mso-width-source:userset;mso-width-alt:3072;width:63pt" width="84"> <tbody><tr style="height:15.0pt" height="20"> <td style="height:15.0pt;width:77pt" height="20" width="103">SYMBOL</td> <td class="xl65" style="width:68pt" width="90">TIMESTAMP</td> <td style="width:53pt" width="71">OPEN</td> <td style="width:48pt" width="64">HIGH</td> <td style="width:48pt" width="64">LOW</td> <td style="width:48pt" width="64">CLOSE</td> <td style="width:80pt" width="107">CONTRACTS</td> <td style="width:63pt" width="84">OPEN_INT</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">BANKNIFTY-I</td> <td class="xl65" align="right">20110701</td> <td align="right">11299.9</td> <td align="right">11346.95</td> <td align="right">11212.25</td> <td align="right">11295.5</td> <td align="right">39508</td> <td align="right">969625</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">BANKNIFTY-II</td> <td class="xl65" align="right">20110701</td> <td align="right">11340.05</td> <td align="right">11354.75</td> <td align="right">11240</td> <td align="right">11322.35</td> <td align="right">640</td> <td align="right">22625</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">BANKNIFTY-III</td> <td class="xl65" align="right">20110701</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> <td align="right">11512.15</td> <td align="right">0</td> <td align="right">0</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">CNXIT-I</td> <td class="xl65" align="right">20110701</td> <td align="right">6668.5</td> <td align="right">6729.95</td> <td align="right">6660</td> <td align="right">6692.8</td> <td align="right">81</td> <td align="right">15650</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">CNXIT-II</td> <td class="xl65" align="right">20110701</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> <td align="right">6211</td> <td align="right">0</td> <td align="right">50</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">CNXIT-III</td> <td class="xl65" align="right">20110701</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> <td align="right">6782.3</td> <td align="right">0</td> <td align="right">0</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">MINIFTY-I</td> <td class="xl65" align="right">20110701</td> <td align="right">5685</td> <td align="right">5699</td> <td align="right">5616.1</td> <td align="right">5638</td> <td align="right">31575</td> <td align="right">677440</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">MINIFTY-III</td> <td class="xl65" align="right">20110701</td> <td align="right">5697.4</td> <td align="right">5710</td> <td align="right">5633</td> <td align="right">5654.7</td> <td align="right">2389</td> <td align="right">98760</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">MINIFTY-III</td> <td class="xl65" align="right">20110701</td> <td align="right">5710.9</td> <td align="right">5719</td> <td align="right">5651</td> <td align="right">5672.75</td> <td align="right">335</td> <td align="right">4080</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">NFTYMCAP50-I</td> <td class="xl65" align="right">20110701</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> <td align="right">2630.6</td> <td align="right">0</td> <td align="right">0</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">NFTYMCAP50-II</td> <td class="xl65" align="right">20110701</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> <td align="right">2460.4</td> <td align="right">0</td> <td align="right">0</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">NFTYMCAP50-III</td> <td class="xl65" align="right">20110701</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> <td align="right">2476.6</td> <td align="right">0</td> <td align="right">0</td> </tr> </tbody></table>

Thank you very much
 
Upvote 0
Maybe this (try with a copy of your sheet)

Code: 
Sub test()
Dim LR As Long, i As Long
Application.ScreenUpdating = False
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = LR To 2 Step -1
    If Range("A" & i).Value <> "FUTSTK" And Range("A" & i).Value <> "FUTIDX" Then Rows(i).Delete
Next i
LR = Range("A" & Rows.Count).End(xlUp).Row
Columns("C").Insert
Range("C2").Value = "-I"
For i = 3 To LR
    If Range("B" & i).Value = Range("B" & i - 1).Value Then
        Range("C" & i).Value = Range("C" & i - 1).Value & "I"
    Else
        Range("C" & i).Value = "-I"
    End If
Next i
For i = 2 To LR
    Range("B" & i).Value = Range("B" & i).Value & Range("C" & i).Value
Next i
Columns("C").Delete
Columns("N:O").Delete
Columns("L").Delete
Columns("I:J").Delete
Columns("C:E").Delete
Columns("A").Delete
Application.ScreenUpdating = True
End Sub
 
Upvote 0
Sir,

Time Stamp Column is missing i.e "O" column.

I have few thoughts.

1. Each sheet is filled with nearly 40,000 cells so this code is taking time to get the output. I would like to ask you whether it is possible to use filter in the above vba code to delete the other data excluding " FUTIDX '' & " FUTSTK"

kindly suggest any other way to speed up the process.

Thank you
 
Upvote 0
Sir,

I tried this and it was much faster . Kindly suggest if there are any mistakes in the below code.

Code: 
Sub test2()

Dim LR As Long, i As Long
Application.ScreenUpdating = False
LastRow = Cells(Rows.Count, "B").End(xlUp).Row
 With Range("A1:O" & LastRow)
     .AutoFilter Field:=1, Criteria1:="<>FUTIDX", Operator:=xlAnd, Criteria2:="<>FUTSTK"
     .Offset(1, 0).EntireRow.Delete
 End With
 ActiveSheet.AutoFilterMode = False
 LR = Range("A" & Rows.Count).End(xlUp).Row
Columns("C").Insert
Range("C2").Value = "-I"
For i = 3 To LR
    If Range("B" & i).Value = Range("B" & i - 1).Value Then
        Range("C" & i).Value = Range("C" & i - 1).Value & "I"
    Else
        Range("C" & i).Value = "-I"
    End If
Next i
For i = 2 To LR
    Range("B" & i).Value = Range("B" & i).Value & Range("C" & i).Value
Next i
Columns("C").Delete
Columns("N:O").Delete
Columns("L").Delete
Columns("I:J").Delete
Columns("C:E").Delete
Columns("A").Delete
Application.ScreenUpdating = True
End Sub

Desired output

<table border="0" cellpadding="0" cellspacing="0" width="552"><col style="mso-width-source:userset;mso-width-alt:4242; width:87pt" span="2" width="116"> <col style="width:48pt" span="5" width="64"> <tbody><tr style="height:15.0pt" height="20"> <td style="height:15.0pt;width:87pt" height="20" width="116">SYMBOL</td> <td style="width:87pt" width="116">TIMESTAMP</td> <td style="width:48pt" width="64">OPEN</td> <td style="width:48pt" width="64">HIGH</td> <td style="width:48pt" width="64">LOW</td> <td style="width:48pt" width="64">CONTRACTS</td> <td style="width:48pt" width="64">OPEN_INT</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">BANKNIFTY-I</td> <td align="right">20010107</td> <td align="right">11299.9</td> <td align="right">11346.95</td> <td align="right">11212.25</td> <td align="right">39508</td> <td align="right">969625</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">BANKNIFTY-II</td> <td align="right">20010107</td> <td align="right">11340.05</td> <td align="right">11354.75</td> <td align="right">11240</td> <td align="right">640</td> <td align="right">22625</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">BANKNIFTY-III</td> <td align="right">20010107</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">CNXIT-I</td> <td align="right">20010107</td> <td align="right">6668.5</td> <td align="right">6729.95</td> <td align="right">6660</td> <td align="right">81</td> <td align="right">15650</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">CNXIT-II</td> <td align="right">20010107</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> <td align="right">50</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">CNXIT-III</td> <td align="right">20010107</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">MINIFTY-I</td> <td align="right">20010107</td> <td align="right">5685</td> <td align="right">5699</td> <td align="right">5616.1</td> <td align="right">31575</td> <td align="right">677440</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">MINIFTY-II</td> <td align="right">20010107</td> <td align="right">5697.4</td> <td align="right">5710</td> <td align="right">5633</td> <td align="right">2389</td> <td align="right">98760</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">MINIFTY-III</td> <td align="right">20010107</td> <td align="right">5710.9</td> <td align="right">5719</td> <td align="right">5651</td> <td align="right">335</td> <td align="right">4080</td> </tr> </tbody></table>

Help me with the TimeStamp Column. How can i directly paste it beside the symbol name and change the date format to " YYYYMMDD"



Thank you
 
Upvote 0
Sir,

Kindly have a look at Post #5 Time stamp Value is nothing but date and it is alreay present in Column " P "

I tried this from the Original data

Columns("B:B").Insert Shift:=xlToRight
Columns("P:P").Cut Columns("B:B")

I would like to know whether we can avoid Inserting a new column for pasting the time stamp values in column " B "


Regards,

Zaska
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
622
smozgur
smozgur
E
  • Question Question
VBA code clean up
Replies
9
Views
582
mumps
mumps
A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
2K
Attrazion
A
E
  • Question Question
VBA for Web Scraping help
Replies
0
Views
921
epactheactor
E
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
2K
CrashOD
C

Forum statistics

Threads
1,224,584
Messages
6,179,691
Members
452,938
Latest member
babeneker

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