Combine rows of a table with similar values using VBA Excel

Question

I have to do sumif multiple time to get this problem solved.

Problem: I have a table with Part Number as the first column and it is always the same for unique part. However, the description 1 and description 2 are not perfect, even for the same part number (due to the typo, etc). I need to combine the Quantity at different inventories: QTY AT V1, V2, and V3

enter image description here

The result if I use multiple sumif and choose the the description appears first. Of course, there are like 50k of rows with many different PN#. Error often occurs if using sumif due to human error.

I would like to ask for help on this one. Compare the PN# if they are the same sum the quantity at different inventory locations, the description 1 and 2 just pick up whatever appear first (like CAR - BLACK and 4 WHEELS).

There are some similar questions and answers to this. However, they do not work well. Merge Cells

Maybe doing data validation on the entries will eliminate the human errors part and give you reliable sumif parameters — datatoo
– datatoo, Commented May 25, 2012 at 2:53
Thank Datatoo and Tim, however, I would like to have standard table and also data come from different sources. — NCC
– NCC, Commented May 25, 2012 at 11:18
Is your desried output data Really PN based, and you are using the description to try and sanitize the incorrect PN entries? — Pynner
– Pynner, Commented May 26, 2012 at 0:34
I would suggest an interim mapping table that maps the various entries to your target PNs then your output report can sum off the interim table. Hope that helps — Pynner
– Pynner, Commented May 27, 2012 at 16:33

Jon Crowell · Accepted Answer · 2012-05-28 15:43:16Z

1

+50

This should solve your problem. I set up a workbook with 2 tabs: RawData and PNTotals. I created data that resembles the two rows in your example. I have 26 rows with 3 different PN#s: Honda, Toyota, and Kia. The code works regardless of how many rows and PN#s you have.

After running the code below, I end up with totals by PN on the PNTotals tab that look like this:

HONDA   CAR - BLACK   4 WHEELS        936   516 2214
TOYOTA  CAR                           864   414 2079
KIA     CAR - RED     SPORT PACKAGE   504   204 1234

To get this to work, add the following code to a module and run the sub DispatchTotalsByPNNumber().

Option Explicit

Sub DispatchTotalsByPNNumber()
    Dim LastPN As Long

    LastPN = Sheets("RawData").Range("A1").End(xlDown).Row

    GetDistinctListOfPNNumbers (LastPN)
    GetQuantityTotalsForEachPNNumber (LastPN)

End Sub
Sub GetDistinctListOfPNNumbers(ByVal LastPN As Long)

    Sheets("PNTotals").Cells.Clear
    Sheets("RawData").Range("A2:A" & LastPN).Copy Sheets("PNTotals").Range("A1")
    Sheets("PNTotals").Range("a:a").RemoveDuplicates Columns:=1, Header:=xlNo
End Sub
Function DescCols(ByVal LastPN As Long) As Integer
    Dim i As Integer

    For i = 2 To 10 ' If you ever have more than 9 description columns, increase range here
         If Not IsNumeric(Cells(Cells(LastPN + 1, i).End(xlUp).Row, i)) Then
            DescCols = DescCols + 1
        Else
            Exit Function
        End If
    Next i
End Function

Sub GetQuantityTotalsForEachPNNumber(ByVal LastPN As Long)
    Dim i As Long
    Dim x As Integer
    Dim TotCols As Integer
    Dim PNN As String
    Dim ThisColumn As String
    Dim PNCount As Integer

    TotCols = Sheets("RawData").Range("A1").End(xlToRight).Column
    PNCount = 1
    ' get count of PN#s if there are more than 1
    If Sheets("PNTotals").Range("A2").Value <> "" Then
        PNCount = Sheets("PNTotals").Range("a1").End(xlDown).Row
    End If

    For i = 1 To PNCount
        PNN = Sheets("PNTotals").Range("A" & i).Value
        Sheets("RawData").Select
        Sheets("RawData").Range("A1").Select
        Sheets("RawData").Cells.Find(What:=PNN, after:=ActiveCell, searchorder:=xlByRows).Activate

        ' Copy description text from first instance of pn to total sheet for all description columns
        For x = 1 To DescCols(LastPN)
            Sheets("PNTotals").Cells(i, x + 1).Value = ActiveCell.Offset(, x).Value
        Next
        For x = x + 1 To TotCols
            ThisColumn = GetColumnLetter(x)
        ' set sumif formulas for however many quantity columns we have
        Sheets("PNTotals").Range(ThisColumn & i).Formula = "=SUMIF(RawData!A2:" & ThisColumn & LastPN & ",PNTotals!A" & i & ",RawData!" & ThisColumn & "2:" & ThisColumn & LastPN & ")"

        Next
    Next
End Sub

Function GetColumnLetter(ByVal ColNum As Integer) As String

    GetColumnLetter = Left(ActiveSheet.Cells(1, ColNum).Address(False, False), (ColNum <= 26) + 2)

End Function

NOTES: Assumes raw data starts in cell A1 of the RawData sheet and that there aren't any blank PN#s. If there are blanks, you'll need to determine the last PN row differently.

edited May 28, 2012 at 15:43

answered May 28, 2012 at 4:39

Jon Crowell

22.4k15 gold badges94 silver badges113 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

NCC Over a year ago

Thank you very much for answering my question. I just would like to have one small improvement. Assume that the PN# is always in first column. However, there are possible of more than two descriptions (Maybe 2 for this case but maybe 4 for another case) and more QTY. HOWEVER, NO DESCRIPTION WILL HAVE NUMBER ALONE - EITHER THEY EMPTY OR THEY ARE TEXT. AND NO QTYS HAVE TEXT, EITHER NONE (0) or NUMBER. Is they away to make it detects which column to sum (because there is no text) and which column to choose for description(because there is no number)?

Jon Crowell Over a year ago

NCC, I've modified the code to copy up to 9 description columns and however many quantity columns you have. Let me know if this works.

NCC Over a year ago

Some of its behaviors are very random: 1. If I only have 1 PN (HONDA), then it keeps adding the data vertically after finished summing. 2. If another sheet is active instead of Raw Data, it keep adding data horizontally. Often I only have 5-6 descriptions.

Jon Crowell Over a year ago

I am now checking for only a single PN, and code should work regardless of which sheet you start on. Try again and let me know.

Jon Crowell Over a year ago

What is the max number of descriptions you can have?

|

Collectives™ on Stack Overflow

Combine rows of a table with similar values using VBA Excel

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related