excel - Consolidation of dynamically changing data in VBA

Question

I work with data including unique names of customers and values of their purchases. I want to conduct its consolidation, meaning the result would be customers and their total sums of purchase like in the picture below:

The data is changing after every VBA code operation (new customers come in, old disappear, new transactions are recorded etc.) which somehow leads to this line of code generating errors:

Sheets("Sheet1").Range(D2).Consolidate Sources:=Array(Sheet1!R2C1:R3C51), Function:=xlSum

Is there a different approach to this situation so that there's no problem with getting the right results?

question from:https://stackoverflow.com/questions/65892479/consolidation-of-dynamically-changing-data-in-vba

Answer 1

Use excel formulas instead of vba.

Fomula for D2:

=IFERROR(INDEX($A$3:$A$7, MATCH(0,COUNTIF($D$1:D1, $A$3:$A$7), 0)),"")

and hit CTR+SHIFT+ENTER

Then use sumif in E2:

=SUMIF($A$2:$A$7, D2, $B$2:$B$7)

Drop the cell down.