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]

It helps if you use code tags when posting your code. Here is your original code using code tags

'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

Code tag instructions:

How to Post Your VBA Code

Post your VBA code.

www.mrexcel.com

 

[rlv01]

Instead of posting your data as an image, post it in a way that others can easily copy if they need it to analyze or experiment with.

Book1
	A	B	C
1	Part	MFG	Internal Part
2	a	name	a
3	a	name	a
4	a	name	a1
5	a	name	a1
6	b	name1	b
7	b	name1	b1
8	c	name2	c
9	c	name2	c
10	c	name2	c
Sheet1

You can use this free tool

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

 

[rlv01]

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.

This part is not clear at all. Instead, post an example of what the new sheet should look like for a given set of data on the input sheet.

 

[kholden1]

I apologize, I am having trouble downloading the add-in right now. I will keep trying. I think I am in over my head.

Right now my table works like this:

Part Number	Manufacter	qty
abc123	Micro	500,000
abc123	Micro	25,600
abc123	Conn	300,000
bcd234	Micro	40,000
bcd234	Conn	100,000

The VBA code consolidates all of my duplicates from column 1 and then adds the qtys. If column b or c is different than column 1 then it creates a new line. It ends up looking like the picture attached on the top half.

I am trying to get the code to still consolidate everything but there is no quantity needed to add for this new code. Here is an example of what it would look like with the data. How it would look when cleaned with the code is in the bottom half of the image. Please let me know if I am still explaining this badly. Thank you!

Part number	Manufacturer	Internal part number
a	Micro	a1
a	Conn	a1
a	Conn	a1
a	Micro	a1

b	Conn	b1
b	Micro	b1
b	Micro	b12

b	Micro	b12
c	Conn	c1
c	Foxtrot	c1

 

[kholden1]

Sorry, I just saw the last table didn't format correctly. My data looks like this. After the VBA Code items like lines 2 and 3 would combine since they're all the same data on all 3 lines. Items 1 and 4 would combine since they're also the same data.

Part number	Manufacturer	Internal Number
a	Micro	a1
a	Conn	a1
a	Conn	a1
a	Micro	a1
b	Conn	b1
b	Micro	b1
b	Micro	b2
c	Conn	c1
c	Foxtrot	c1

 

[rlv01]

I am more confused now.

ter the VBA Code items like lines 2 and 3 would combine since they're all the same data on all 3 lines. Items 1 and 4 would combine since they're also the same data.

I have literally no idea what you mean when you say "combine". You have to show me, not tell me.

 

[kholden1]

I'm sorry! Consolidate, not combine. Please let me know if this is more helpful?

Part Number	Manufacturer	Internal Part Number		Part Number	Manufacturer	Internal Part Number
a	Micro	a1		a	Micro	a1
a	Conn	a1		a	Conn	a1
a	Conn	a1		b	Conn	b1
a	Micro	a1		b	Micro	b1
b	Conn	b1	would consolidate into	b	Micro	b2
b	Micro	b1		b	Conn	b2
b	Micro	b2		c	Conn	c1
b	Micro	b2		c	Foxtrot	c1
c	Conn	c1
c	Foxtrot	c1

 

[rlv01]

Perhaps this.

Sub Consolidate2()
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet

    Set ws1 = ActiveSheet
    Set ws2 = ActiveWorkbook.Worksheets.Add
    ws2.Name = "ONLINEINV3"

    With ws1
        .Range("A1:C" & .Range("A" & .Rows.Count).End(xlUp).Row).Copy ws2.Range("A1")
    End With

    With ws2
        .Range("A:C").RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes
        .UsedRange.Columns.AutoFit
    End With
End Sub

 

[kholden1]

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!