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

У меня есть лист с тремя вкладками. Клиент сталкивается с «Формой», «Списком» для вставки в CRM извлеченной информации и «Данными» для размещения любых необходимых промежуточных данных.

Соответствующая информация, извлеченная из CRM, представляет собой два столбца: столбец A — «Регионы» и столбец C — «Имена элементов».

Я хочу использовать код VBA, чтобы взять информацию из «Списка» и отобразить ее в «Форме» с именем региона, а затем в том же столбце перечислить элементы для этого региона. Оставьте место и продолжайте.

Пример:

Airport
Toy Robot
Uno Card Game

Commercial
Dinosaur Figures
Glass Marbles
Kids Makeup Kit
Lego Bricks
Nerf Gun
PlayDoh Can
PlayDoh Toolkit

Residential
Deck Of Cards
Dino Egg
Dinosaur Figures
Glass Marbles
Kids Makeup Kit
Lego Bricks
Mini Ping Pong
Monopoly

Downtown
Lego Bricks
Nerf Gun
PlayDoh Can

Я использовал код VBA, чтобы определить, сколько элементов указано для каждого региона, и сохранить их на вкладке «Данные». В столбце A указан регион, а в столбце B указано количество товаров, перечисленных для этого региона.

Для образца: Аэропорт 2 Коммерческий 7 Жилой 8 Центр города 3

Далее у меня есть код, который настраивает имена регионов, оставляя место для элементов.

Аэропорт

Коммерческий

Жилой

Центр города

Теперь я пытаюсь выяснить, как добавить сопровождающие имена элементов в каждом регионе на вкладке «Форма».

Я пробовал использовать Index Match с циклом, но он не выполняет цикл нужное количество раз и не принимает во внимание пустые строки.

Преобразование HTML-таблицы в профессиональный документ Excel
Преобразование HTML-таблицы в профессиональный документ Excel
Это самый простой способ создания Excel из HTML-таблицы.
Импорт excel в laravel в базу данных
Импорт excel в laravel в базу данных
Здравствуйте, дорогой читатель, в этой статье я расскажу практическим и быстрым способом, как импортировать файл Excel в вашу базу данных с помощью...
0
0
61
2
Перейти к ответу Данный вопрос помечен как решенный

Ответы 2

(Disclaimer: depending on your version of Excel, not all of these functions may be available.)


Let's break this down into steps: first, you want a unique list of the Regions. Then, for each Region, you want to get the list of Items. Finally, you want to Stack all of those up.

Итак, для первого шага мы можем использовать UNIQUE и FILTER:

=UNIQUE(FILTER(List!$A:$A, --(List!$A:$A<>"Region")*--(List!$A:$A<>"")))

(Обратите внимание, что FILTER предназначен для удаления заголовка и пустых ячеек. Вы можете изменить "Region" на List!$A$1, если хотите, чтобы все было более «динамично»).

Теперь нам нужно будет действовать с каждой записью в этом массиве/списке, поэтому давайте воспользуемся REDUCE и LAMBDA:

=REDUCE("", UNIQUE(FILTER(List!$A:$A, --(List!$A:$A<>"Region")*--(List!$A:$A<>""))), LAMBDA(arr, val, [SOME FUNCTION]))

Это возьмет значение arr (которое начинается с "") и применит к нему <SOME FUNCTION> для каждого значения (val) в массиве. Итак, анализируя наши шаги: «Во-первых, вам нужен уникальный список регионов. Затем для каждого региона вы хотите получить список элементов. Наконец, вы хотите сложить все это вместе». До сих пор мы все сделали жирным шрифтом: давайте «получим список элементов» для val в качестве нашего следующего шага:

UNIQUE(FILTER(List!$C:$C, List!$A:$A=val))

Наконец, мы хотим сложить все вертикально, с пустой строкой между ними. Для этого мы можем использовать VSTACK:

VSTACK(arr, val, [LIST OF ITEMS], "")

Разберем это: мы хотим взять наш arr на данный момент, добавить val (Регион) в качестве заголовка, затем наш Список элементов, который мы разработали мгновение назад, и закончить пустой ячейкой (""). Итак, сложим все это воедино:

=REDUCE("",UNIQUE(FILTER(List!$A:$A, --(List!$A:$A<>"Region")*--(List!$A:$A<>""))), LAMBDA(arr, val, VSTACK(arr, val, UNIQUE(FILTER(List!$C:$C, List!$A:$A=val)), "")))

Теперь, если вы запустите это, вы можете заметить, что оно включает пустую строку вверху (потому что arr начинается как "") и пустую строку внизу (потому что мы добавляем пустую строку после каждого списка элементов). Если вы хотите избавиться от них, вы можете использовать функцию DROP: положительное число удаляет указанное количество строк/столбцов из верхнего/левого края массива, а отрицательное число удаляет их из нижнего/справа. Мы хотим удалить по одному, поэтому нам нужно DROP дважды:

=DROP(DROP(REDUCE("",UNIQUE(FILTER(List!$A:$A, --(List!$A:$A<>"Region")*--(List!$A:$A<>""))), LAMBDA(arr,val, VSTACK(arr, val, UNIQUE(FILTER(List!$C:$C, List!$A:$A=val)), ""))), 1), -1)
Ответ принят как подходящий

Этот код работает в моем примере файла, если я запускаю его на листе «Форма». Ввод данных в лист «Форма» начинается с ячейки A10, и при запуске макроса на листе «Форма» данные отсутствуют. Как и в вашем примере, данные сортируются на листе «Список». Код также работает, если для региона имеется только одна запись.

Sub regions()

Dim i, j, l, m As Long

Range("A:A").Clear

j = 10

For i = 2 To Worksheets("List").Range("A" & Rows.Count).End(xlUp).Row

 l = Application.WorksheetFunction.CountIf _
 (Worksheets("List").Range("A:A"), Worksheets("List").Cells(i, 1))


  If l = 1 Then
       Cells(j, 1).Value = Worksheets("List").Cells(i, 1).Value
       Cells(j + 1, 1).Value = Worksheets("List").Cells(i, 3).Value
    j = j + l + 2
 Else



  If Worksheets("List").Cells(i, 1).Value = _
  Worksheets("List").Cells(i + 1, 1).Value And _
Application.WorksheetFunction.CountIf(Range("A:A"), _
Worksheets("List").Cells(i, 1)) = 0 Then




    Cells(j, 1).Value = Worksheets("List").Cells(i, 1).Value
    
    For m = 1 To l
    Cells(j + m, 1).Value = Worksheets("List").Cells(i + m - 1, 3).Value
    Next m
    
    j = j + l + 2
 

Else

End If

End If

Next i

End Sub

Другие вопросы по теме