Перекрестное сравнение данных между двумя рабочими книгами

Question

Я пытаюсь сравнить 2 данных в 2 разных книгах. Затем мне нужно найти недостающие данные между двумя и составить отдельный новый, обновленный список. Тем не менее, мне нужно обновить данные, только если есть запись из первого списка. Если нет, то нет необходимости обновлять эти данные.

НАПРИМЕР

Workbook 1
ID            Name      Code
...
futa-123456   Max       0a
futa-123458   Max       0a   
futb-ms8793   John      SX
futg-809153   May       gH
...

Workbook 2
ID            Name      Contact Address Code Flag
...
futa-123456   Max       ...       ...    0a   Y
futa-123457   Max       ...       ...    0a   Y
futb-XY5543   Sam       ...       ...    SX   N
futg-809153   May       ...       ...    gH   Y
futg-809154   May       ...       ...    gH   Y
... 

Final Result
Separate new sheet (doesn't natter which workbook)
ID            Name      Code
...
futa-123456   Max       0a
futa-123457   Max       0a* 
futa-123458   Max       0a   
futb-ms8793   John      SX
futg-809153   May       gH
futg-809154   May       gH*
...

Таким образом, Excel покажет различия с обеих сторон рабочей книги для Макса, потому что есть различия между обеими сторонами, и они присутствуют в рабочей книге 1. В мае различия будут отображаться только в рабочей тетради 2, поскольку различия есть только в рабочей тетради 2 (но все равно будет отображаться, поскольку в рабочей тетради 1 присутствует май). Сэм не будет выделен, потому что в книге 1 нет Сэма.

По сути, я сравниваю с Workbook 1 в качестве источника. Если в рабочей книге 1 нет записи, но есть в рабочей книге 2, она не будет выделена.

Есть ли способ сделать это?

score 0 · Accepted Answer · 2011-03-09T19:38:42

Шаги, необходимые для этого вручную, довольно длинные и утомительные.

Предполагая, что данные в Рабочей книге 1 находятся на Sheet1 выполните следующие действия:

Скопируйте данные (включая заголовки) из Рабочей книги 2
Вставьте в ячейку A1 пустого листа в Workbook 1 (скажем, Sheet2)
Введите эту формулу в G1 → =MATCH(B1,Sheet1!B:B,0)
Скопируйте или вставьте формулу вниз соответствующим образом. Я предпочитаю эту последовательность нажатий клавиш:
- Оставил
- Ctrl+Вниз
- Правильно
- Ctrl+Shift+Up
- Ctrl+D
Включить фильтрацию для столбца (Ctrl+Shift+L)
Фильтр только для #N/A (Если их нет, игнорируйте этот шаг и следующий.)
Удалить все строки #N/A
Удалить столбцы C , D , F , G
Выделить данные
Скопируйте данные (исключая заголовки) из Sheet1 1 Рабочей книги 1 (текущей рабочей книги)
Щелкните правой кнопкой мыши в ячейке A2 Sheet2 и выберите « Insert Copied Cells
Убедитесь, что выбран Shift cells down , нажмите OK
Выделите все данные и удалите дубликаты с помощью « Data → « Data Tools → « Remove Duplicates . (Убедитесь, что отмечен только ID столбца.)
Сортировать по столбцу ID

Лучшее решение, конечно, состоит в том, чтобы автоматизировать эти шаги. Это то, что делает следующий код VBA.

Скопируйте и вставьте этот код в стандартный модуль в Рабочей книге 1:

'============================================================================================
' Module     : a standard module in Workbook 1
' Version    : 0.1.1
' Part       : 1 of 1
' References : N/A
' Source     : https://superuser.com/a/1331855/763880
'============================================================================================
Option Explicit

Public Sub CrossCompareSheets()

  Const s_CompareToWorkbook  As String = "Workbook 2.xlsx"
  Const s_CompareToSheet     As String = "Sheet1"
  Const s_CompareToTopLeft   As String = "A1"
  Const s_CompareToExtraCols As String = "C,D,F"
  Const s_SourceSheet        As String = "Sheet1"
  Const s_SourceTopLeft      As String = "A1"
  Const n_SourceMatchColumn  As Long = 2
  Const s_ResultSheet        As String = "Sheet2"
  Const s_ResultTopLeft      As String = "A1"
  Const n_ResultMatchColumn  As Long = 2
  Const n_ResultUniqueColumn As Long = 1
  Const n_ResultSortColumn   As Long = 1

  Dim wkstCompareTo  As Worksheet: Set wkstCompareTo = Workbooks(s_CompareToWorkbook).Worksheets(s_CompareToSheet)
  Dim rngCompareTo As Range: Set rngCompareTo = wkstCompareTo.Range(s_CompareToTopLeft).CurrentRegion
  Dim wkstSource  As Worksheet: Set wkstSource = ActiveWorkbook.Worksheets(s_SourceSheet)
  Dim rngSource As Range: Set rngSource = wkstSource.Range(s_SourceTopLeft).CurrentRegion
  Dim wkstResult  As Worksheet: Set wkstResult = ActiveWorkbook.Worksheets(s_ResultSheet)
  Dim rngResult As Range
  Dim celResultTopLeft As Range: Set celResultTopLeft = wkstResult.Range(s_ResultTopLeft)

  wkstResult.UsedRange.Clear
  rngCompareTo.Copy Destination:=wkstResult.Range(s_ResultTopLeft)
  Set rngResult = celResultTopLeft.CurrentRegion
  With rngResult.Resize(ColumnSize:=1).Offset(ColumnOffset:=rngResult.Columns.Count)
    .FormulaR1C1 = Replace(Replace(Replace( _
      "=MATCH(RC{a},{Sheet}!C{b},0)" _
      , "{a}", n_ResultMatchColumn), "{Sheet}", s_SourceSheet), "{b}", n_SourceMatchColumn)
    .Copy
    .PasteSpecial xlPasteValues
  End With
  Set rngResult = celResultTopLeft.CurrentRegion
  rngResult.AutoFilter Field:=rngResult.Columns.Count, Criteria1:="#N/A"
  rngResult.Offset(RowOffset:=1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
  rngResult.AutoFilter
  Dim colsToBeDeleted As Range
  Set colsToBeDeleted = rngResult.Resize(ColumnSize:=1).Offset(ColumnOffset:=rngResult.Columns.Count - 1).EntireColumn
  Dim varColumn As Variant
  For Each varColumn In Split(s_CompareToExtraCols, ",")
    Set colsToBeDeleted = Union(colsToBeDeleted, wkstResult.Range(varColumn & ":" & varColumn))
  Next varColumn
  colsToBeDeleted.Delete
  Set rngResult = celResultTopLeft.CurrentRegion
  rngSource.Offset(RowOffset:=1).Copy Destination:=celResultTopLeft.Offset(RowOffset:=rngResult.Rows.Count)
  Set rngResult = celResultTopLeft.CurrentRegion
  rngResult.RemoveDuplicates Columns:=n_ResultUniqueColumn, Header:=xlYes
  Set rngResult = celResultTopLeft.CurrentRegion
  With wkstResult.Sort
    .SortFields.Clear
    .SortFields.Add Key:=rngResult.Columns(n_ResultSortColumn)
    .SetRange rngResult
    .Header = xlYes
    .Apply
  End With

End Sub

Заметки:

Вы можете изменить константы в верхней части, чтобы обслуживать различные столбцы и имена файлов, и код будет автоматически корректироваться.

score -1 · Answer 2 · 2011-03-09T19:38:42

РЕДАКТИРОВАТЬ перечитал вопрос, и обновил с моим пониманием проблемы ...

Второе редактирование, где я запутался между листами 1 и 2

Чтобы найти данные, которые нужно перейти на новый лист, потому что он существует только на листе 1 ...

=match(a1, sheet2!a:a,0).

Фильтр для #na.

Чтобы обновить данные на листе 1, добавьте столбец справа от данных, примерно так;

=Iferror(Index(sheet2!d:d,match(a1, sheet2!a:a,0)),D1)

Скопируйте это в запасной столбец на листе 1, затем скопируйте и вставьте как значения исходного столбца D1.

Еще один пионт; если идентификаторы не совпадают между листами (их нельзя использовать для сопоставления), создайте объединенное имя и код «вспомогательный столбец» или что-либо еще, что можно использовать для правильного сравнения двух листов.

(Если идентификаторы не могут быть использованы для сопоставления, я бы также начал задавать много вопросов о данных, но это выходит за рамки вопроса)

score -1 · Answer 3 · 2011-03-09T19:38:42

Я хотел бы предложить самый простой способ сравнить и создать уникальный список.

Следуй этим шагам:

Скопируйте данные из обеих рабочих книг на чистый лист.
Выберите Все данные.
На вкладке «Главная» нажмите «Сортировать и отфильтровать».
Сортировать данные в порядке возрастания по идентификатору.
Снова выберите все данные.
Перейдите на вкладку «Данные» и нажмите «Удалить дубликат».
Выберите столбец ID, чтобы найти и удалить дубликаты.

Вы найдете ваши данные, как показано ниже:

ID           Name      Code
futa-123456  Max        0a
futa-123457  Max        0a
futa-123458  Max        0a
futb-ms8793  John       SX
futb-xy5543  Sam        Sx
futg-890153  May        gH
futg-890154  May        GH

NB

Показанный выше метод подходит для небольшой базы данных.
Вы можете записать весь процесс как макрос, чтобы работать быстрее.

Сейчас выбран русский

Перекрестное сравнение данных между двумя рабочими книгами

3 ответа3

Всё ещё ищете ответ? Посмотрите другие вопросы с метками microsoft-excel worksheet-function comparison highlighting.

Перекрестное сравнение данных между двумя рабочими книгами

3 ответа3

Всё ещё ищете ответ? Посмотрите другие вопросы с метками microsoft-excel worksheet-function comparison highlighting.

Похожие