Need help consolidating data from 3 columns. I have a VBA code for QTY but the last column will now be data instead, how do I change code?

[kholden1]

Good morning!

You all helped me come up with a VBA code that takes a part number, manufacturer, and qty and consolidates duplicates. I have been messing around with that original VBA code but I cannot seem to figure out how to replace with a new 3rd column and consolidate it the same. It keeps trying to count a qty that isn't there since my change with column 3 is words. I've added a picture of what I'm currently working on. I want each column to consolidate, as long as it's the same as columns B and C. If it's different than either column, then I'd want it to start as a new line.

Here is my original VBA code:

Sub Consolidate()

Dim ws1 As Worksheet

Dim ws2 As Worksheet

Dim lrow As Long

Dim i As Long



Set ws1 = ActiveSheet

Set ws2 = ActiveWorkbook.Worksheets.Add

ws2.Name = "ONLINEINV3"


lrow = ws1.Cells(Rows.Count, 1).End(xlUp).Row



ws1.Range("A1:B" & lrow).Copy ws2.Range("A1")



ws2.Range("A:B").RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes

ws2.Range("C1") = "FULL PARTNO"

ws2.Range("D1") = "QTY"


lrow = ws2.Cells(Rows.Count, 1).End(xlUp).Row


For i = 2 To lrow

ws2.Cells(i, 3) = ws2.Cells(i, 2) & " " & ws2.Cells(i, 1)

ws2.Cells(i, 4) = Application.SumIfs(ws1.Range("C:C"), ws1.Range("A:A"), ws2.Cells(i, 1), ws1.Range("B:B"), ws2.Cells(i, 2))



Next i


End Sub

 

[rlv01]

YES!!! You saved a week of my life! Thank you SO much.

Do you have Venmo? I'd like to send you something for helping me! I insist!

It's a nice thought about Venmo, but no one here does this for money. I'm glad I could help.