Home Articles

Excel将单元格合并到批量数据的一列中

Asked
Viewed 1809 times
0

我对从布局不良的供应商发送的产品数据感到非常困难 . 我希望规范在每个产品的不同列中 . 然而,他们已将它们列在2列中,使生活变得困难 .

我有一个我想要使用的公式,但是,我需要将其向下拖动并跳过不属于该产品的行 .

例如:C2到C8和D2到D8由产品10000ES的数据组成,下一个产品需要从C9到C18和D9到D18的数据 .

我希望在我的HTML网站中使用这个公式,以便使用它 . 但如果我把它拖下来它会捕获我不想要的细胞 .

=CONCATENATE("<tr>",F2,"<tr>",F3,"<tr>",F4,"<tr>",F5,"<tr>",F6,"<tr>",F7,"<tr>",F8,"<tr>",F9,"<tr>",F10,"<tr>")

Image of excel sheet

1 Answer

  • 1

    根据我在单元格G2中输出的输出,我想我可以确定你所追求的是什么 . 试试这个:

    Sub TryThis()
    
    Dim rng As Range, writeto As Range
    Dim outpt As String
    
    Set rng = ActiveSheet.Range("B1:D38") ' set this to cover the range down to the bottom
    
    Set writeto = ActiveSheet.Range("G:G") ' this is the column to output to
    
    For Each rw In rng.Rows
        If rw.Cells(1, 2) <> "" Then
            If rw.Cells(1, 1) = 1 Then
                writeto.Cells(writeto.Rows.Count, 1).End(xlUp).Offset(1, 0) = outpt
                outpt = ""
            End If
            outpt = outpt & "<tr>"
            outpt = outpt & "<th>" & rw.Cells(1, 2) & "</th>"
            outpt = outpt & "<td>" & rw.Cells(1, 3) & "</td>"
        End If
    Next
    outpt = outpt & "<tr>"
    writeto.Cells(writeto.Rows.Count, 1).End(xlUp).Offset(1, 0) = outpt
    End Sub
    

Related