Страницы: 1
RSS
Сравнение каждой строки одного диапазона с другим
 
По совету из этого топика создаю отдельную тему. Подскажите, как такое сделать? :) Заранее спасибо!

Надо из столбца А взять содержимое первой строки и сравнить со строками столбца B. Совпадения забрать и добавить в столбец C. Потом из столбца А взять содержимое второй строки, сравнить со столбцом B и совпадения добавить в столбец D. И так далее по всем строкам столбца А. Не силен в терминологии, надеюсь объяснил.

 
формула массива:
=ЕСЛИОШИБКА(ИНДЕКС($B$1:$B$114;НАИМЕНЬШИЙ(ЕСЛИ(ЕЧИСЛО(ПОИСК(ИНДЕКС($A$1:$A$143;СТОЛБЕЦ(A1));$B$1:$B$114));СТРОКА($B$1:$B$114));СТРОКА(A1)));"")
вводите в С1 и растягиваете по строкам и столбцам
 
Михаил, заработало! (с) кот Матроскин
Спасибо :)
 
Код
Sub Макрос1()
Dim x, i&
  i = 2
  Rows(1).Insert
  For Each x In Range("A2", Cells(Rows.Count, 1).End(xlUp)).Value
    Columns(2).AutoFilter Field:=1, Criteria1:="=*" & x & "*"
    i = i + 1
    ActiveSheet.AutoFilter.Range.SpecialCells(xlCellTypeVisible).Copy Cells(1, i)
  Next
  Rows(1).Delete
End Sub
 
Спасибо всем, кто откликнулся! :)

Наберусь смелости, если это не будет наглостью))) Помогите еще с одной задачей, нужен такой вариант формулы. Пример, как должно работать ниже (прикрепил файл).



(1): берем содержимое ячейки на листе "Итог" начиная с A2,
(2): находим совпадения на Листе1 в Столбце1
(3)(4): забираем содержимое ячеек строки 8, Cтолбец2 и Столбец3
(5)(6): вставляем на лист "Итог" в B2.

Повторяем тоже самое со всеми строками на листе Итог. Если совпадений не найдено, то остается пустая ячейка.
Возможно, криво объяснил, не силен в терминологии, как уже говорил, но надеюсь понятно. Готов отблагодарить не за спасибо, если что. :)  
Изменено: NiceExcel - 27.11.2018 02:43:43
 
А с Лист2 что делать?
 
С Лист2 тоже самое, что и с Лист1. Берем содержимое ячейки начиная с A2 на листе "Итог", находим совпадения на Листе2 и вставляем на лист "Итог" в C2. То есть, тоже самое, только искать совпадения на листе2. :) Прошу прощения, что не уточнил. По сути, для этого можно будет использовать ту же формулу, что и для Лист1.
Изменено: NiceExcel - 27.11.2018 11:22:10
 
Цитата
NiceExcel написал:
можно будет использовать ту же формулу
Только формулами? Макрос не устроит?
 
Хотелось бы формулой, если это возможно. :)
 
Формулами - это не ко мне )
См. вариант с макросом - может понравится.
 
=ЕСЛИОШИБКА(ВПР($A2;ДВССЫЛ("'"&B$1&"'!A:C");2;0);"")&" | "&ЕСЛИОШИБКА(ВПР($A2;ДВССЫЛ("'"&B$1&"'!A:C");3;0);"")

Формула массива (ФМ) вводится Ctrl+Shift+Enter
Memento mori
 
Спасибо! Только вопрос, как указать конкретный лист с другим именем?)

Цитата
Юрий М написал: Формулами - это не ко мне )См. вариант с макросом - может понравится.
Я пока не дружу с макросами, новичок в сабже, но тем не менее благодарю. :)
 
NiceExcel, написанная формула ссылается на ячейку с именем листа по адресу B$1.

