You are here:

Excel/Formula to drag data

Advertisement


Question
Hello Tom,

In my job profile i need to QC the data coded by other employees in our system.
When i extract the data from that system to excel i got the data in below format. (for testing purpose i am giving only few rows of data)

" CATEGORY  08 - STOPOVERS          
         
  SEQUENCE: 04998900          
U EFFECTIVE: 04FEB 15          
         
     FOR FARES WITH FOOTNOTE Q          
         
       UNLIMITED STOPOVERS PERMITTED ON THE PRICING UNIT.          
         NOTE -          
         PERMITTED AT GUEST OWN EXPENSES.          
         
  SEQUENCE: 04999000          
  EFFECTIVE: 30OCT 14          
         
     UNLESS OTHERWISE SPECIFIED          
         
       UNLIMITED FREE STOPOVERS PERMITTED ON THE PRICING UNIT IN          
         AUH.          
         NOTE -          
         STOPOVER PERMITTED AT GUEST OWN EXPENSE          

CATEGORY  11 - BLACKOUT DATES          
         
  SEQUENCE: 04999800          
U EFFECTIVE: 04FEB 15          
         
     FOR FARES WITH FOOTNOTE 4R          
         
       TRAVEL IS NOT PERMITTED 15JUL 15 THROUGH 22JUL 15.          
         
  SEQUENCE: 04999900          
  EFFECTIVE: 01NOV 13          
         
     FOR FARES WITH FOOTNOTE E          
         
       FOR TICKETING ON/AFTER 02NOV 13          
         TRAVEL IS NOT PERMITTED 18DEC THROUGH 10JAN OF THE NEXT          
         YEAR.          
"
Is there any formula to pull out the data which is starting below the text "Effective" and ends one live above the text "SEQUENCE" or one line above the text "CATEGORY"

e.g if the below data start from cell A1.


CATEGORY  08 - STOPOVERS          
         
  SEQUENCE: 04998900          
U EFFECTIVE: 04FEB 15          
         
     FOR FARES WITH FOOTNOTE Q          
         
       UNLIMITED STOPOVERS PERMITTED ON THE PRICING UNIT.          
         NOTE -          
         PERMITTED AT GUEST OWN EXPENSES.          
         
  SEQUENCE: 04999000          
  EFFECTIVE: 30OCT 14          
         
     UNLESS OTHERWISE SPECIFIED          
         
       UNLIMITED FREE STOPOVERS PERMITTED ON THE PRICING UNIT IN          
         AUH.          
         NOTE -          
         STOPOVER PERMITTED AT GUEST OWN EXPENSE          

CATEGORY  11 - BLACKOUT DATES          


I want the data below to be extracted in column B

     FOR FARES WITH FOOTNOTE Q          
         
       UNLIMITED STOPOVERS PERMITTED ON THE PRICING UNIT.          
         NOTE -          
         PERMITTED AT GUEST OWN EXPENSES.          




     UNLESS OTHERWISE SPECIFIED          
         
       UNLIMITED FREE STOPOVERS PERMITTED ON THE PRICING UNIT IN          
         AUH.          
         NOTE -          
         STOPOVER PERMITTED AT GUEST OWN EXPENSE          

Hope this explains else i can send you the data to your mail.

Thanks
Rakesh

Answer
Rakesh,

I would use a Dummy column with a formula that would mark the boundary lines and identify the lines in between.  Then you could filter on this dummy column and copy the visible data to another sheet.  

If you want to send a sample workbook, I can put in some example formulas and you can see if that meets you needs.

send to twogilvy@msn.com

--
Regards,
Tom Ogilvy

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.