Страницы: 1
RSS
Как в базе клиентов по каждой организации объединить в 1 строку разные телефоны и эл.адреса?
 
Добрый день, уважаемые форумчане!
Занимаюсь подготовкой базы клиентов для менеджеров по продажам. База берется от парсинга справочников. Проблема в том, что одна и та же организация в справочнике часто находится в разных категориях, у компании может быть несколько адресов. И, соответственно, получаются дубли строк компаний с разными номерами телефонов, эл.адресами (или в одной категории у организации не указан e-mail, в другой категории указан, в третьей указан, причем другой).

Мне необходимо объединить дубли каждой организации в одну строку таким образом, чтобы Наименование осталось в строке в 1 экземпляре (ведь наименование - это единственное что одинаковое у всех этих дублей), а телефоны, e-mail и т.д. из других дублей объединились и были все в одной строке через запятую.

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

PS. Решение искал долго, в том числе на этом форуме много перечитал, но ответа на конкретно этот вопрос (казалось бы простой и очень нужный) не нашел. Поэтому просьба помочь с решением, а не писать типа "читай форум, лентяй" :)
Заранее премного благодарен. Не представляете как! :)
 
Цитата
Новоселов Семен пишет:
Решение искал долго, в том числе на этом форуме
надо срочно что-то менять в методике поиска
http://www.planetaexcel.ru/forum/index.php?PAGE_NAME=read&FID=2&TID=10
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
макросом устроит?
 
Я не программист, но способный. :))))  Макрос устроит. Можете еще прокомментировать как и куда этот макрос вставить?
 
Цитата
Новоселов Семен пишет:
Можете еще прокомментировать как и куда этот макрос вставить?
откройте эту книгу, и откройте ту которую нужно обработать, нажмите Alt+F8
главное чтоб структура таблицы была такая же как в вашем примере, потому что код написан под неё.
Изменено: Scripter - 19.03.2014 14:57:28
 
Обычно такие базы объединяют по ИНН/КПП, ибо это действительно более-менее одинаковое. Потому как названия, если нет системы, как раз таки часто бывают такими: Псков-пром, ОАО "Псков-пром", фирма АО "Псков пром", ПКФ псков-пром...
Если автоматизировать бардак, то получится автоматизированный бардак.
 
Там по ссылке нет перехода на последнюю версию, выложу тут.

Код
Function VLOOKUPCOUPLE(Table As Variant, _
                       SearchColumnNum As Integer, _
                       SearchValue As Variant, _
                       RezultColumnNum As Integer, _
                       Separator_ As String, _
                       Optional BezPovtorov As Boolean = True)

'Table - таблица, где ищем
'SearchColumnNum - столбец, где ищем
'SearchValue - данные, которые ищем
'RezultColumnNum - колонка, откуда берём результат
'Separator_ - разделитель, желательно вводить с пробелом в конце
'BezPovtorov - если поставить 0, то будут выведены все повторяющиеся совпадения

    Dim i As Long, tmp As String, vlk

    If TypeName(Table) = "Range" Then Table = Intersect(Table.Parent.UsedRange, Table).Value
    If BezPovtorov Then
        With CreateObject("Scripting.Dictionary")
            For i = 1 To UBound(Table)
                If Table(i, SearchColumnNum) = SearchValue Then
                    tmp = Table(i, RezultColumnNum)
                    If tmp <> "" Then
                        If Not .Exists(tmp) Then
                            .Add tmp, 0&
                            vlk = vlk & Separator_ & Table(i, RezultColumnNum)
                        End If
                    End If
                End If
            Next i
        End With
    Else
        For i = 1 To UBound(Table)
            If Table(i, SearchColumnNum) = SearchValue Then
                vlk = vlk & Separator_ & Table(i, RezultColumnNum)
            End If
        Next i
    End If
    If vlk > 0 Then vlk = Mid(vlk, Len(Separator_) + 1) Else vlk = ""
    VLOOKUPCOUPLE = vlk
End Function 
 
Hugo, ну и опять затеряется она тут.
скажи модераторам - пусть поместят в копилку.
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
1.Scripter, благодарю за макрос! Супер!

1.1. Alt+F8 почему-то не сработало. Запустил макрос через меню экселя Макросы - Выполнить. Ну это мелочи.

1.2. В настоящий момент, если номер телефона или эл.адрес или сайт дублируется в нескольких строках, то они также в файле "result table" в ячейку помещаются через запятую. А нужно чтобы из дублей осталось по 1 экземпляру. Пример получившегося файла "Попробовал" во вложении. Там же еще раз описал эту проблему подробнее.  Как изменить для этого код макроса?

1.3.Файл базы в экселе который я прикрепил в 1м сообщении в этой теме, был для примера. Файл актуального формата базы прилагаю к этому сообщению. Называется "Актуальный формат базы для которой нужен макрос!" В нем красным цветом отметил заголовки столбцов по которым необходимо сростить дубли и оставить каждого значения по 1 экземпляру в срощенных ячейках (Это "Название", "Телефон", "Факс", "Е-mail", "Сайт" и "Адрес" ;) . Как изменить код из файла "Пробуй" для этого файла?  (Я пытался в коде найти где это можно изменить, но ничего там не понял, поскольку в программировании я чайник  :) ))
Изменено: Новоселов Семен - 20.03.2014 11:11:51
 
