Подбор ближайшего значения (текста)

Дата: Четверг, 11.05.2017, 12:53 | Сообщение № 1

Группа: Проверенные

Ранг: Обитатель

Сообщений: 268

Репутация: 4 ±

Замечаний: 0% ±

Excel 2003-2016

Добрый день!

Нужна помощь специалиста VBA.

Настоящая таблица очень большая порядка 8 - 10 тыс. строк на листе "Данные" тоже самое, потому файл обрезан до минимума.

Суть: нужно привести столбец "наименование" к ближайшему подходящему значению с листа "Данные", т.е. заменить значение. Ключом является цифровая часть наименования. Прочитал много тем по замене текста, перепробовал все! Но к сожалению либо печаль, либо куча несоответствий. Потому решил попытать счастья тут. Пробовал обработку найти и заменить, но она не даёт 100% результата. Предположительно думаю должен быть макрос, с использованием библиотек т.к. объём оч внушительный.

Любая помощь, совет, направление

приветствуется.

К сообщению приложен файл: _2.xlsm (72.8 Kb)

Я не Гуру, но стремлюсь!

Сообщение отредактировал ZamoK - Пятница, 12.05.2017, 09:32

wild_pig

Дата: Четверг, 11.05.2017, 16:20 | Сообщение № 2

Группа: Проверенные

Ранг: Обитатель

Сообщений: 518

Репутация: 97 ±

Замечаний: 0% ±

2003, 2013

Хоть бы показали эти самые "соответствия" в файле.

Дата: Пятница, 12.05.2017, 09:37 | Сообщение № 3

Группа: Проверенные

Ранг: Обитатель

Сообщений: 268

Репутация: 4 ±

Замечаний: 0% ±

Excel 2003-2016

Перезалил файлик.
Не знаю какой алгоритм будет лучше, но если бы допустим появлялось какое-либо диалоговое окно в случае отсутствия позиции на листе "Данные", то было бы совсем хорошо, но это по возможности.

Я не Гуру, но стремлюсь!

Дата: Пятница, 12.05.2017, 11:00 | Сообщение № 4

Группа: Друзья

Ранг: Участник клуба

Сообщений: 2566

Репутация: 724 ±

Замечаний: 0% ±

Excel 2010

Добрый день.
Вариант формулой (массива)

Код

=ИНДЕКС(Данные!$A$2:$A$261;МАКС(ЕЧИСЛО(ПОИСК(ПОДСТАВИТЬ(ПОДСТАВИТЬ(Данные!$A$2:$A$261;".";"");" ";"");ПОДСТАВИТЬ(ПОДСТАВИТЬ(B2;".";"");" ";"")))*СТРОКА($A$1:$A$260)))

К сообщению приложен файл: _2-1-.xlsm (75.2 Kb)

Яндекс: 410016850021169

Дата: Пятница, 12.05.2017, 14:17 | Сообщение № 5

Группа: Проверенные

Ранг: Обитатель

Сообщений: 268

Репутация: 4 ±

Замечаний: 0% ±

Excel 2003-2016

sboy, Диапазоны: Данные!$A$2:$A$261 и $A$1:$A$260 не постоянны, поэтому оч сложно

Цитата ZamoK, 11.05.2017 в 12:53, в сообщении № 1 ()

Настоящая таблица очень большая порядка 8 - 10 тыс. строк на листе "Данные" тоже самое, потому файл обрезан до минимума.

Меняются ежедневно.

Я не Гуру, но стремлюсь!

Дата: Пятница, 12.05.2017, 14:51 | Сообщение № 6

Группа: Друзья

Ранг: Участник клуба

Сообщений: 2566

Репутация: 724 ±

Замечаний: 0% ±

Excel 2010

Цитата ZamoK, 12.05.2017 в 14:17, в сообщении № 5 ()

Диапазоныне постоянны, поэтому оч сложно

в диспетчер имен "Данные"

Код

=Данные!$A$1:ИНДЕКС(Данные!$A:$A;СЧЁТЗ(Данные!$A:$A))

формула

Код

=ИНДЕКС(Данные;МАКС(ЕЧИСЛО(ПОИСК(ПОДСТАВИТЬ(ПОДСТАВИТЬ(Данные;".";"");" ";"");ПОДСТАВИТЬ(ПОДСТАВИТЬ(B2;".";"");" ";"")))*СТРОКА(Данные)))

