Соединение двух столбцов для создания списка данных

Question

В настоящее время у меня есть два столбца, которые необходимо сравнить. Col A, Лист 1 и Col A, Лист 2.

Лист 1 содержит:

A      B       C
5000   Apples  WI
6182   Oranges NY
7271   Grapes  MN
2293   Peanuts FL

Лист 2 содержит:

A
4032
5233
7271
2293

Должен получать результаты, как ....

7271   Grapes  MN
2293   Peanuts FL

Мне нужно только отображать результаты, которые содержат тот же номер, что и лист 2. Есть ли лучший способ загрузки Листа 2 в массив и сравнения его с каждой ячейкой Листа 1?

    'For i = 1 to Sheet1LastRow

    Sheet2Row = Range("A" & Rows.Count).End(xlUp).Row
    Sheet2Array = Range("A2:A" & Sheet2LastRow).Value
            For i = LBound(Sheet2Array, 1) To UBound(Sheet2Array, 1)

    'if cell = Sheet2Array(i, 1)
     '....
    'End if

    Next i

   'Next Cell sheet 1

Answer 1

Это основная функция =VLOOKUP() .

Синтаксис:

=VLOOKUP(
         compare this cell, 
         to the cells in the leftmost column of this range,
         returning the corresponding value from this column index,
         true/false for range lookup (just leave this false if you aren't sure)
 )

На листе 2 в ячейке B1 вы должны разместить =VLOOKUP(A1,Sheet1!A:B,2,False) а в C1 вы бы =VLOOKUP(A1,Sheet1!A:C,3,False)

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

Продолжая это, вы, вероятно, получите ошибки #N/A для значений, которых нет в исходной таблице. Оберните формулу в =IFERROR( your vlookup() function , "" ) чтобы заменить любые ошибки пустой ячейкой.

Answer 2

Из ваших вопросов и примеров данных видно, что в качестве выходных данных вы ищете столбец с совпадающими данными в непрерывном блоке ячеек без пробелов или ошибок между ними.

Если вы хотите использовать формульный подход, то этого можно достичь с помощью слегка сложных функций INDEX & MATCH в формуле массива. Как только у вас есть совпадающие данные в смежных ячейках в столбце, просто примените VLOOKUP, чтобы получить остальные два столбца из вашей основной таблицы в Sheet1.

Вот как. Ниже приведены два скриншота Sheet1 и Sheet2.

Sheet1 имеет вашу основную таблицу в столбцах A, B и C, Sheet2 содержит ваш список для сопоставления со столбцом A из Sheet1

Теперь в Листе 2 в ячейке С1 поместите следующую формулу массива

{=INDEX(Sheet1!$A$1:$A$4,SMALL(IF(ISERROR(MATCH(Sheet2!$A$1:$A$4,Sheet1!$A$1:$A$4,0)),"",MATCH(Sheet2!$A$1:$A$4,Sheet1!$A$1:$A$4,0)),ROW(1:1)))}

Поместите эту формулу без фигурных скобок и в панели формул нажмите CTRL+SHIFT+ENTER, чтобы создать формулу массива, и перетаскивайте ее вниз, пока не получите ошибку в ячейках или длине вашего списка для сопоставления. Теперь все строки выше ячеек с ошибками имеют совпадающие ячейки из столбца A Sheet1.

Просто очистите ячейки с ошибками, и у вас появится список в престижном блоке ячеек.

Теперь в D1 введите относительно простую формулу VLOOKUP, чтобы получить следующий соответствующий столбец из Sheet1

=VLOOKUP(C1,Sheet1!$A$1:$C$4,2,FALSE)

А в Е1 положить формулу

=VLOOKUP(C1,Sheet1!$A$1:$C$4,3,FALSE)

и перетащите оба вниз. У вас есть список по желанию.

Вы можете использовать встроенную в Excel опцию Evaluate Formula, чтобы оценить комбинацию INDEX MATCH, чтобы понять, как это работает. Он создает массив совпадающих номеров строк и выбирает первое, затем второе, а затем третье наименьшее число при перетаскивании формулы.

Здесь есть два ограничения: данные должны возвращаться в том порядке, в котором они существуют в основной таблице, а не в том, как они находятся в таблице «для сопоставления», и в случае, если у вас более 1 совпадающих ячеек, должна быть возвращена первая.