Hugo, ikki, благодарю за код макроса. Но у меня не получилось создать макрос из этого кода (Начинаю создавать макрос, в поле VBA вставляю указанный код, но макрос почему-то не создается). Можете добавить пошаговую "инструкцию" или комментарии что делать с этим кодом, для таких чайников как я? Тогда это чрезвычайно важное знание будет доступно и простым людям :).
 
Цитата
Новоселов Семен пишет:
Тогда это чрезвычайно важное знание будет доступно и простым людям
Оно и сейчас вполне доступно тем, кто прочитал "что такое макрос"
А кому не доступно - они не "простые люди".
 
Как сцепить несколько значений в одну ячейку по критерию? СцепитьЕсли


Читаем, не забываем посещать ссылки внутри статьи - там есть разъяснения куда и как.
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
Мой код - это не макрос (процедура), а код функции пользователя, UDF. Применять аналогично как и код The_Prist - как формулу на листе в ячейке.
 
а я вообще не виноватый - просто ссылку на готовую тему дал   :D
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
Цитата
Новоселов Семен пишет:
в коде найти где это можно изменить
добавил проверку на одинаковые значения, за исключением с вариантом "АвтоПЛЮС, автошкола", так как в ячейке содержится несколько тел. номеров, над этим подумать надо (split ячеек делать надо), надеюсь у вас таких значений не много. макрос подогнан под структуру таблицы "Актуальный формат базы для которой нужен макрос!"
Изменено: Scripter - 21.03.2014 06:35:11
 
Все работает  отлично! Невероятно полезный макрос! Благодарю, Scripter, еще раз!!!
 
Может я не в тему но (ссылка удалена) можно без проблем тут
 
Точно не в тему. Бан!.
 
Здравствуйте.

Но, моя цель, предполагаю, более проста для решения и хотелось бы ее достичь без макроса. Т.к. пытаюсь помочь коллегам, 1/3 из которых приличного возраста и макросы не для них.

Уверен, что все хожу вокруг да около, и хотелось бы частично поучаствовать в решении.
По-этому, если можно, прошу поэтапно подвести к правильному решению.

Цель: формула, объединяющая в одну ячейку все значения ячеек по соответствующему идентификатору.
См. вложение, в обеих таблицах будут добавляться строки.

Заранее, очень спасибо, Андрей.
 
В E3:
=[@Коррмеры]&ЕСЛИОШИБКА(СИМВОЛ(10)&ИНДЕКС(СМЕЩ([Столбец3];СТРОКА()-СТРОКА(Таблица1[[#Заголовки];[Столбец3]]);0;ЧСТРОК([Столбец3])-СТРОКА()+СТРОКА(Таблица1[[#Заголовки];[Столбец3]]));ПОИСКПОЗ([@Идентификатор];СМЕЩ([Идентификатор];СТРОКА()-СТРОКА(Таблица1[[#Заголовки];[Идентификатор]]);0;ЧСТРОК([Идентификатор])-СТРОКА()+СТРОКА(Таблица1[[#Заголовки];[Идентификатор]]));0));"")

В J3:
=ВПР([@Идентификатор];Таблица1[[#Все];[Идентификатор]:[Столбец3]];ЧИСЛСТОЛБ(Таблица1[[#Все];[Идентификатор]:[Столбец3]]);0)

Формула массива (ФМ) вводится Ctrl+Shift+Enter
Memento mori
 
Пока, спасибо за помощь.
Приду домой посмотрю.
 
Так ведь уже есть выше:
Код
=VLOOKUPCOUPLE(Таблица1[[#All],[Идентификатор]:[Коррмеры]],1,[@Идентификатор],2,CHAR(10))
И причём тут возраст? Я тоже уже не мальчик...
 
Друзья, не хочу никого обидеть.
Но, поверьте, даже использование другого типа файла, уже создаст пробле
му.

Кстати, многие на форуме старше 37 (мне)
и это вдохновляет, значит не поздно я решил в экселе разобраться.
 
Функции пользователя отлично работают и в xlsx - главное чтоб был доступен код в любой открытой надстройке. Ну или в персональной или любой другой книге с макросами - но удобнее надстройка, тогда синтаксис не нужно менять.
 
JayBhagavan, спасибо.

Пока посмотрел действие - вопросов нет.
Сейчас буду разбираться в логике.
Функций: ЧСТРОК и СИСЛСТОЛБ не было в моих пробах.
 
Hugo, пока Ваша последняя фраза сложна мне для понимания.

Но, это пока.
 
inженер, Создание макросов и пользовательских функций на VBA: http://www.planetaexcel.ru/techniques/3/59/
Ну а про надстройки потом поищите сами...
Страницы: 1
Читают тему (гостей: 1)
Наверх