将同一材质的多行转换为单行

将同一材质的多行转换为单行

每种材料都有多行(行数会有所不同),制造商和制造商零件编号的值也不同。我需要将其转换为每种材料一行,所有制造商和制造零件编号都在同一行。

由于我无法附加图像,因此下面提供了示例:

现有数据

Material1 Mfgr1 MPN11    
          Mfgr2 MPN12

Material2 Mfgr2 MPN21    
          Mfgr3 MPN22    
          Mfgr4 MPN23

所需数据

Material1 Mfgr1 MPN11 Mfgr2 MPN12    
Material2 Mfgr2 MPN21 Mfgr3 MPN22 Mfgr4 MPN23

答案1

这段代码可以达到这个目的:

Sub materialsinglerow()
Dim wkb As Workbook
Dim wks, wks1 As Worksheet
Set wkb = ThisWorkbook
Set wks = wkb.Sheets(1)
Set wks1 = wkb.Sheets(2)
continue = True
i = 1
j = 0
k = 2
emptycount = 0
lastmaterial = ""
While continue = True
    material = wks.Cells(i, 1)
    mfgr = wks.Cells(i, 2)
    mpn = wks.Cells(i, 3)
    If material <> "" Then
        j = j + 1
        k = 2
        wks1.Cells(j, 1) = material
        lastmaterial = material
    End If
    If mfgr <> "" Then
        wks1.Cells(j, k) = mfgr
        k = k + 1
        wks1.Cells(j, k) = mpn
        k = k + 1
    Else
        emptycount = emptycount + 1
    End If
    If emptycount > 10 Then
        continue = False
    End If
    i = i + 1
Wend

它从工作表1并将结果写入工作表2假设原始数据为列A, B and C

当列中空单元格超过 10 个时,它就会停止C

相关内容