Вы не упомянули версию Excel, которую вы используете. Я предлагаю возможное решение, основанное на моем понимании вашего вопроса. Однако он также использует функции с именем IFERROR & COUNTBLANK, которые, как я полагаю, недоступны до Excel 2007. Поэтому, если у вас Excel 2003, может потребоваться другой подход.
Это может быть не очень идеальным решением, так как в нем используются вспомогательные столбцы, а также вспомогательный лист!
В этом примере пример данных находится на листе 1!A2:B10, как видно на этом скриншоте.
Сначала нам нужно создать вспомогательный столбец в столбце D, чтобы получить уникальный список из ваших идентификаторов.
В D2 поместите следующую формулу, а затем нажмите CTRL + SHIFT + ENTER на панели формул, чтобы создать формулу массива. Вы также должны оставить строку (желательно первую строку) в качестве заголовка, чтобы это работало правильно.
Формула должна быть заключена в фигурные скобки, чтобы указать, что это формула массива.
Перетащите его вниз до предполагаемых строк на основе ожидаемого количества уникальных значений в главном столбце. В конце концов он начнет возвращать пробелы внизу, затем вы можете остановиться. Это создает уникальный основной список идентификаторов.
=IFERROR(INDEX($A$2:$A$10, MATCH(0,COUNTIF($D$1:D1, $A$2:$A$10), 0)),"")
Теперь нам нужно знать точное начало и конец этого списка, чтобы заполнить список проверки.
Теперь в G3 ставим следующую формулу.
=INDEX(Sheet1!D2:D10,1)
и в G4 положить следующую формулу массива.
=MIN(IF(Sheet1!D2:D10="",ROW(Sheet1!D2:D10),9^99))-2
Не забудьте CTRL + SHIFT + ENTER для этого.
Теперь перейдите в диспетчер имен и создайте новое имя с именем MyList.
Поместите в него следующую формулу
=INDEX(Sheet1!$D$2:$D$10,Sheet1!$G$3):INDEX(Sheet1!$D$2:$D$10,Sheet1!$G$4)
Здесь мы используем INDEX, чтобы вернуть ссылку на ячейку, а не значение
Вставьте новый лист с именем Sheet2.
Теперь в столбце A2 и ниже создайте список проверки и поместите в него =MyList .
Теперь все уникальные значения в первом раскрывающемся списке для идентификаторов.
Это завершает часть 1.
Теперь следующая задача - заполнить второй список проверки на основе данных, извлеченных из столбца описания.
Вставьте третий вспомогательный лист с именем Sheet3. Вы можете просто спрятать этот лист.
В этом примере я взял данные в Sheet3!В2:H10. Определите количество столбцов на основе максимального количества дубликатов в ваших идентификаторах из основного списка. Также оставьте первый столбец A для правильной работы.
В B2 поместите следующую формулу и нажмите CTRL + SHIFT + ENTER на панели формул, чтобы создать формулу и массив.
Перетащите его вниз и вниз, как предполагалось.
=IFERROR(INDEX(Sheet1!$B$2:$B$10, SMALL(IF(Sheet2!$A2=Sheet1!$A$2:$A$10,ROW(Sheet1!$A$2:$A$10)-1,999),COLUMN()-COLUMN($A$2))),"")
Вернувшись в диспетчер имен, создайте новое имя с именем Trimmed и поместите в него следующую формулу.
=OFFSET(Sheet3!$B1,0,0):OFFSET(Sheet3!$B1,0,COUNTA(Sheet3!$B1:$H1)-COUNTBLANK(Sheet3!$B1:$H1)-1)
Это генерирует список, исключая пробелы, так что список корректно обрезается.
Теперь в Sheet2 для ячеек B2 и ниже создайте список проверки и поместите в него =Trimmed .
Теперь, когда ваши данные изменяются в Sheet1!A2:A10 список уникальных значений автоматически отражается в MyList, а затем имя Trimmed извлекает только предполагаемое описание из столбца описания Sheet1.
См. Ниже экран в GIF, чтобы почувствовать, как все это работает.
