Deleteing all rows staring with

[chesspupil]

Using an external UNIX program that quieries a database, I export all the data found based on my query and export the report as a text file. THe text file can be up to 1 MB of raw text.

Selecting all text and pasting the data into collum 1 using PASTE SPECIAL -->(TEXT)

All Data is in Collum A

Each record returned has between 40-50 values each on a seperate row, only 10 of which I need.

I am looking for a way to find the rows that begin with the unwanted field, and then delete the entire row.

Otherwise ignore the row.

IE:

AUDFI : (data path loc here)

BILLNUM : 060606

CUTNUM : (0000)

SD : 120012

ED : 121259

CC : 123456789 MATERIAL LISTS AND ITEMS

COMMENT : Multiple lines of text here

with continuing free form data, numbers, operators,

then the next data file would repeat:

ADUFI : (data path loc here)

...

...

...

etc.,

In the above example, I want to delete the rows AUDFI, & CUTNUM and move the below items up to take the empty rows place. The BILL NUM, SD,ED, and COMMENT are filed that are always kept (as well as others). Each record has roughly the same data, each tailored to an event or object.

The number of rows of data dumped into excel can easily exceed 50000, about 200 - 1500 records in the report.

After wards, the data is dumped to a text file for easier analysis.

I hope you can help me.

In BASIC my line might look like

5 START

10 if A:$="AUDFI :" then delete row, move below contents up.

20 if A:$="CUTNUM :" then delete row, move below contents up.

30 END

(I think there needs to be a loop since the vaules AUDFI and CUTNUM (as well as other unwanted fields can occurr 200 - 1500 times each.)

[Romany777]

I can think of a few ways to do this and none of them involves any macros.

The simplest involves creating a column for numbering the records. This will only work if all records have the same number of data field rows. Set a "1" for every field in the first record, and then "A1 + 1" for the first field of the second record and copy to the bottom. Copy the entire column and Paste Values. Then sort by the second column (your original column A) and do wholesale deletions of the unwanted fields - then resort by Record Number and Field.

The next simplest involves using text to column (with a ":" delimiter) to separate the Field Names from the Field Data. Set up a short table in another sheet that will contain the field names that you want to keep. Back on the data sheet - use a vlookup (set for exact, not range lookup) to compare the Field Name on the data sheet to the table. If it's not found it will show as N/A. Create column headings for "LOOKUP", "FIELD NAME" and "DATA". Set a data filter on the data sheet and filter to show all instances of N/A in the LOOKUP column. *****SAVE THE FILE****** Highlight the first row (the one with the column headings) and FORMAT ROW HIDE. Highlight the ENTIRE SHEET (only the rows with N/A will be showing) Then set for VISIBLE CELLS ONLY (Edit, goto, special, visible cells only) - then cross you fingers and EDIT DELETE. This will delete all the rows you want. Then highlight the entire sheet and FORMAT ROW UNHIDE.

You can certainly practice on a subset of your data to make sure it works.

Good luck!