Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Leslie Coover
 
Posts: n/a
Default Cleaning up data from Crystal Report

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


  #2   Report Post  
Dave Peterson
 
Posts: n/a
Default

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)

Leslie Coover wrote:

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
  #3   Report Post  
Dave Peterson
 
Posts: n/a
Default

oops. I dropped a ).

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

Sorry.

Dave Peterson wrote:

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)

Leslie Coover wrote:

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


--

Dave Peterson
  #4   Report Post  
Leslie Coover
 
Posts: n/a
Default

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" wrote in message
...
oops. I dropped a ).

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

Sorry.

Dave Peterson wrote:

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)

Leslie Coover wrote:

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


--

Dave Peterson



  #5   Report Post  
Dave Peterson
 
Posts: n/a
Default

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.

Leslie Coover wrote:

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" wrote in message
...
oops. I dropped a ).

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

Sorry.

Dave Peterson wrote:

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)

Leslie Coover wrote:

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


--

Dave Peterson


--

Dave Peterson
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I compare 2 sets of data and highlight differences? Perplexed1 Excel Worksheet Functions 1 July 9th 05 01:15 AM
How do I import formmail data to a custom excel template? cxlough41 Excel Worksheet Functions 1 July 1st 05 12:59 AM
Pulling data from a big report nbeaird_51 Excel Discussion (Misc queries) 5 March 17th 05 06:06 PM
Inconsistant Data Cleaning gallegos1580 Excel Discussion (Misc queries) 1 January 10th 05 09:19 PM
Running Data Table using an input that triggers DDE linked data [email protected] Excel Discussion (Misc queries) 1 December 16th 04 12:56 PM


All times are GMT +1. The time now is 12:48 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"