Macro To Concatenate

[zaska]

Hi all,

I have the following data...

INSTRUMENT SYMBOL EXPIRY DATE

FUTIDX BBBB MAY
FUTIDX BBBB JUN
FUTIDX BBBB JULY
FUTIDX CCCC MAY
FUTIDX CCCC JUN
FUTIDX DDDD JULY
OPSTK BBBB MAY
OPSTK BBBB JUN


I want to convert this data as follows

BBBB-I
BBBB-II
BBBB-III
CCCC-I
CCCC-II
DDDD-III

and DELETE all the symbols starting with the Instrument name " OPSTK".

Thank you

 

[cjcobra]

I would do a Formula next to it. Can you make a small table with May=I, June=II, etc.

Then do a

=MID(A1,8,4) & "-" & vlookup(TRIM(MID(A1,13,10),your table,2,0)

Bascially get the BBBB or CCCC, etc out of the cell, put a "-", then lookup the I/II/III.

I would also put a if(left(a1,5)="OPSTK","") in front of the above formula to blank it out if it's OPSTK

 

[zaska]

Thank you..

Last thing that i would like to delete all the rows from where the OPSTK Instrument starts.

The data is arranged in a sequence that FUTSK symbols are arranged first and then OPSTK symbols later...i don't want OPSTK Symbols data...i want to complete delte the range of OPSTK symbols..

IS it possible?

Thank you once again

 

[Jon von der Heyden]

For VBA:

Public Sub Phoo()
    Dim vararr As Variant
    Dim lngItem As Long, lngRN As Long
    
    With Range("A2:A9")
        vararr = Filter(Evaluate("transpose(if(row()," & .Address & "&""-""&" & .Offset(, 1).Address & "))"), "OPSTK", False)
    End With
    
    lngRN = 1
    For lngItem = LBound(vararr) To UBound(vararr)
        If lngItem > LBound(vararr) Then
            If Split(vararr(lngItem), "-")(1) = Split(vararr(lngItem - 1), "-")(0) Then
                lngRN = lngRN + 1
            Else
                lngRN = 1
            End If
        End If
        vararr(lngItem) = Split(vararr(lngItem), "-")(1) & "-" & Application.Roman(lngRN)
    Next lngItem
    
    Range("E2").Resize(UBound(vararr) + 1).Value = Application.Transpose(vararr)
End Sub

Currently does not qualify the sheet, so sheet must be active when you run this code.

Change ranges to suit. Currently assumes your table in A2:A9, and assumes results to be written out to column E.

 

[DC8421]

I'll take a stab at it....<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Assuming FUTIDX starts at A2<o></o>
<o></o>
IF((A2="OPSTK"),"",concatenate(B2&”-“&(Vlookup(C2,Your Table,2,false))))
<o></o>
My excel is currently locked up so I couldn’t test this out.
<o></o>
Hopefully it works or at least helps you find the solution.<o></o>

 

[Jon von der Heyden]

Formula approach:

Excel Workbook
	A	B	C	D	E	F	G
1	INSTRUMENT	SYMBOL	EXPIRY		CODE	ROMAN	RESULT
2	FUTIDX	BBBB	MAY		BBBB	I	BBBB-I
3	FUTIDX	BBBB	JUN		BBBB	II	BBBB-II
4	FUTIDX	BBBB	JULY		BBBB	III	BBBB-III
5	FUTIDX	CCCC	MAY		CCCC	I	CCCC-I
6	FUTIDX	CCCC	JUN		CCCC	II	CCCC-II
7	FUTIDX	DDDD	JULY		DDDD	I	DDDD-I
8	OPSTK	BBBB	MAY		#NUM!	I	#NUM!
9	OPSTK	BBBB	JUN		#NUM!	II	#NUM!
Sheet1

I am showing a stepped approach, using two helper columns, but you could wrap it up into a single result column if you wish.

 

[Jon von der Heyden]

I just twigged that the roman numerals are month values, so:

Excel Workbook
	A	B	C	D	E	F	G
1	INSTRUMENT	SYMBOL	EXPIRY		CODE	ROMAN	RESULT
2	FUTIDX	BBBB	MAY		BBBB	I	BBBB-I
3	FUTIDX	BBBB	JUN		BBBB	II	BBBB-II
4	FUTIDX	BBBB	JULY		BBBB	III	BBBB-III
5	FUTIDX	CCCC	MAY		CCCC	I	CCCC-I
6	FUTIDX	CCCC	JUN		CCCC	II	CCCC-II
7	FUTIDX	DDDD	JULY		DDDD	III	DDDD-III
8	OPSTK	BBBB	MAY		#NUM!	I	#NUM!
9	OPSTK	BBBB	JUN		#NUM!	II	#NUM!
Sheet1

Excel 2010

Cell Formulas
Range	Formula
F2	=ROMAN(MONTH("1-"&C2)-4)
G2	=E2&"-"&F2

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

 

[zaska]

Thank you for the Help.

I have one query ...I tried few times to post excel sheets but i was not successful ..

I tried with Xl jeanie...but i can't figure it out where i went wrong.