Macro that would Copy/Paste to new sheet and remove duplicates

Give this a try

Please Login or Register  to view this content.

Hey dani_n88

If you upload a workbook with your correct format i'll see what I can do.

Test Template.xlsm

First sheet (“Unit”) could be ignored here.

******Average Method 1

By clicking on the ‘Calculate Average 1’ Button in “Industry Average 1” sheet:
For all the Quarters mentioned in the second row of the “Industry Average 1” sheet from the second column forward, Under each Quarter, called upon and recognized by its text (e.g. 1Q12, 2Q12, etc) and matched by that same quarter in the “Rate Calculation” Sheet,

The ‘Reference Rates’ in the “Rate Calculation” the be SUMMED and COUNTED, from the first existing ‘ID 2’ Reference Rate in that page all the way to the last existing ‘ID 2’ Reference Rate,

If the given conditions hold:

1) The corresponding ‘ID 2’ numbers are not being double counted, (Two different internal codes could have the same ‘ID 2’ numbers, which means they are identical items but just have different internal codes, and therefore we do not want to count them twice).

2) ‘Reference Rates’ are greater than zero and are numbers only
Then the sum and the count are shown and the average is calculated by dividing the sum by the count for each called-up quarter.

******Average Method 2

By clicking on the ‘Calculate Average 2’ Button in “Industry Average 2” Sheet:

For each matched up Quarter between “Industry Average 2” sheet and the “Rate Calculation” sheet,

1) Interests in the “Rate Calculation” sheet be summed up for all ‘ID 2’s for each called-up quarter, only if, both the interest and balance of that ‘ID 2’ are numbers and greater than zero and the ‘ID 2’ is not repeating. (no double counting)

2) Balances in the “Rate Calculation” sheet be summed up for all ‘ID 2’s for each called-up quarter, only if, both the interest and balance of that ‘ID 2’ are numbers and greater than zero and the ‘ID 2’ is not repeating. (no double counting)

3) The Count of all those ‘ID 2’s that had interests and balances of number type and greater than zero be displayed for each quarter

4) The Average would show the total Interest for that quarter divided by the total balance for that quarter

******I would need the matching of Quarters in both sheet be dynamic. And whatever quarter I type in any cell of the second row, call up that quarter in the “Rate Calculation” sheet and display the results right under it.

Sorry if its a lot or confusing. I've been trying to incorporate your original codes for the past 10 hours but just could figure out.

I would really really appreciate the help Mike. Thanks man

dani,

Sorry Ive been preety busy lately. Yea its a little long for reading. But if you can list what sheets Industry Average 1 and Industry Average 2 output should look like it may be easier for me to understand.

To be honest no. I don't think I can. I'll post a message and see if someone else may be able to help.