Формула массива (ФМ) вводится Ctrl+Shift+Enter
Memento mori
 
 JayBhagavan, спасибо! Я был просто не внимателен, все работает)

P.S. На мороженое там ушло. :)
 
NiceExcel, ээээ... спасибо, но не стоило.

Формула массива (ФМ) вводится Ctrl+Shift+Enter
Memento mori
 
JayBhagavan, скажите, пожалуйста, как будет выглядеть формула, чтобы обрабатывались все подобные сочетания, как в примере на скриншоте ниже? А условия те же, что и в первом сообщении.
 
ФМ!!!
=ЕСЛИОШИБКА(ИНДЕКС(ДВССЫЛ("'"&B$1&"'!A:C");НАИМЕНЬШИЙ(ЕСЛИОШИБКА(ПОИСКПОЗ(ПСТР(", "&$A2&", ";нач+2;кон-нач-2);ДВССЫЛ("'"&B$1&"'!A:A");0);"");1);2);"")&ЕСЛИ(ЕЧИСЛО(НАИМЕНЬШИЙ(ЕСЛИОШИБКА(ПОИСКПОЗ(ПСТР(", "&$A2&", ";нач+2;кон-нач-2);ДВССЫЛ("'"&B$1&"'!A:A");0);"");1));" | ";"")&ЕСЛИОШИБКА(ИНДЕКС(ДВССЫЛ("'"&B$1&"'!A:C");НАИМЕНЬШИЙ(ЕСЛИОШИБКА(ПОИСКПОЗ(ПСТР(", "&$A2&", ";нач+2;кон-нач-2);ДВССЫЛ("'"&B$1&"'!A:A");0);"");1);3);"")
Именованные функции (см. диспетчер имён) в формуле:
- нач:
=ПОИСК("| ";ПОДСТАВИТЬ(", "&Итог!$A2&", ";", ";"| ";СТРОКА(ДВССЫЛ("1:"&((ДЛСТР(Итог!$A2)-ДЛСТР(ПОДСТАВИТЬ(Итог!$A2;", ";)))/2+1)))))
- кон:
=ПОИСК("| ";ПОДСТАВИТЬ(", "&Итог!$A2&", ";", ";"| ";СТРОКА(ДВССЫЛ("1:"&((ДЛСТР(Итог!$A2)-ДЛСТР(ПОДСТАВИТЬ(Итог!$A2;", ";)))/2+1)))+1))
Для большей внешней эстетичности результата можно вывести каждое число в отдельную ячейку (см. лист "Итог (2)"), либо придётся усложнить формулу.

Формула массива (ФМ) вводится Ctrl+Shift+Enter
Memento mori
 
JayBhagavan, спасибо! Попробую разобраться, ибо в прикрепленном Вами файле все работает, а в новом пока как-то не очень)), пробую найти причину, хотя все данные внес и скопировал из Вашего файла.  
 
NiceExcel, обратите внимание, что формула привязана к разделителю ", " (запятая и пробел) и это формула массива (см. подпись).

Формула массива (ФМ) вводится Ctrl+Shift+Enter
Memento mori
 
Уже разобрался, все работает, ФМ решает)) Спасибо.  
 
Подскажите, пожалуйста, как сделать, чтобы учитывался такой вариант при сравнениях? Прикрепил файл для примера.

 
Будет работать только для слов длинной 6 символов, т.е.
для

