Question

我得做几次才能解决问题

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

""https://i.sstatic.net/Z94a6.jpg" alt="此处的内置图像描述"/"

如果先使用多个对称并选择描述, 则结果将出现。当然, 有大约50公里的行, 有许多不同的 PN# 。如果使用对称时因人为错误而发生错误。

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

Answer 1

这应该解决你的问题。我设置了一本工作簿, 上面有两个标签: RawData 和 PNTOTal 。我创建了类似您示例中两行的数据。我有26行, 有三个不同的 PN# : Honda, Fento, 和 Kia 。代码不管您有多少行和 PNP #, 都有效。

在运行下面的代码后,我最后在PNTOTals标签上的 PNTOTALs标签上得出了PN的总数, 看起来是这样的:

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

要做到这一点, 请在模块中添加以下代码, 并运行 sub DispatchTotalsByPNNO() 。

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: 假设原始数据起始于 RawData 工作表中的单元格 A1, 没有空白的 PN# 。如果有空白, 您将需要以不同方式确定最后一个 PN行。

友情链接