Если данные в столбце A похожи в двух файлах Excel, сохраните похожие данные и удалите все остальные данные во 2-м файле.

Question

У меня есть родительский файл Excel с постоянно меняющимся диапазоном расположения точек, введенным вручную в столбец А. Для этого примера, скажем, 11.

Point
    P1
    P13
    P20
    P5
    P17
    P8
    P10
    P11
    P3
    P7
    P4

У меня есть 2-й файл Excel с другим навсегда изменяющимся диапазоном точечных местоположений и столбцов для каждого местоположения. Этот файл включает в себя точки из 1-го файла с другими данными в (x) количестве столбцов.

   Point Data1 Data2 Data3 Data4 Data5
   P1       a      b     c     d     e
   P2       a      b     c     d     e
   P3       a      b     c     d     e
   P4       a      b     c     d     e
   P5       a      b     c     d     e
   P6       a      b     c     d     e
   P7       a      b     c     d     e
   P8       a      b     c     d     e
   P9       a      b     c     d     e
   P10      a      b     c     d     e
   P11      a      b     c     d     e
   P12      a      b     c     d     e
   P13      a      b     c     d     e
   P14      a      b     c     d     e
   P15      a      b     c     d     e
   P16      a      b     c     d     e
   P17      a      b     c     d     e
   P18      a      b     c     d     e
   P19      a      b     c     d     e
   P20      a      b     c     d     e

Я хотел бы, чтобы кнопка в первом файле очищала второй файл от всех точек, которые не существуют в первом файле, при сохранении заголовка в 1-й строке. Вывод будет выглядеть так:

Point Data1 Data2 Data3 Data4 Data5
P1       a      b     c     d     e
P3       a      b     c     d     e
P4       a      b     c     d     e
P5       a      b     c     d     e
P7       a      b     c     d     e
P8       a      b     c     d     e
P10      a      b     c     d     e
P11      a      b     c     d     e
P13      a      b     c     d     e
P17      a      b     c     d     e
P20      a      b     c     d     e

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

Может быть проще извлечь каждую точку и соответствующую строку и сохранить ее на новом листе, чем удалять все остальные точки и данные. Это также подойдет. Я знаю, как сделать это вручную с помощью VLOOKUP, но я хотел бы автоматизировать его в VBA для других пользователей.

Спасибо.

Answer 1

Вы можете попробовать это:

Sub mysub()
    Dim parentWb As Workbook, secondWb As Workbook

    Set parentWb = Workbooks.Open("parentWbPath\parentWbName.xlsx") ' adjust 'parent' workbook path and name to suit your needs
    Set secondWb = Workbooks.Open("secondWbPath\secondWbName.xlsx")' adjust 'secondWB' workbook path and name to suit your needs

    Dim filters As Variant
    With parentWb.Worksheets(1) 'assuming 'parent' workbook data are in its 1st worksheet
        filters = Application.Transpose(.Range("A2", .Cells(.Rows.Count, 1).End(xlUp)).Value)
    End With

    Dim iRow As Long        
    With secondWb.Worksheets(1) 'assuming 'secodnWb' workbook data to be processed are in its 1st worksheet
        With .Range("A1", .Cells(.Rows.Count, 1).End(xlUp))
            .AutoFilter Field:=1, Criteria1:=filters, Operator:=xlFilterValues
            For iRow = .Rows(.Rows.Count).Row To 2 Step -1
                .Rows(iRow).Hidden = Not .Rows(iRow).Hidden
            Next
            .Resize(.Rows.Count - 1).Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        End With
        .AutoFilterMode = False
    End With
End Sub

Answer 2

Вы можете использовать Power Query. Это надстройка для Excel 2010+ (по умолчанию в 2016 году известная как Get & Transform), которую вы можете легко загрузить. Там вы можете напрямую подключить свои источники данных, и у вас будет много инструментов для преобразования ваших данных.

В вашем случае вы можете преобразовать первую таблицу как List и использовать его в качестве фильтра для ваших данных.

В редакторе запросов > Главная > Расширенный редактор вы можете вставить этот код (неофициально известный как М-код)

let
    Source = Excel.CurrentWorkbook(){[Name="Main"]}[Content], //Main Table
    Source2 = Excel.CurrentWorkbook(){[Name="Filter"]}[Content], //Filter Table
    Filter = Source2[Point], //Covert Filter table in a List
    #"Filtered Rows" = Table.SelectRows(Source, each List.Contains(Filter, [Point])) //Use the list as a filter
in
    #"Filtered Rows"

или выполните следующие действия:

Даже вы можете отключить ваши данные для последующего анализа: