Data analysis of imported Report

[Mike Fogleman]

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

 

[Bernie Deitrick]

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]

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.

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.

 

[Mike Fogleman]

I've got it now, thanks Bernie.

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.

(without the header), then use Edit | Go To.... Special...
are equal to N. If you want G OR V equal to N, then filter
delete only those rows where G AND V are equal to Y. V each