Reformatting and organising data then extracting rows to a new tab based on criteria

S

SHARRIS2013

New Member
Joined
Jan 3, 2013
Messages
10
Hi guys!
I am struggling with a data extract that i need to reformat and hoping someone can assist:)

I have the below raw data in tab1, most of the fields in column h repeat themselves so i want to set these fields up as headings and then format the data as per example 2 in tab2. some fields, the first 12 rows denoted by column F 'flex udf' need to be applied to all of the other rows


REF</SPAN>TYPE</SPAN>E</SPAN>P</SPAN>CCY</SPAN>TYPE</SPAN>NAME</SPAN>VALUE1</SPAN>VALUE2</SPAN>
1</SPAN>EX</SPAN>TEST</SPAN>TEST_R</SPAN>EUR/GBP</SPAN>FLEX_UDF</SPAN>TK</SPAN>TARGET</SPAN>0.2</SPAN>
1</SPAN>EX</SPAN>TEST</SPAN>TEST_R</SPAN>EUR/GBP</SPAN>FLEX_UDF</SPAN>TK</SPAN>PAYTYP</SPAN>STRIP</SPAN>
1</SPAN>EX</SPAN>TEST</SPAN>TEST_R</SPAN>EUR/GBP</SPAN>FLEX_UDF</SPAN>TK</SPAN>EXPIRY</SPAN>20130205</SPAN>
1</SPAN>EX</SPAN>TEST</SPAN>TEST_R</SPAN>EUR/GBP</SPAN>FLEX_UDF</SPAN>TK</SPAN>EXPFLW</SPAN>YES</SPAN>
1</SPAN>EX</SPAN>TEST</SPAN>TEST_R</SPAN>EUR/GBP</SPAN>FLEX_UDF</SPAN>TK</SPAN>FIXFRO</SPAN>20121205</SPAN>
1</SPAN>EX</SPAN>TEST</SPAN>TEST_R</SPAN>EUR/GBP</SPAN>FLEX_UDF</SPAN>TK</SPAN>ACCPRO</SPAN>0.2</SPAN>
1</SPAN>EX</SPAN>TEST</SPAN>TEST_R</SPAN>EUR/GBP</SPAN>FLEX_UDF</SPAN>TK</SPAN>TAGTYP</SPAN>CAPPED</SPAN>
1</SPAN>EX</SPAN>TEST</SPAN>TEST_R</SPAN>EUR/GBP</SPAN>FLEX_UDF</SPAN>TK</SPAN>MUPROF</SPAN>NO</SPAN>
1</SPAN>EX</SPAN>TEST</SPAN>TEST_R</SPAN>EUR/GBP</SPAN>FLEX_UDF</SPAN>TK</SPAN>CP</SPAN>PUT_FIRST_CCY</SPAN>
1</SPAN>EX</SPAN>TEST</SPAN>TEST_R</SPAN>EUR/GBP</SPAN>FLEX_UDF</SPAN>TK</SPAN>DELIVE</SPAN>20130207</SPAN>
1</SPAN>EX</SPAN>TEST</SPAN>TEST_R</SPAN>EUR/GBP</SPAN>FLEX_UDF</SPAN>TK</SPAN>FIXSCH</SPAN>VOLA_MENSUAL</SPAN>
1</SPAN>EX</SPAN>TEST</SPAN>TEST_R</SPAN>EUR/GBP</SPAN>FLEX_UDF</SPAN>TK</SPAN>FIXTO</SPAN>20130205</SPAN>
1</SPAN>EX</SPAN>TEST</SPAN>TEST_R</SPAN>EUR/GBP</SPAN>FLEX_VECTOR</SPAN>TK</SPAN>FIXDAT</SPAN>20121205</SPAN>
1</SPAN>EX</SPAN>TEST</SPAN>TEST_R</SPAN>EUR/GBP</SPAN>FLEX_VECTOR</SPAN>TK</SPAN>QUODAT</SPAN>20121205</SPAN>
1</SPAN>EX</SPAN>TEST</SPAN>TEST_R</SPAN>EUR/GBP</SPAN>FLEX_VECTOR</SPAN>TK</SPAN>PAYDAT</SPAN>20121207</SPAN>
1</SPAN>EX</SPAN>TEST</SPAN>TEST_R</SPAN>EUR/GBP</SPAN>FLEX_VECTOR</SPAN>TK</SPAN>VALDAT</SPAN>20121204</SPAN>
1</SPAN>EX</SPAN>TEST</SPAN>TEST_R</SPAN>EUR/GBP</SPAN>FLEX_VECTOR</SPAN>TK</SPAN>STRIK1</SPAN>1.1</SPAN>
1</SPAN>EX</SPAN>TEST</SPAN>TEST_R</SPAN>EUR/GBP</SPAN>FLEX_VECTOR</SPAN>TK</SPAN>MPLY1</SPAN>1</SPAN>
1</SPAN>EX</SPAN>TEST</SPAN>TEST_R</SPAN>EUR/GBP</SPAN>FLEX_VECTOR</SPAN>TK</SPAN>STRIK2</SPAN>0</SPAN>
1</SPAN>EX</SPAN>TEST</SPAN>TEST_R</SPAN>EUR/GBP</SPAN>FLEX_VECTOR</SPAN>TK</SPAN>MPLY2</SPAN>0</SPAN>
1</SPAN>EX</SPAN>TEST</SPAN>TEST_R</SPAN>EUR/GBP</SPAN>FLEX_VECTOR</SPAN>TK</SPAN>STRIK3</SPAN>0</SPAN>
1</SPAN>EX</SPAN>TEST</SPAN>TEST_R</SPAN>EUR/GBP</SPAN>FLEX_VECTOR</SPAN>TK</SPAN>MPLY3</SPAN>0</SPAN>
1</SPAN>EX</SPAN>TEST</SPAN>TEST_R</SPAN>EUR/GBP</SPAN>FLEX_VECTOR</SPAN>TK</SPAN>STRIK4</SPAN>0</SPAN>
1</SPAN>EX</SPAN>TEST</SPAN>TEST_R</SPAN>EUR/GBP</SPAN>FLEX_VECTOR</SPAN>TK</SPAN>MPLY4</SPAN>0</SPAN>
1</SPAN>EX</SPAN>TEST</SPAN>TEST_R</SPAN>EUR/GBP</SPAN>FLEX_VECTOR</SPAN>TK</SPAN>LEV1</SPAN>1</SPAN>
1</SPAN>EX</SPAN>TEST</SPAN>TEST_R</SPAN>EUR/GBP</SPAN>FLEX_VECTOR</SPAN>TK</SPAN>PIVOT</SPAN>1.1</SPAN>
1</SPAN>EX</SPAN>TEST</SPAN>TEST_R</SPAN>EUR/GBP</SPAN>FLEX_VECTOR</SPAN>TK</SPAN>LEV2</SPAN>2</SPAN>
1</SPAN>EX</SPAN>TEST</SPAN>TEST_R</SPAN>EUR/GBP</SPAN>FLEX_VECTOR</SPAN>TK</SPAN>FIXDAT</SPAN>20130107</SPAN>
1</SPAN>EX</SPAN>TEST</SPAN>TEST_R</SPAN>EUR/GBP</SPAN>FLEX_VECTOR</SPAN>TK</SPAN>QUODAT</SPAN>20130107</SPAN>
1</SPAN>EX</SPAN>TEST</SPAN>TEST_R</SPAN>EUR/GBP</SPAN>FLEX_VECTOR</SPAN>TK</SPAN>PAYDAT</SPAN>20130109</SPAN>
1</SPAN>EX</SPAN>TEST</SPAN>TEST_R</SPAN>EUR/GBP</SPAN>FLEX_VECTOR</SPAN>TK</SPAN>VALDAT</SPAN>20130106</SPAN>
1</SPAN>EX</SPAN>TEST</SPAN>TEST_R</SPAN>EUR/GBP</SPAN>FLEX_VECTOR</SPAN>TK</SPAN>STRIK1</SPAN>1.1</SPAN>
1</SPAN>EX</SPAN>TEST</SPAN>TEST_R</SPAN>EUR/GBP</SPAN>FLEX_VECTOR</SPAN>TK</SPAN>MPLY1</SPAN>1</SPAN>
1</SPAN>EX</SPAN>TEST</SPAN>TEST_R</SPAN>EUR/GBP</SPAN>FLEX_VECTOR</SPAN>TK</SPAN>STRIK2</SPAN>0</SPAN>
1</SPAN>EX</SPAN>TEST</SPAN>TEST_R</SPAN>EUR/GBP</SPAN>FLEX_VECTOR</SPAN>TK</SPAN>MPLY2</SPAN>0</SPAN>
1</SPAN>EX</SPAN>TEST</SPAN>TEST_R</SPAN>EUR/GBP</SPAN>FLEX_VECTOR</SPAN>TK</SPAN>STRIK3</SPAN>0</SPAN>
1</SPAN>EX</SPAN>TEST</SPAN>TEST_R</SPAN>EUR/GBP</SPAN>FLEX_VECTOR</SPAN>TK</SPAN>MPLY3</SPAN>0</SPAN>
1</SPAN>EX</SPAN>TEST</SPAN>TEST_R</SPAN>EUR/GBP</SPAN>FLEX_VECTOR</SPAN>TK</SPAN>STRIK4</SPAN>0</SPAN>
1</SPAN>EX</SPAN>TEST</SPAN>TEST_R</SPAN>EUR/GBP</SPAN>FLEX_VECTOR</SPAN>TK</SPAN>MPLY4</SPAN>0</SPAN>
1</SPAN>EX</SPAN>TEST</SPAN>TEST_R</SPAN>EUR/GBP</SPAN>FLEX_VECTOR</SPAN>TK</SPAN>LEV1</SPAN>1</SPAN>
1</SPAN>EX</SPAN>TEST</SPAN>TEST_R</SPAN>EUR/GBP</SPAN>FLEX_VECTOR</SPAN>TK</SPAN>PIVOT</SPAN>1.1</SPAN>
1</SPAN>EX</SPAN>TEST</SPAN>TEST_R</SPAN>EUR/GBP</SPAN>FLEX_VECTOR</SPAN>TK</SPAN>LEV2</SPAN>2</SPAN>
1</SPAN>EX</SPAN>TEST</SPAN>TEST_R</SPAN>EUR/GBP</SPAN>FLEX_VECTOR</SPAN>TK</SPAN>FIXDAT</SPAN>20130205</SPAN>
1</SPAN>EX</SPAN>TEST</SPAN>TEST_R</SPAN>EUR/GBP</SPAN>FLEX_VECTOR</SPAN>TK</SPAN>QUODAT</SPAN>20130205</SPAN>
1</SPAN>EX</SPAN>TEST</SPAN>TEST_R</SPAN>EUR/GBP</SPAN>FLEX_VECTOR</SPAN>TK</SPAN>PAYDAT</SPAN>20130207</SPAN>
1</SPAN>EX</SPAN>TEST</SPAN>TEST_R</SPAN>EUR/GBP</SPAN>FLEX_VECTOR</SPAN>TK</SPAN>VALDAT</SPAN>20130204</SPAN>
1</SPAN>EX</SPAN>TEST</SPAN>TEST_R</SPAN>EUR/GBP</SPAN>FLEX_VECTOR</SPAN>TK</SPAN>STRIK1</SPAN>1.1</SPAN>
1</SPAN>EX</SPAN>TEST</SPAN>TEST_R</SPAN>EUR/GBP</SPAN>FLEX_VECTOR</SPAN>TK</SPAN>MPLY1</SPAN>1</SPAN>
1</SPAN>EX</SPAN>TEST</SPAN>TEST_R</SPAN>EUR/GBP</SPAN>FLEX_VECTOR</SPAN>TK</SPAN>STRIK2</SPAN>0</SPAN>
1</SPAN>EX</SPAN>TEST</SPAN>TEST_R</SPAN>EUR/GBP</SPAN>FLEX_VECTOR</SPAN>TK</SPAN>MPLY2</SPAN>0</SPAN>
1</SPAN>EX</SPAN>TEST</SPAN>TEST_R</SPAN>EUR/GBP</SPAN>FLEX_VECTOR</SPAN>TK</SPAN>STRIK3</SPAN>0</SPAN>
1</SPAN>EX</SPAN>TEST</SPAN>TEST_R</SPAN>EUR/GBP</SPAN>FLEX_VECTOR</SPAN>TK</SPAN>MPLY3</SPAN>0</SPAN>
1</SPAN>EX</SPAN>TEST</SPAN>TEST_R</SPAN>EUR/GBP</SPAN>FLEX_VECTOR</SPAN>TK</SPAN>STRIK4</SPAN>0</SPAN>
1</SPAN>EX</SPAN>TEST</SPAN>TEST_R</SPAN>EUR/GBP</SPAN>FLEX_VECTOR</SPAN>TK</SPAN>MPLY4</SPAN>0</SPAN>
1</SPAN>EX</SPAN>TEST</SPAN>TEST_R</SPAN>EUR/GBP</SPAN>FLEX_VECTOR</SPAN>TK</SPAN>LEV1</SPAN>1</SPAN>
1</SPAN>EX</SPAN>TEST</SPAN>TEST_R</SPAN>EUR/GBP</SPAN>FLEX_VECTOR</SPAN>TK</SPAN>PIVOT</SPAN>1.1</SPAN>
1</SPAN>EX</SPAN>TEST</SPAN>TEST_R</SPAN>EUR/GBP</SPAN>FLEX_VECTOR</SPAN>TK</SPAN>LEV2</SPAN>2
</SPAN>

