Как отфильтровать столбец Excel для записей, соответствующих основному списку

13745
Chris

Общий вопрос влечет за собой сортировку большого списка Excel 2007, чтобы найти записи, которые соответствуют меньшему списку подмножеств.

У меня есть пара идей о том, как подойти к проблеме, но мне не хватает технической сложности для реализации этих идей. Я опишу мое конкретное требование к прецедентам, чтобы прояснить вопрос.

Конкретный пример:

У меня есть основной список названий компаний, которыми я управляю для своей территории продаж (приблизительно 1000 учетных записей клиентов). Каждую неделю моя компания публикует список всех операций, совершаемых в сделке, на каждой территории продаж в США (моей и сотнях других территорий). Это Журнал транзакций содержит более 10000 строк, поэтому сканирование на глаз, чтобы найти транзакции, связанные с моими аккаунтами, практически невозможно.

Мое текущее неадекватное решение состоит в том, чтобы выделить мой список учетных записей желтым, скопировать этот выделенный список, затем вставить этот выделенный список внизу еженедельного журнала транзакций, затем отсортировать AZ, а затем прокрутить вручную до выделенных элементов. Если журнал транзакций содержит одну из моих учетных записей, запись в журнале транзакций будет находиться непосредственно над или под выделенной мной записью. Этот метод эффективен, но чрезвычайно трудоемкий.

Я знаю, как устранить дубликаты в Excel. Есть ли способ устранить все, НО дублирует? Это облегчит визуальное сканирование списка.

Другая проблема остается, потому что несогласованность данных ограничивает использование простых макросов, фильтров или кнопки «найти дубликаты». Имена журналов транзакций часто пишутся немного иначе, чем в моем основном списке.

Например: Acme Widget Company, Inc .; Acme Widget Inc; Acme Widget; Пример: Организация США по гандболу; США по гандболу; Американский гандбол; USHO

Я знаю, что есть некоторые сторонние приложения, которые могут использовать нечеткую логику для сопоставления неточных записей. Однако я не могу запустить плагины на своем корпоративном компьютере. (Если нет очень убедительного случая ...)

Существует ли макрос, который может «нормализовать» журнал транзакций, исключая пробелы и знаки препинания? Существует ли макрос, который может соответствовать первому числу символов Х (больше символов = более высокая точность, но больше шансов пропустить почти повторяющуюся запись ...)? Есть ли макрос, который может вывести или отфильтровать полученный список совпадений?

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

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

Спасибо!

1
@ Крис, у этого поста слишком много вопросов. В будущем вы должны спросить каждого отдельно. hyperslug 14 лет назад 2
В примере 2 (гандбол США) даже удаление пробела + специальных символов и сопоставление первых символов X приведет к совпадению с низкой достоверностью: «US» не близко к «United». Но приличный макрос мог бы вытащить некоторые совпадения. Не уверен, что это уменьшит вашу рабочую нагрузку или все еще оставит слишком много ручного процесса. Опять же, вы должны разбить это или указать, в какой области решение сэкономит вам больше всего времени: удалить недопустимые копии? нечеткое совпадение? скрыть линии снаружи + - 5? hyperslug 14 лет назад 0

4 ответа на вопрос

1
Lance Roberts

Здесь определенно слишком много вопросов, на которые нужно ответить (как комментарии гиперссла). У меня очень похожая ситуация, и я обнаружил, что для поиска дуплей мне просто нужно было сделать это вручную, поскольку было слишком много вариантов для кодирования.

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

1
J.T. Grimes

Я бы использовал функцию MATCH в Excel для получения необходимых данных вместо копирования и сортировки.

Допустим, ваш основной список находится в именованном диапазоне с именем Master, а название компании в журнале транзакций - в столбце D. Где-то в строке транзакции введите следующую формулу: =IF(ISNA(MATCH(D1,Master,0)),0,1)и скопируйте ее во все строки таблицы транзакций. Эта формула приведет к 1, если название компании совпадает, и 0 в противном случае.

Это будет соответствовать только точным именам. Что вам нужно сделать, это добавить альтернативные имена в мастер-диапазон (убедитесь, что сортировали его после добавления имен), чтобы получить все возможные версии.

0
AdamV

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

= ЗАМЕНА (ЗАМЕНА (ЗАМЕНА (НИЖНЯЯ (A1), "inc", ""), ".", ""), "", "") ...

Таким образом, каждая замена заменяет любой экземпляр выделенного текста заменой - в нашем случае здесь ничего нет. Исходя из моего опыта нечеткого сопоставления между именами из разных систем, вам, возможно, придется отбросить такие вещи, как inc, corp, plc и т. Д., Чтобы получить совпадения. Несмотря на то, что вы можете использовать SUBSTITUTE для этого, вы можете получить некоторые странные результаты, когда «Income Corporation» станет «omeorporation», так что может быть безопаснее использовать такие вещи:

ЕСЛИ (ПРАВЫЙ (нижняя (А1), 4) = "АМФ", влево (нижний (А1), длина (А1) -4)), низший (А1)).

Сделайте замену пробелов в прошлом.

Вы можете использовать MATCH или COUNTIF с похожими результатами, чтобы получить столбец, показывающий, какие транзакции соответствуют вашему списку.

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

0
RocketGoal

Интересно, пытались ли вы использовать сводную таблицу? Я использую PT для обработки большого количества данных, и они помогают мне быстро и с полной целостностью рассматривать проблемы несколькими способами.

Выделите все ваши данные и выберите вставить сводную таблицу. Теперь вы сможете просматривать свои данные множеством интерактивных способов, которые позволят вам сузить любые надоедливые двойные записи, неправильное написание и т. Д. Затем вы сможете сортировать, используя пользовательские сортировки и т. Д., А также AZ.

Похожие вопросы