Array VBA Help

Howdy all!

I'm trying to build a workbook that will read from a raw data table and consolidate into a second table.
I've included a test page "Array VBA Help" and the actual workbook I'm trying to build (with a handful of sample entries) "Count Sheet Test (1)".

Objectives:
Have a macro that will
1) Copy all of the static information from the raw data table (columns A and C in this example, more in my actual data), and paste them into the Export table, skipping blanks.
2) Remove duplicates from the Export table
((I have code to do this first part, though it's not very elegant))

Please Login or Register  to view this content.

3) Calculate the total quantity of an item in a location and the total reserves of that item in that location
((I have very inefficient code for this part))

Please Login or Register  to view this content.

4) Find and display the maximum unit price of an item

5) Calculate the aggregate value of the item across all locations at the maximum item value.

I know parts 1 and 2 can be done very quickly (and probably better than I have).
I'm pretty sure parts 3 and 5 are also easy code.
Part 4, though is the beast that I don't have any concept of how to tackle.

Holy crap! I would give you 1,000 stars for this if I could.

I don't know how this works, yet, but this works spectacularly!

Thank you!

One more thing, is it possible for the result in the aggregate column to be the total aggregate of a given item? I was using the following formula in column J, =I2*SUMIF(A:A,A2,G:G)

So for example, item 1106-018 (in rows 27 and 28), it's the same item in two locations, with 1 unit in the first location and 3 units in the second. The unit price is $3,711.40, and the way the macro runs now it returns an aggregate of $3,711.40 for the first location and $11,134.21 for the second. However, I would like it to show an aggregate of $14,845.61 on each row.

I know beggars can't be choosers, and I'm supremely grateful for what you've already written, so if this is a step too far, no worries.

Thank you again!

So I asked this question over a year ago, and xladept gave me this brilliant bit of code

Please Login or Register  to view this content.

It works extraordinarily well; however, it was just recently brought to my attention that it is rounding off decimals to whole integers. I tried swapping out the CLng with CSng but then got errors in processing.

I would appreciate it if someone could help me tweak the code so that it displayed decimal quantities.

New sheet uploaded.

The sheet in question is Consolidated CAT, and the cells that are rounding are in G:H.

Success!

Thank you once again!