VBA Macro to Calculate Average excluding Duplicates

Hi,

I am trying to calculate the average value for all items in each quater but there are a lot of duplicate items that I do not want their values be double counted in the average calculation. I could simply filter duplicates and get the regular average, but I cannot do that everytime for a lot of data. Therefore, I need a VBA Macro (guessing a loop one) that averages the "Quarter Values" column for all IDs if those IDs are not a repeat. (its okay for the values to be repeat because different IDs could possibly have same values). I also need the average only count for and sum positive numbers, not zero or negatives or texts. The last row is variant.

I would need the Macro be linked to the 'Calculate Button' on the Average Sheet.

test.xlsx

Thanks a lot

what if some of the data has the same ID but different value?

good point to bring up. In my case all the ID #s are unique. and each unique ID has the same value.

I am not sure if what I said makes sense, but for example, ID # 444 will always have the value 8 even if repeated multiple times.

How about This instead:

a Macro that would
1) Copy and Paste the two columns in a new sheet
2) Removes the duplicate ID rows from the data in the new sheet
3) Calculates the regular average based on the data remained in the new sheet
4) Copy pastes the calculated average in a cell in the Main worksheet
5) Deletes the temporary new sheet used to do all the dirty work

preferably all this hidden behind the scenes?