Cleaning up data from Crystal Report

[Leslie Coover]

The agency where I work creates various reports (specific records) from a
large database. When I export it to Excel there is a lot of "garbage" that
I don't need. (e.g. headings and blank rows that print repeatedly through
the worksheet.

Consider the following as an example of how the data appears in Excel once
it is imported from crystal

Work Dept
ID Field01 Field02 Field03 Field04
Work Dept
W12345602 a b c d
W23456725 d c b a
123

W12340678 a b c d
W23356787 d c b a

I want only the records (ID, Field01 through Field04) that contain an
ID number (ID numbers always start with a W and have 8 numeric digits
after the W.

How can I extract the records that contain ID numbers to another sheet and
leave the garbage behind? Without having to go through the entire sheet and
hand delete each row that does not contain an ID number.

Thanks,

Les

 

[Dave Peterson]

I'd add a helper column of cells with formulas like:

=if(and(len(a1)=9,left(a1,1)="w",isnumber(-right(a1,8)),"keepit","deleteit")

Then drag it down the column.

Apply data|filter|autofilter
show the deleteit lines and delete those visible rows.

(maybe start on Row 3, delete row 1 manually and fix row 2 to be the nicer
headers)

 

[Dave Peterson]

oops. I dropped a ).

=if(and(len(a1)=9,left(a1,1)="w",isnumber(-right(a1,8))),"keepit","deleteit")

Sorry.

 

[Leslie Coover]

Thank you for information, here is how I did it.

(1) Select all columns that contain data
(2) Edit, Clear, Format
(3) Delete empty columns
(4) Select all columns that contain data and choose Data, Filter, AutoFilter
(5) Select "Custom" in the dropdown list for the ID field
(6) Set the criterial to "Contains" "W"

This seems to work okay, but I was wondering how else the Contains
option on the "Custom" filter might be used. Would it recognize W########
as a W followed by 8 numbers? And what if you have a column (Field01) that
contains items like A, B, C, D would the Contains option recognize
A,C or how would you enter the criteria to get all the records that
have A or C in Field01.

Les

 

[Dave Peterson]

Excel doesn't support that kind of wildcard (# as numeric).

That's why I suggested moving the "thinking" out of the filter custom options
and use a helper column of cells that return true or false.

I find building formulas that return that kind of classification easier than
learning to use data|filter|advanced filter. But you could try that to see if
it works for you.

Thank you for information, here is how I did it.

(1) Select all columns that contain data
(2) Edit, Clear, Format
(3) Delete empty columns
(4) Select all columns that contain data and choose Data, Filter, AutoFilter
(5) Select "Custom" in the dropdown list for the ID field
(6) Set the criterial to "Contains" "W"

This seems to work okay, but I was wondering how else the Contains
option on the "Custom" filter might be used. Would it recognize W########
as a W followed by 8 numbers? And what if you have a column (Field01) that
contains items like A, B, C, D would the Contains option recognize
A,C or how would you enter the criteria to get all the records that
have A or C in Field01.

Les