К сообщению приложен файл: 5045966.xlsm (75.1 Kb)

Яндекс: 410016850021169

Дата: Пятница, 12.05.2017, 16:16 | Сообщение № 7

Группа: Проверенные

Ранг: Обитатель

Сообщений: 268

Репутация: 4 ±

Замечаний: 0% ±

Excel 2003-2016

sboy, Вроде все хорошо, только есть номера с двумя точками на конце (я этот момент упустил) формула подхватывает именно его, а нужен без точек.
Файл обновил

И ещё у меня макрос протягивает формулу до конца списка
[vba]

Код

Range("G2:G" & Cells(Rows.Count, 1).End(xlUp).Row).Formula = [G2].FormulaR1C1 'протягиваем формулу

[/vba]
Он её превращает в простую формулу из формулы массива, может как-то по другому протягивать?

К сообщению приложен файл: 5363382.xlsm (74.6 Kb)

Я не Гуру, но стремлюсь!

Сообщение отредактировал ZamoK - Пятница, 12.05.2017, 16:18

Дата: Пятница, 12.05.2017, 16:19 | Сообщение № 8

Группа: Друзья

Ранг: Участник клуба

Сообщений: 2566

Репутация: 724 ±

Замечаний: 0% ±

Excel 2010

Для формулы массива нужно в коде писать
[vba]

Код

.FormulaArray

[/vba]
По поводу двух точек не совсем понял, покажите в файле

Яндекс: 410016850021169

Дата: Пятница, 12.05.2017, 16:31 | Сообщение № 9

Группа: Проверенные

Ранг: Обитатель

Сообщений: 268

Репутация: 4 ±

Замечаний: 0% ±

Excel 2003-2016

Все ровно результата нет, хоть .FormulaArray хоть .Formula - все ровно убивает массив.

Я не Гуру, но стремлюсь!

Дата: Пятница, 12.05.2017, 16:57 | Сообщение № 10

Группа: Друзья

Ранг: Участник клуба

Сообщений: 2566

Репутация: 724 ±

Замечаний: 0% ±

Excel 2010

[vba]

Код

Sub formula()
    fa = "=INDEX(Данные,MAX(ISNUMBER(SEARCH(SUBSTITUTE(SUBSTITUTE(Данные,""."",""""),"" "",""""),SUBSTITUTE(SUBSTITUTE(RC[-5],""."",""""),"" "","""")))*ROW(Данные)))"
    For Each cl In Range("G2:G" & Cells(Rows.Count, 1).End(xlUp).Row).Cells
       cl.FormulaArray = fa
    Next cl
End Sub

[/vba]
так работает

Яндекс: 410016850021169

Дата: Понедельник, 15.05.2017, 08:25 | Сообщение № 11

Группа: Проверенные

Ранг: Обитатель

Сообщений: 268

Репутация: 4 ±

Замечаний: 0% ±

Excel 2003-2016

Не не хочет, или я что не так сделал?

К сообщению приложен файл: 6290146.xlsm (81.1 Kb)

Я не Гуру, но стремлюсь!

Сообщение отредактировал ZamoK - Понедельник, 15.05.2017, 08:34

Дата: Понедельник, 15.05.2017, 09:27 | Сообщение № 12

Группа: Друзья

Ранг: Участник клуба

Сообщений: 2566

Репутация: 724 ±

Замечаний: 0% ±

Excel 2010

ZamoK, ваш файл работает нормально, макросом формула нормально вставляется... Что Вы от него ждете?
Если Вы хотите, чтобы заменялись данные в столбце В, то макрос нужно дописывать

Яндекс: 410016850021169

Дата: Понедельник, 15.05.2017, 09:58 | Сообщение № 13

Группа: Проверенные

Ранг: Обитатель

Сообщений: 268

Репутация: 4 ±

Замечаний: 0% ±

Excel 2003-2016

Цитата ZamoK, 12.05.2017 в 16:16, в сообщении № 7 ()

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

Вот я про что! , но если это никак наверно автозамену надо будет дописать или как-то по другому можно решить?

Я не Гуру, но стремлюсь!