Excel VBA - поиск нескольких значений в двух листах Excel

Question

У меня есть два листа Excel, и мне нужно скопировать значения из одного в другой.

Лист 1: Содержит идентификаторы в одном столбце и Данные в другом, которые я хочу получить на Листе 2 позже.

Лист 2: Содержит те же идентификаторы и должен быть дополнен соответствующими значениями данных из Листа 1.

Как найти соответствующие идентификаторы, а затем скопировать соответствующее значение данных из одного листа Excel в другой?

Answer 1

Вы можете использовать =exact() или

Сравните два листа в одной книге:

1. Включите книгу, в которой вы хотите сравнить два листа, а затем нажмите

Вид> Новое окно

, 2. Затем перейдите на панель задач, чтобы отобразить новое окно текущей рабочей книги.

3. Перейдите к двум листам, которые вы хотите сравнить, из каждого окна и расположите их рядом

4. Теперь сравните два листа, как вам нужно.

Чтобы узнать разницу

1. Откройте книгу, содержащую листы, которые вы хотите сравнить, и создайте новый лист.

2. На новом листе выберите пустую ячейку, например, A1, и введите эту формулу

=IF(Sheet1!A1<> Sheet7!A1, "Sheet1:"&Sheet1!A1&" vs
    Sheet7:"&Sheet7!A1, "")

, Sheet1 и Sheet7 - это листы, с которыми вы хотите сравнить, а A1 - первая ячейка, которую вы хотите сравнить.

3. Затем перетащите маркер автозаполнения на диапазон, который нужно сравнить между двумя листами.

Сравните два листа в двух разных книгах

Если два листа вы хотите сравнить в двух разных книгах, вы можете применить утилиту View Side by Side для их обработки.

1. Откройте два листа, между которыми вы хотите сравнить, и

activate one
    sheet and click View > View Side by Side.

2. Затем два листа в двух рабочих книгах были показаны горизонтально.И тогда вы можете сравнить два листа, как вам нужно.

Answer 2

@Bandersnatch - самое простое решение

Для этих данных на листе 1:

,

Формула для Sheet2, столбец B будет иметь вид =VLOOKUP(A2, Sheet1!A$1:B$6, 2)

---

В VBA:

---

Option Explicit

Public Sub FindValsInWS1Vlookup()

    With Sheet2.Range("B2")

        .FormulaR1C1 = "=VLOOKUP(RC[-1], Sheet1!R1C[-1]:R6C, 2)"

        .AutoFill Destination:=Range("B2:B6")

    End With

End Sub

---

Другой вариант (только VBA, с объектом словаря) - на листе 3:

---

Option Explicit

'Add ref in VBA window: Tools -> References... -> Microsoft Scripting Runtime

Public Sub FindValsInWS1Arrays()

    Dim arr1 As Variant, arr21 As Variant, arr22 As Variant
    Dim i As Long, j As Long, d As Dictionary

    arr1 = Sheet1.UsedRange
    arr21 = Sheet3.UsedRange.Columns(1)
    arr22 = Sheet3.UsedRange.Columns(2)
    Set d = New Dictionary

    For i = LBound(arr1) To UBound(arr1)
        d(arr1(i, 1)) = arr1(i, 2)      'read the 2 columns from ws1 into a dictionary
    Next

    For i = LBound(arr21) To UBound(arr21)
        If d.Exists(arr21(i, 1)) Then arr22(i, 1) = d(arr21(i, 1))
    Next

    Sheet3.UsedRange.Columns(2) = arr22

End Sub

---

Результат:

Примечание: идентификаторы не могут быть дублированы для этих решений

Answer 3

Я хотел бы предложить вам два метода. Первый - это не VBA Solution, а другой - VBA.

Способ 1:

Используйте эту формулу массива в ячейке A2 листа 2.

{=IFERROR(INDEX(Sheet1!$A$2:$E$6, SMALL(IF(COUNTIF($G$1, Sheet1!$A$2:$A$6), ROW(Sheet1!$A$2:$E$6)-MIN(ROW(Sheet1!$A$2:$E$6))+1), ROW(A1)), COLUMN(A1)),"")}

NB. Перетащите эту формулу вправо в столбец E, затем вниз. Ячейка G1 на листе 2 имеет код соответствия Q1.

Способ 2:

Sub ExtractDuplicateID()


Dim sht As Worksheet 
Dim newsht As Worksheet 


Set sht = ThisWorkbook.Worksheets("Sheet1")
Set newsht = ThisWorkbook.Worksheets("Sheet2")


Set dat = sht.Range("A1")
Set newdat = newsht.Range("A1")


Dim i, j
i = 1
j = 1


'Copy Header Values from Sheet1

newdat.Offset(0, 0).Value = dat.Offset(0, 0).Value 
newdat.Offset(0, 1).Value = dat.Offset(0, 2).Value 
newdat.Offset(0, 2).Value = dat.Offset(0, 3).Value 
newdat.Offset(0, 3).Value = dat.Offset(0, 4).Value 
newdat.Offset(0, 4).Value = dat.Offset(0, 5).Value 

Do While newdat.Offset(i, 0).Value <> "" Or newdat.Offset(i, 1).Value <> ""

  j = 1     

  Do While dat.Offset(j, 0).Value <> ""

    If (newdat.Offset(i, 0).Value = dat.Offset(j, 4).Value _
    Or newdat.Offset(i, 1).Value = dat.Offset(j, 5).Value) _
    And dat.Offset(j, 6).Value = "Q1" Then

      'Copy Header Values in Sheet2

      newdat.Offset(iRow, 0).Value = dat.Offset(j, 0).Value 
      newdat.Offset(iRow, 1).Value = dat.Offset(j, 2).Value 
      newdat.Offset(iRow, 2).Value = dat.Offset(j, 3).Value 
      newdat.Offset(iRow, 3).Value = dat.Offset(j, 4).Value 
      newdat.Offset(iRow, 4).Value = dat.Offset(j, 5).Value 

      iRow = iRow + 1
    End If
    j = j + 1     
  Loop

  i = i + 1     
Loop
End Sub

Надеюсь, это поможет вам.