слова иной длины будут игнорироваться на предмет добавления дефиса:
ФМ
=ЕСЛИОШИБКА(ИНДЕКС(ДВССЫЛ("'"&ИНДЕКС($1:$1;0;СТОЛБЕЦ($A:$A)+1+ОТБР((СТОЛБЕЦ()-СТОЛБЕЦ($A:$A)-1)/2;0)*2)&"'!A:C");НАИМЕНЬШИЙ(ЕСЛИОШИБКА(ПОИСКПОЗ(искомое;ДВССЫЛ("'"&ИНДЕКС($1:$1;0;СТОЛБЕЦ($A:$A)+1+ОТБР((СТОЛБЕЦ()-СТОЛБЕЦ($A:$A)-1)/2;0)*2)&"'!A:A");0);ЕСЛИ(ДЛСТР(искомое)=6;ЕСЛИОШИБКА(ПОИСКПОЗ(ЛЕВБ(искомое;3)&"-"&ПРАВБ(искомое;3);ДВССЫЛ("'"&ИНДЕКС($1:$1;0;СТОЛБЕЦ($A:$A)+1+ОТБР((СТОЛБЕЦ()-СТОЛБЕЦ($A:$A)-1)/2;0)*2)&"'!A:A");0);"");""));1);ОСТАТ(СТОЛБЕЦ()-СТОЛБЕЦ($A:$A)-1;2)+2)&"";"")
Добавил именованную функцию:
искомое
=ПСТР(", "&'Итог (3)'!$A2&", ";'Итог (3)'!нач+2;'Итог (3)'!кон-'Итог (3)'!нач-2)
дабы немного сократить длину формулы и повысить её читабельность.
Добавил от себя немного универсальности

Формула массива (ФМ) вводится Ctrl+Shift+Enter
Memento mori
 
JayBhagavan, прям то, что нужно! Спасибо огромное. :) Буду разбираться.
 
У меня остались еще попытки "помощь зала" или "звонок другу"?) Не могу сделать такую штуку (см. скриншот + прикрепил файлик):

1) Берем содержимое "А", ищем совпадения в "B" и получаем "С".



P.S. Стыдоба спрашивать такие элементарные вещи, как покажется присутствующим, но се ля ви.
 
Цитата
NiceExcel написал: ищем совпадения
Что Вы подразумеваете под этой фразой? Приведите, 2-3 примера что с чем, по-Вашему, совпадает.

Формула массива (ФМ) вводится Ctrl+Shift+Enter
Memento mori
 
Цитата
JayBhagavan написал:
Что Вы подразумеваете под этой фразой? Приведите, 2-3 примера что с чем, по-Вашему, совпадает.
Из меня объясняльщик никакущий, ибо терминологии всей не знаю, простите. Сейчас попробую объяснить, если что, сильно не пинайте)

В столбце "А" есть аббревиатуры. В столбце "B" есть список аббревиатур склеенных воедино, часть которых совпадает с аббревиатурами из столбца "А". Допустим, в двух склеенных аббревиатурах ETHBTC (B1) присутствуют две отдельные аббревиатуры ETH и BTC (которые есть в столбце "A"). Надо найти такие частичные совпадения аббревиатур в столбце "B" и разделить дефисом, поместив в столбец "C".

 
Искомое и где ищем разнёс на разные листы. ИМХО, так правильно.
Искомое желательно отсортировать в порядке убывания по длине текста. (см. файл)
ФМ:
=ЕСЛИ(ПОИСК(найденное;$B1)=1;найденное&"-"&ПОДСТАВИТЬ($B1;найденное;;1);ПОДСТАВИТЬ($B1;найденное;;1)&"-"&найденное)
где "найденное" (Ctrl+F3):
=ИНДЕКС(ЧТО_ИЩЕМ!$A:$A;НАИМЕНЬШИЙ(ЕСЛИОШИБКА(СТРОКА(ЧТО_ИЩЕМ!$A$1:$A$7)/(ПОИСК(ЧТО_ИЩЕМ!$A$1:$A$7;ГДЕ_ИЩЕМ!$B1)^0);"");1))

Мне кажется, или тема превращается в свалку разных задач? Модераторы, что скажете?

Формула массива (ФМ) вводится Ctrl+Shift+Enter
Memento mori
 
JayBhagavan, увидел Ваш пример и понял свою ошибку, почему не получалось у самого) Спасибо большое!
Страницы: 1
Наверх