Excel比较两列，将匹配行复制到新工作表 [英] Excel Compare two columns, copy matching rows to new sheet

问题描述

我在Excel中有一个工作表，有5列数据：A，B ，C，D& E。

我有另一列F有一些数据。

我想要的是一个公式或宏所以我可以从列B中的列F中找到所有值，并只将整个行（A，B，C，D& E）复制到新工作表中（或只删除所有其他值）。

$

我试过做一个公式并删除错误行，但它会弄乱数据。

解决方案

因为我没有数据，所以你有如下的数据

/ p>

  ABC 
行1值1值3 
行2值2值5 
行3值3值8 
行4值4值3 
行5值5值5 
行6值6值8 
行7值7值3 
行8值8值5 ​​
行9值9值8 
  

/ p>

  AB 
第3行3 
第5行5 
第8行8 
  

尝试以下代码

  Sub Macro1（）
 
 Dim Counter As Long 
 
 Dim lastRow As Long 
 lastRow = 10 
 Dim arrColF（10）As字符串
 
表格（Sheet1）。选择
 
对于Counter = 1 To lastRow步骤1 
 arrColF（Counter  -  1）= Sheet1.Cells 3）.Value 
 Next Counter 
 
 arrStr = Join（arrColF，，）
 Dim strRange As String 
 
对于Counter = 1 To lastRow步骤1 
如果InStr（1，arrStr，Sheet1.Cells（Counter，2）.Value& ，）> 0 Then 
 strRange = strRange& A&计数器& ：B&计数器
如果Trim（strRange）<> then strRange = strRange& ，
 End If 
 Next Counter 
 strRange = Left（strRange，Len（strRange） -  1）
 
 Sheet1.Range（strRange）.Select 
 Selection.Copy 
表格（Sheet2）。选择
范围（A1）。选择
 ActiveSheet.Paste 
 
 End Sub 
  

I've been trying to do this for some time keep hitting a roadblock.

I have a sheet in Excel that has 5 columns with data: A,B,C,D & E.

I have another column F that has some data.

What I want is a formula or macro so that I can find all values from column F in column B, and copy only that entire row (A,B,C,D & E) into a new sheet (or just drop all other values).

I tried doing a formula and removing the error rows, but it messes the data up.

Any ideas?

解决方案

As i undestand you have data like following

Compare column C with B

A       B       C
Row 1   Value 1 Value 3
Row 2   Value 2 Value 5
Row 3   Value 3 Value 8
Row 4   Value 4 Value 3
Row 5   Value 5 Value 5
Row 6   Value 6 Value 8
Row 7   Value 7 Value 3
Row 8   Value 8 Value 5
Row 9   Value 9 Value 8

and you want result in sheet2

A       B
Row 3   Value 3
Row 5   Value 5
Row 8   Value 8

Try the following code

Sub Macro1()

    Dim Counter As Long

    Dim lastRow As Long
    lastRow = 10
    Dim arrColF(10) As String

    Sheets("Sheet1").Select

    For Counter = 1 To lastRow Step 1
        arrColF(Counter - 1) = Sheet1.Cells(Counter, 3).Value
    Next Counter

    arrStr = Join(arrColF, ", ")
    Dim strRange As String

    For Counter = 1 To lastRow Step 1
        If InStr(1, arrStr, Sheet1.Cells(Counter, 2).Value & ", ") > 0 Then
            strRange = strRange & "A" & Counter & ":B" & Counter
            If Trim(strRange) <> "" Then strRange = strRange & ","
        End If
    Next Counter
    strRange = Left(strRange, Len(strRange) - 1)

    Sheet1.Range(strRange).Select
    Selection.Copy
    Sheets("Sheet2").Select
    Range("A1").Select
    ActiveSheet.Paste

End Sub

这篇关于Excel比较两列，将匹配行复制到新工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！