<TBODY>
</TBODY>




Example 2 - how i would like it to look (previously i recorded a macro copying and pasting and using filters which doesnt seem efficient)

REF</SPAN>P</SPAN>CCY</SPAN>TARGET</SPAN>PAYTYP</SPAN>EXPIRY</SPAN>EXPFLW</SPAN>FIXFRO</SPAN>ACCPRO</SPAN>TAGTYP</SPAN>MUPROF</SPAN>CP</SPAN> DELIVE</SPAN> FIXSCH</SPAN>FIXTO</SPAN>FIXDAT</SPAN>QUODAT</SPAN>PAYDAT</SPAN>VALDAT</SPAN>STRIK1</SPAN>MPLY1</SPAN>STRIK2</SPAN>MPLY2</SPAN>STRIK3</SPAN>MPLY3</SPAN>STRIK4</SPAN>MPLY4</SPAN>LEV1</SPAN>PIVOT</SPAN>LEV2</SPAN>
1</SPAN>TEST_R</SPAN>EUR/GBP</SPAN>0.2</SPAN>STRIP</SPAN>20130205</SPAN>YES</SPAN>20121205 </SPAN> 0.2</SPAN>CAPPED</SPAN>NO</SPAN>PUT_FIRST_CCY</SPAN> 20130207</SPAN> VOLA</SPAN>20130205</SPAN> 20121205</SPAN>20121205</SPAN>20121207</SPAN>20121204</SPAN>1.1</SPAN>1</SPAN>1.1</SPAN>0</SPAN>0</SPAN>0</SPAN>0</SPAN>0</SPAN>1</SPAN>1.1</SPAN>2</SPAN>
1</SPAN>TEST_R</SPAN>EUR/GBP</SPAN>0.2</SPAN>STRIP</SPAN>20130205</SPAN>YES</SPAN>20121205</SPAN> 0.2</SPAN>CAPPED</SPAN>NO</SPAN>PUT_FIRST_CCY</SPAN> 20130207</SPAN> VOLA </SPAN>20130205</SPAN> 20130107</SPAN>20130107</SPAN>20130109</SPAN>20130106</SPAN>1.1</SPAN>1</SPAN>1.1</SPAN>0</SPAN>0</SPAN>0</SPAN>0</SPAN>0</SPAN>1</SPAN>1.1</SPAN>2</SPAN>
1</SPAN>TEST_R</SPAN>EUR/GBP</SPAN>0.2</SPAN>STRIP</SPAN>20130205</SPAN>YES</SPAN>20121205</SPAN> 0.2</SPAN>CAPPED</SPAN>NO</SPAN>PUT_FIRST_CCY</SPAN> 20130207</SPAN> VOLA </SPAN>20130205</SPAN> 20130205 </SPAN>20130205</SPAN>20130207</SPAN>20130204</SPAN>1.1</SPAN>1</SPAN>1.1</SPAN>0</SPAN>0</SPAN>0</SPAN>0</SPAN>0</SPAN>1</SPAN>1.1</SPAN>2</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL span=5><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP>


sorry this isnt laid out very well but i wasn't sure how to attach a s/s to provide the data
once i have the data in the above format i would like to copy any rows into tab3 where
the 'fixdat' column = todays date

if anyone could help that would be amazing or pointing me in the right direction as to what code or formula would be suitable would also be great!

Thanks!
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
You must log in or register to reply here.

Similar threads

L
  • Question
VBA - Macro to Output Data into Tab Based on Criteria
Replies
0
Views
451
lpw0806
L
A
  • Question
VBA macro to extract data from one row to two rows
Replies
4
Views
2K
Andy booth
A
C
  • Question
VBA - Copy sheet1 data to new Workbook & save out
Replies
0
Views
532
Curious_
C

Forum statistics

Threads
1,196,323
Messages
6,014,637
Members
441,833
Latest member
Rangerreeve

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