Creating Copy of Range on new Sheet for Calculations

**yunesm** · 11-10-2010, 12:49 PM

This is a repost of a question from earlier (that I messed-up). So I'm trying again!

I have a worksheet with two simple calculations that need to be done. I’ve attached my workbook which has my macro, the original worksheet and a final calculated worksheet (my end product).

The worksheet (QUERY3) has a list of dates in column C ('DispositionD)

I need to copy column C to a new worksheet so that I have a unique list of dates without blank cells. Then from the copied range I need to perform 2 calculations.

Calculation 1:
I need to count the number of times that date appears in the original range (see ‘sheet1’ of attached workbook of completed calculations)

Calculation 2:
I then need to count the results from calculation 1. What I’m doing for this now is creating unique list of the calculated 1 values and doing a simple countif for each value in the unique range.

I’ve included the macro that I just put together to accomplish this but I know there are betters ways of doing this.

For example:
Can I copy the range of unique values in 1 step? I’m currently creating a new worksheet (ws) copying/pasting the original range, creating a unique list from the copied range, then I’ll remove duplicates and then delete the original range. This seems like a lot of steps to me…

Also, when I’m creating unique the list from the calculated counts in calculation 1 I’m getting a duplicate on the number 1. I don’t know why.

Finally, please look at the way I’m calculating my counts. I’m quit certain this is not the best way to do it. I haven’t tested this enough but I’m not even sure that my counts will work dynamically as this list changes each day. This file QUERY3 file is an output from another program.

Suggestions and feedback of any kind are always appreciated.
Thanks!

Please Login or Register  to view this content.

**StephenR** · 11-10-2010, 01:20 PM

Your code could be speeded up, but it seems to work properly.

EDIT: not sure this is much quicker, but it should work adjust to however much data you have:

Please Login or Register  to view this content.

**yunesm** · 11-10-2010, 01:51 PM

Thanks Stephen! This is definetly better. Your edits make a lot of sense. The changes are small but I learn a lot whenever people like you take the time to help me. I appreciate your time.

Thanks again!