Excel, если ячейка из двух разных листов соответствует строке удаления

Question

У меня есть два разных листа, запрос и форма, в одной книге. Столбцы b на обоих листах содержат данные, которые я хочу сравнить. Если столбец b на formonth совпадает с любой ячейкой в столбце b на листе запросов, я хочу удалить всю строку на листе запросов. Я пробовал так много разных идей без успеха.

Answer 1

Это сложный вопрос, и, скорее всего, это будет решение VBA.

Этот код должен работать (он определенно не самый красивый и может быть довольно неуклюжим в зависимости от размера ваших наборов данных), но, надеюсь, кто-то может предоставить вам что-то более гладкое в будущем.

Sub DeleteRows()

Dim QueryRange As Range
Dim FormonthRange As Range
Dim y As Integer

Application.ScreenUpdating = False

y = Worksheets("formonth").UsedRange.Rows.Count

For Each QueryRange In Worksheets("query").Range("B1:" & "B" & y)
'This is the range of cells to check

    For Each FormonthRange In Worksheets("formonth").Range("B1:" & "B" & y)
    'This is the range of cells to compare

        If QueryRange.Value = FormonthRange.Value Then
            QueryRange.Offset(, 1).Value = "@"
            Exit For
            End If

        Next FormonthRange

    Next QueryRange

Worksheets("query").Columns("C").SpecialCells(xlConstants, 2).EntireRow.Delete

Application.ScreenUpdating = True

End Sub

Этот код устанавливает столбец (в данном случае column C) как символ @ если он обнаруживает совпадение между вашей вкладкой « Query » и вкладкой « Formonth ». Затем он удаляет строки на основе столбца, который имеет символ.

Надеюсь, способ написания должен позволить вам копировать и вставлять. Единственное, что вам нужно изменить, - это столбец, в который он помещает символ @ потому что он помещает символ как смещение к столбцу B. Это следующие строки (отключите EnterTheNumberYouWantHere и EnterColumnLetterHere)

QueryRange.Offset(, EnterTheNumberYouWantHere).Value = "@"

а также

Worksheets("query").Columns("EnterColumnLetterHere").SpecialCells(xlConstants, 2).EntireRow.Delete

Где несколько типичных совпадающих значений следующие:

Если смещение = 1 столбец = C (как показано в коде выше)
Если смещение = 2 столбца = D
Если смещение = 10 столбец = L
Если смещение = 15 столбец = Q
И т.п.

В основном выберите столбец, который всегда будет пустым в вашей электронной таблице, и работайте оттуда.

Answer 2

Вы можете использовать бесплатный Power Power Query от Microsoft Excel (из Excel 2010), чтобы выбрать записи, которые не совпадают в листе запросов. То, что вы пытаетесь получить, это сделать анти-соединение между обоими листами

1. Определите оба источника данных в виде таблицы и дайте им имя запроса и форму.

2. Загрузите справочную таблицу в Power Query (лента Power Query> из таблицы)
   В расширенном редакторе вы получите код, похожий на этот:

   let
       Source = Excel.CurrentWorkbook(){[Name="formonth"]}[Content]
   in
       Source
   

   Перейдите в раздел Домашняя страница -> Закрыть и загрузить -> Закрыть и загрузить в ... -> Только создать соединение

3. Затем вы объединяете обе таблицы, используя левое анти-объединение. Это хорошо описано в блоге Excelguru соответственно на веб-сайте поддержки MS

   Загрузите запрос таблицы в Power Query и выполните левую защиту от объединения (ниже кода M).

   let
       Source = Excel.CurrentWorkbook(){[Name="query"]}[Content],
       #"Merge source (anti-join)" = Table.NestedJoin(Source,
                   {"B"},formonth,{"B"},"NewColumn",JoinKind.LeftAnti),
       #"Remove columns" = Table.RemoveColumns(#"Merge source (anti-join)",{"NewColumn"})
   in
       #"Remove columns"
   

   Удалить новый столбец из операции слияния
   Перейти на главную -> Закрыть и загрузить -> Закрыть и загрузить

Вот как это выглядит между источниками данных и выводом Power Query: