Data analysis of imported Report
 

I have a 21 column report of service calls imported from an AS400 database.
I get a new report weekly. I need to copy/paste special/values to another
sheet so I can convert date and time fields and calculate the average time
to complete the service calls. There are 3 types of service calls indicated
in column D by a C, D, or T. Column G has either a Y or N, and column V has
a Y or N. I need to extract the date/time columns, E,F and J,K for each of
the 3 types of service calls that also has an N in columns G and V. My plan
is to do this 3 times, once for each type, storing the results and clearing
the data for each type. Any help accomplishing this would be greatly
appreciated....Thanx in advance....Mike

Data analysis of imported Report
 

Mike,

Select your table, then use Data | Filter... AutoFilter.. on column G select "Y" from the dropdown.

That will show all the records with Ys in columns G. Select the table (without the header), then use Edit | Go To.... Special...
"Visible" and then use Delete entire rows.

Repeat for column V, and you'll be left only with records where G AND V are equal to N. If you want G OR V equal to N, then filter
both at the same time, using "Y" as your criteria prior to deletion, to delete only those rows where G AND V are equal to Y.

HTH,
Bernie
Excel MVP


"Mike Fogleman" wrote in message ...
I have a 21 column report of service calls imported from an AS400 database.
I get a new report weekly. I need to copy/paste special/values to another
sheet so I can convert date and time fields and calculate the average time
to complete the service calls. There are 3 types of service calls indicated
in column D by a C, D, or T. Column G has either a Y or N, and column V has
a Y or N. I need to extract the date/time columns, E,F and J,K for each of
the 3 types of service calls that also has an N in columns G and V. My plan
is to do this 3 times, once for each type, storing the results and clearing
the data for each type. Any help accomplishing this would be greatly
appreciated....Thanx in advance....Mike

Data analysis of imported Report
 

I agree that Auto Filter needs to be used, but I want to use it with code
and copy the filtered list to another sheet or copy all data to a new sheet
and use the Filter code there. That is to preserve the original data where
it is.
Bernie Deitrick wrote in message
...
Mike,

Select your table, then use Data | Filter... AutoFilter.. on column G
select "Y" from the dropdown.

That will show all the records with Ys in columns G. Select the table
(without the header), then use Edit | Go To.... Special...
"Visible" and then use Delete entire rows.

Repeat for column V, and you'll be left only with records where G AND V
are equal to N. If you want G OR V equal to N, then filter
both at the same time, using "Y" as your criteria prior to deletion, to
delete only those rows where G AND V are equal to Y.

HTH,
Bernie
Excel MVP


"Mike Fogleman" wrote in message
...
I have a 21 column report of service calls imported from an AS400
database.
I get a new report weekly. I need to copy/paste special/values to
another
sheet so I can convert date and time fields and calculate the average
time
to complete the service calls. There are 3 types of service calls
indicated
in column D by a C, D, or T. Column G has either a Y or N, and column V
has
a Y or N. I need to extract the date/time columns, E,F and J,K for each
of
the 3 types of service calls that also has an N in columns G and V. My
plan
is to do this 3 times, once for each type, storing the results and
clearing
the data for each type. Any help accomplishing this would be greatly
appreciated....Thanx in advance....Mike

Data analysis of imported Report
 

I've got it now, thanks Bernie.
"Mike" wrote in message
...
I agree that Auto Filter needs to be used, but I want to use it with code
and copy the filtered list to another sheet or copy all data to a new
sheet
and use the Filter code there. That is to preserve the original data where
it is.
Bernie Deitrick wrote in message
...
Mike,

Select your table, then use Data | Filter... AutoFilter.. on column G
select "Y" from the dropdown.

That will show all the records with Ys in columns G. Select the table
(without the header), then use Edit | Go To.... Special...
"Visible" and then use Delete entire rows.

Repeat for column V, and you'll be left only with records where G AND V
are equal to N. If you want G OR V equal to N, then filter
both at the same time, using "Y" as your criteria prior to deletion, to
delete only those rows where G AND V are equal to Y.

HTH,
Bernie
Excel MVP


"Mike Fogleman" wrote in message
...
I have a 21 column report of service calls imported from an AS400
database.
I get a new report weekly. I need to copy/paste special/values to
another
sheet so I can convert date and time fields and calculate the average
time
to complete the service calls. There are 3 types of service calls
indicated
in column D by a C, D, or T. Column G has either a Y or N, and column
V
has
a Y or N. I need to extract the date/time columns, E,F and J,K for
each
of
the 3 types of service calls that also has an N in columns G and V. My
plan
is to do this 3 times, once for each type, storing the results and
clearing
the data for each type. Any help accomplishing this would be greatly
appreciated....Thanx in advance....Mike