如何有条件地将一行Excel数据从一张表追加到另一张？ [英] How do I conditionally append a row of Excel data from one sheet to another?

问题描述

我不经常使用Excel，但我希望有一个相当简单的方法来解决这个问题。我查看了许多其他解决方案，包括将数据从一张表粘贴到另一张，但我找不到任何允许我（1）将单元格从一张表匹配到另一张，然后（2）有条件地附加或连接数据而不是简单地粘贴它。

I don't use Excel very often, but I'm hoping there is a fairly straightforward way to get through this. I looked through a number of other solutions involving pasting data from one sheet to another, but I couldn't find anything that would allow me to (1) match a cell from one sheet to another and then (2) conditionally append or concatenate data instead of simply pasting it over.

我有一个带有两张数据的Excel文档。两张纸都包含数字ID列。
我基本上需要将Sheet从Sheet2匹配到Sheet1，然后将Sheet2中的行数据从Sheet1附加到匹配的行。我会想象它会像这样：

I have an Excel document with two sheets of data. Both sheets contain a numerical ID column. I basically need to match the ID's from Sheet2 to the Sheet1 and then append the row data from Sheet2 to the matching rows from Sheet1. I would imagine it will look something like this:

If Sheet2 ColumnA Row1 == Sheet1 ColumnA RowX
  Copy Sheet2 Row1 Columns
  Paste (Append) to Sheet1 RowX (without overwriting the existing columns).

对不起，如果有更好的方式来形成这个问题。我已经设法想到自己在圈子，现在我觉得我有一个困惑的Nigel Tufnel看起来在我的脸上。

Sorry if there is a better way to form this question. I've managed to think myself in circles and now I feel like I have a confused Nigel Tufnel look on my face.

[更新：更新以澄清要复制的单元格。]

[Update: Updated to clarify cells to be copied.]

推荐答案

我认为这是你想要做的？

I think this is what you are trying to do?

代码未经测试。我相信它应该奏效如果您收到任何错误，请告知我们，我们将会在此处进行...

The code is untested. I believe it should work. If you get any errors, let me know and we will take it form there...

Sub Sample()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim ws1LR As Long, ws2LR As Long
    Dim i As Long, j As Long, LastCol As Long
    Dim ws1Rng As Range, aCell As Range
    Dim SearchString

    Set ws1 = Sheets("Sheet1")
    '~~> Assuming that ID is in Col A
    '~~> Get last row in Col A in Sheet1
    ws1LR = ws1.Range("A" & Rows.Count).End(xlUp).Row
    '~~> Set the Search Range
    Set ws1Rng = ws1.Range("A1:A" & ws1LR)

    Set ws2 = Sheets("Sheet2")
    '~~> Get last row in Col A in Sheet2
    ws2LR = ws2.Range("A" & Rows.Count).End(xlUp).Row

    '~~> Loop through the range in Sheet 2 to match it with the range in Sheet1
    For i = 1 To ws2LR
        SearchString = ws2.Range("A" & i).Value

        '~~> Search for the ID
        Set aCell = ws1Rng.Find(What:=SearchString, LookIn:=xlValues, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)

        '~~> If found
        If Not aCell Is Nothing Then
            LastCol = ws2.Cells(i, ws2.Columns.Count).End(xlToLeft).Column

            '~~> Append values
            For j = 2 To LastCol
                ws1.Cells(aCell.Row, j).Value = ws1.Cells(aCell.Row, j).Value & " " & ws2.Cells(i, j).Value
            Next j
        End If
    Next i
End Sub

HTH

Sid

这篇关于如何有条件地将一行Excel数据从一张表追加到另一张？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！