Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Consolidate Data in Excel that has Multiple Duplicate Values


I'm currently having trouble with a table. In this video https://www.youtube.com/watch?v=IGaq_biQ1B8  they show how to consolidate a table with multiple duplicated names. 

My question is why doesnt data update in the consolidated table when I change amounts in the non consolidated table?




Hello RUSSO and welcome to the forum.

The resultant consolidated table doesn't update because there are no formulas in the table.

However, it is possible to have the table update using VBA. There are two ways to accomplish this. 1) Place the macro in a regular code module and assign it to a "button" on the sheet which when clicked updates the table. [see attached file], 2) Place the code in the worksheet code window as a Worksheet_Change event. [sheet2 of attached file] The code was tweaked to include defining the target range. The following line was added as the first line:

If Intersect(Target, Range("B:B")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub

This means that the code will only run (be triggered) if a cell in column "B" is changed.

Here is the macro:

Sub Consolidate_1()

' Consolidate_1 Macro
' macro written by WillieD24 for teachexcel.com

'Dim LRsrc As Long   ' last row in source table
Dim LRcon As Long   ' last row in consolidated table

'LRsrc = Range("A" & Rows.Count).End(xlUp).Row
LRcon = Range("D" & Rows.Count).End(xlUp).Row
' delete existing consolidated table data
Range("D2:E" & LRcon).ClearContents

    Application.CutCopyMode = False
    ' edit file path below to match your situation
    Selection.Consolidate Sources:= _
        "'C:\Users\USER\Documents\My Excel Stuff\TeachExcel Files\[ConsolTest1.xlsm]Sheet1'!R2C1:R11C2" _
        , Function:=xlSum, TopRow:=False, LeftColumn:=True, CreateLinks:=False


End Sub 

If rows of data are added or removed from the source table then this line will need to have the R11 reference edited to match. (in both cases)


If this solves your situation please mark the answer as selected.

Cheers   :-)


Answer the Question

You must create an account to use the forum. Create an Account or Login