how to consolidate rows

try using a pivot table

click on any cell where u have the data, press alt dpf ... a blank pivot table would appear in a new sheet

tick all the fields as shown below - below should be the output

Hi,

This M code in Power Query works

let

Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], 

#"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Value A", Int64.Type}, {"Value B", Int64.Type}, {"Value C", Int64.Type}}), 

#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Name"}, "Attribute", "Value"), 

#"Pivoted Column" = Table.Pivot(#"Unpivoted Other Columns", List.Distinct(#"Unpivoted Other Columns"[Attribute]), "Attribute", "Value") 

in

#"Pivoted Column"

Hope this helps.

Hello

With Power Query in this sample workbook:

• Put your data in Input Table
• Right-click somewhere in Query output > Refresh

Hi Carla Kline,

Based on your description, it seems like that you want to know how to implement de-duplication and merge rows via VBA, we can fully understand your distress and will do everything we can to help you.

Based on your example, I have written a VBA script for you, you can run the script by following these steps:

1. Click on Developer on Ribbon.
2. Click on Macros.
3. In the newly open window, enter a name, for example, in my case, test, and click Create.  
4. Paste the following code between Sub test() and End Sub.

       Dim ws As Worksheet 
   
       Dim lastRow As Long, lastCol As Long 
   
       Dim lastRowH As Long 
   
       Dim i As Long, j As Long 
   
       Dim formulaRange As Range 
   
       Dim colLetter As String 
   
       Set ws = ActiveSheet 
   
       ws.Range("H1:Z10000").ClearContents 
   
       lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row 
   
       lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column 
   
       ws.Range("A1:A" & lastRow).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=ws.Range("H1"), Unique:=True 
   
       ws.Range("B1:D1").Copy Destination:=ws.Range("I1") 
   
       lastRowH = ws.Cells(ws.Rows.Count, 8).End(xlUp).Row 
   
       Set formulaRange = ws.Range(ws.Cells(2, 9), ws.Cells(lastRowH, 8 + lastCol - 1)) 
   
       For j = 1 To lastCol - 1 
   
       colLetter = Split(ws.Cells(1, j + 1).Address, "$")(1) 
   
       formulaRange.Columns(j).Formula = "=IF(COUNTIFS(" & colLetter & "$2:" & colLetter & "$" & lastRow & ","">0"",$A$2:$A$" & lastRow & ",$H2)>1,""error"",SUMIFS(" & colLetter & "$2:" & colLetter & "$" & lastRow & ",$A$2:$A$" & lastRow & ",$H2))" 
   
       Next j 
   
       formulaRange.Value = formulaRange.Value
   

5. Press Ctrl + S to save the code and click Yes if you get a popup.
6. Select the sheet you want to de-merge, click on Macros on Ribbon.
7. Select the macro you just created, and then click Run.

If everything works fine, the data should be organized like this:

If you want to do further testing or any want to exchange ideas, I will share the Excel document containing the Macro with you in a private message, you can open a private message by clicking on your avatar in the upper right corner of the screen, clicking on the three dots, and clicking on View Private Message.

If you have further questions, please feel free to contact us!

Thanks for your cooperation and understanding. I hope that you are keeping safe and well!

Best Regards,

Thomas C - MSFT | Microsoft Community Support Specialist