У меня есть таблица Excel 2007 с 2250 строками и 19 столбцами. В этих строках у меня могут быть две строки с дублирующейся информацией о клиентах, которые нужно объединить, но только если ячейка выше пуста. У меня также могут быть строки с данными о клиентах, которые не нужно объединять. Уникальный номер участника-клиента может использоваться для идентификации строк, которые нужно объединить вместе. Я борюсь с разработкой правильного сценария VBA для объединения уникальных данных о клиентах в одну строку (сверху) и удаления строки, которая осталась после объединения. Кто-нибудь готов помочь? Это сэкономит мне часы/дни ручного объединения этих строк, а мы находимся в середине аудита, чувствительного ко времени.
Пример наших данных:
ИМЯ УЧАСТНИКА ФАМИЛИЯ УЧАСТНИКА № MVP СИСТЕМНАЯ ДАТА ЗАПИСИ ДАТА РЕГИСТРАЦИИ БАЛЛЫ MVP ЗАПИСИ РОЗЫГРЫША ФОРМА РЕГИСТРАЦИИ? БАЛЛЫ ВЕРНЫ? БАЛЛЫ ПРОПУЩЕНЫ ОКОНЧАТЕЛЬНЫЕ БАЛЛЫ ЗАПИСИ РОЗЫГРЫША SP Talon # WP Talon # BD DEPT ЗАМЕТКИ СОТРУДНИКА DLR Джин С 550061 02.03.2013 0 0 0 #N/A Джин С 550061 02.03.2013 1539 137 MC MJ SP Стив Г 550087 02.03.2013 30019 1588 PA NR WP Кертис С 550128 24.04.2013 5 0 5 #N/A Курт С 550128 24.04.2013 358 47 MC MJ SP
Правка (не из OP), чтобы добавить версию с разделителями в виде вертикальной черты/абзаца и подчеркиванием пробелов в заголовках:
MEMBER_FIRST_NAME|MEMBER_LAST_NAME|MEMBER_#|MVP_SYSTEM_ENTRY_DATE|ENROLL_DATE|MVP_POINTS|DRAWING_ENTRIES|ENROLL_FORM?|POINTS_CORRECT?|POINTS_MISSED|FINAL_POINTS|DRAWING_ENTRIES|SP_Talon_#|WP_Talon_#|BD|DEPT|EMPLOYEE|NOTES|DLR
Gene|S|550061|02.03.2013||0|0||||0|#N/A|||||||
Gene|S|550061||02.03.2013||||||||1539|137||MC|MJ||SP
Steve|G|550087||02.03.2013||||||||30019|1588||PA|NR||WP
Curtis|S|550128|24.04.2013||5|0||||5|#N/A|||||||
Курт|S|550128||24.04.2013||||||||358|47||MC|MJ||SP
решение1
Я не совсем уверен в правильности предоставленных вами разъяснений, но вот так!:
Ключевой момент — нижеследующее предполагает, что в рамках номера участника MVP ДАТА ВХОДА В СИСТЕМУ всегда будет указана выше ДАТЫ РЕГИСТРАЦИИ.
Для безопасности работайте с копией и добавляйте порядковый номер к каждой строке (например, insert ColumnA, put 1in A1, =A1+1in A2и скопируйте формулу до строки Row2250. Скопируйте ColumnAи вставьте Special/Values поверх.
Выберите D2, Главная > Стили – Условное форматирование, Новое правило, Использовать формулу для определения ячеек для форматирования, Форматировать значения, где эта формула истинна: вставить =AND(NOT(ISBLANK(D2)),OR(D1=D2,D2=D3)), Формат, Заполнить, выбрать желтый, ОК, ОК. В Условное форматирование - Управление правилами, в Применяется ввести =$D$2:$D$2250, Применить. ОК.
Выберите всю электронную таблицу (щелкните по треугольнику слева от A и над 1 в заголовках), Данные > Сортировка и фильтр – Фильтр и для ColumnDФильтра по цвету выберите желтый.
Скопируйте строку Row1 до последней строки, пронумерованной синим цветом, и вставьте на A1другой лист (например, Sheet2).
На Листе2 удалите F1, сдвиньте ячейки вверх, ОК. Также N1:T1. (Здесь может потребоваться дополнительная проверка на глаз.)
Добавьте новый ColumnAна Лист2. Вставьте 1в A1, 2в A2, выберите A1:A2, захватите нижний правый угол выделения, удерживайте левую кнопку мыши нажатой, перетаскивая вниз до тех пор, пока не нажмете и не удержите Ctrl.
Выберите Лист2, Данные > Сортировка и фильтр – Сортировка, установите флажок Мои данные содержат заголовки, Сортировать по ColumnA(первой букве 1s!), Сортировать по значениям, От меньшего к большему, ОК.
Запишите номер самой низкой строки, которая содержится 2в ColumnAЛисте2, и номер самой высокой занятой строки. Удалить ColumnA.
Вернитесь на первый лист и удалите все строки, выделенные желтым цветом.
На Листе 2 выберите строку с меньшим номером и все остальные занятые строки с большим номером, скопируйте и вставьте обратно в ColumnAнижнюю часть первого листа.
Надеюсь, это достигнет большей части того, что вам нужно, или, если нет, то это 'шаг в правильном направлении'! Для проверки, ваш последний занятый ряд теперь должен быть 2250+1 минус разница между двумя числами, указанными выше.
Для проверки ИМЯ УЧАСТНИКА я предлагаю создать таблицу поиска по номеру УЧАСТНИКА и этому, а затем сравнить ИМЯ УЧАСТНИКА на этой основе в листе, копию которого вы сделали. Курт или Куртис, по-видимому, является оценочным решением.
решение2
Вот еще один возможный подход. Он зависит от трех условий:
- Уникальный идентификатор должен быть доступен для различения дублирующихся и недублирующихся полей. В этом случае поле MEMBER# служит этой цели. В других случаях идентификатор может быть создан как комбинация значений в нескольких полях. Этот идентификатор может быть значением в одном поле или составным значением в нескольких полях.
- Не более двух дубликатов любого номера MEMBER#, т.е. не допускается троекратное или более многократное повторение записей.
- Строки сортируются по идентификатору MEMBER#.
Идея состоит в том, чтобы построить преобразованную таблицу, удобнее всего расположить ее справа от существующей таблицы, которая использует формулы для объединения в одну строку частичных данных, общих для двух дубликатов строк, оставляя одну заполненную строку и одну пустую строку.
После этого к таблице результатов можно применить фильтр, чтобы исключить пустые строки и скопировать заполненные строки в другое место.
Как показано ниже, я добавил поле флага «DUP» в столбец A: оно равно 1, если MEMBER# в столбце C равен MEMBER# в предыдущей строке, и равно 0 в противном случае. Два набора строк в примере данных с дублирующимися MEMBER# выделены желтым цветом.
Вот как выглядит таблица результатов формул. Как и ожидалось, дополнительная информация, которая была разделена между двумя записями, была собрана в одной из записей, оставив другую запись заполненной двойными тире ("--"). (Два набора дублирующихся строк в данных примера выделены в таблице более темным синим цветом.)
Если взглянуть на первые две строки таблицы, содержащие дубликаты версий для MEMBER# 550061, то второй «Gene» в строке 4 столбца MEMBER_FIRST_NAME был заменен на «--»; ранее пустое поле ENROLLMENT_DATE в строке 3 теперь заполнено значением 02.03.2013, перемещенным вверх из строки 4; значения N/A для второго поля DRAWING_ENTRIES (столбец M в исходной таблице, столбец AS в новой) были заменены пробелами.
Осталось только применить фильтр, использовать столбец DUP в качестве критериального столбца, выбрать только те строки, где DUP равен 0, и скопировать результат в новое место.
Формулы, используемые для объединения дубликатов, по сути идентичны по структуре, поэтому имеет смысл рассмотреть одну из них подробно. Вот первая формула в таблице, из ячейки AH3, для столбца MEMBER_FIRST_NAME (в конце этого поста я привожу полный набор формул для первой строки таблицы результатов).
=IF($A3=1, If this is row 2 of a DUP set,
"--", Set value of the result cell to "--"
Otherwise it's a row 1 (maybe a dup, maybe not)
IF($A4=0, Is the following row its dup?
IF(IFERROR(B3="",FALSE),"",B3), No, set result to the value on this row
IF(OR(IFERROR(B3="",FALSE),ISERROR(B3)), Yes, but is this row's value blank or error?
IF(IFERROR(B4="",FALSE),"",B4), Yes, use the value from the following row
IF(IFERROR(B3="",FALSE),"",B3)))) No, use the value from this row
Еще один комментарий по коду: несколько окольный оборот речи IFERROR(<cell address>="",FALSE)необходим для того, чтобы правильно отсеять значения ошибок N/A в некоторых строках.
Код для первой строки таблицы результатов
DUP =IF(D3=D2,1,0)
FNAME =IF($A3=1,"--",IF($A4=0,IF(IFERROR(B3="",FALSE),"",B3),IF(OR(IFERROR(B3="",FALSE),ISERROR(B3)),IF(IFERROR(B4="",FALSE),"",B4),IF(IFERROR(B3="",FALSE),"",B3))))
LNAME =IF($A3=1,"--",IF($A4=0,IF(IFERROR(C3="",FALSE),"",C3),IF(OR(IFERROR(C3="",FALSE),ISERROR(C3)),IF(IFERROR(C4="",FALSE),"",C4),IF(IFERROR(C3="",FALSE),"",C3))))
MEMBER# =IF($A3=1,"--",IF($A4=0,IF(IFERROR(D3="",FALSE),"",D3),IF(OR(IFERROR(D3="",FALSE),ISERROR(D3)),IF(IFERROR(D4="",FALSE),"",D4),IF(IFERROR(D3="",FALSE),"",D3))))
ENTRY DT =IF($A3=1,"--",IF($A4=0,IF(IFERROR(E3="",FALSE),"",E3),IF(OR(IFERROR(E3="",FALSE),ISERROR(E3)),IF(IFERROR(E4="",FALSE),"",E4),IF(IFERROR(E3="",FALSE),"",E3))))
ENROL_DT =IF($A3=1,"--",IF($A4=0,IF(IFERROR(F3="",FALSE),"",F3),IF(OR(IFERROR(F3="",FALSE),ISERROR(F3)),IF(IFERROR(F4="",FALSE),"",F4),IF(IFERROR(F3="",FALSE),"",F3))))
MVP_PTS =IF($A3=1,"--",IF($A4=0,IF(IFERROR(G3="",FALSE),"",G3),IF(OR(IFERROR(G3="",FALSE),ISERROR(G3)),IF(IFERROR(G4="",FALSE),"",G4),IF(IFERROR(G3="",FALSE),"",G3))))
ENTRIES =IF($A3=1,"--",IF($A4=0,IF(IFERROR(H3="",FALSE),"",H3),IF(OR(IFERROR(H3="",FALSE),ISERROR(H3)),IF(IFERROR(H4="",FALSE),"",H4),IF(IFERROR(H3="",FALSE),"",H3))))
FORM =IF($A3=1,"--",IF($A4=0,IF(IFERROR(I3="",FALSE),"",I3),IF(OR(IFERROR(I3="",FALSE),ISERROR(I3)),IF(IFERROR(I4="",FALSE),"",I4),IF(IFERROR(I3="",FALSE),"",I3))))
PTS_CORRECT =IF($A3=1,"--",IF($A4=0,IF(IFERROR(J3="",FALSE),"",J3),IF(OR(IFERROR(J3="",FALSE),ISERROR(J3)),IF(IFERROR(J4="",FALSE),"",J4),IF(IFERROR(J3="",FALSE),"",J3))))
PTS_MISSED =IF($A3=1,"--",IF($A4=0,IF(IFERROR(K3="",FALSE),"",K3),IF(OR(IFERROR(K3="",FALSE),ISERROR(K3)),IF(IFERROR(K4="",FALSE),"",K4),IF(IFERROR(K3="",FALSE),"",K3))))
FINAL_PTS =IF($A3=1,"--",IF($A4=0,IF(IFERROR(L3="",FALSE),"",L3),IF(OR(IFERROR(L3="",FALSE),ISERROR(L3)),IF(IFERROR(L4="",FALSE),"",L4),IF(IFERROR(L3="",FALSE),"",L3))))
DR_ENTRIES =IF($A3=1,"--",IF($A4=0,IF(IFERROR(M3="",FALSE),"",M3),IF(OR(IFERROR(M3="",FALSE),ISERROR(M3)),IF(IFERROR(M4="",FALSE),"",M4),IF(IFERROR(M3="",FALSE),"",M3))))
SP_TALON =IF($A3=1,"--",IF($A4=0,IF(IFERROR(N3="",FALSE),"",N3),IF(OR(IFERROR(N3="",FALSE),ISERROR(N3)),IF(IFERROR(N4="",FALSE),"",N4),IF(IFERROR(N3="",FALSE),"",N3))))
WP_TALON =IF($A3=1,"--",IF($A4=0,IF(IFERROR(O3="",FALSE),"",O3),IF(OR(IFERROR(O3="",FALSE),ISERROR(O3)),IF(IFERROR(O4="",FALSE),"",O4),IF(IFERROR(O3="",FALSE),"",O3))))
BD =IF($A3=1,"--",IF($A4=0,IF(IFERROR(P3="",FALSE),"",P3),IF(OR(IFERROR(P3="",FALSE),ISERROR(P3)),IF(IFERROR(P4="",FALSE),"",P4),IF(IFERROR(P3="",FALSE),"",P3))))
DEPT =IF($A3=1,"--",IF($A4=0,IF(IFERROR(Q3="",FALSE),"",Q3),IF(OR(IFERROR(Q3="",FALSE),ISERROR(Q3)),IF(IFERROR(Q4="",FALSE),"",Q4),IF(IFERROR(Q3="",FALSE),"",Q3))))
EMPL =IF($A3=1,"--",IF($A4=0,IF(IFERROR(R3="",FALSE),"",R3),IF(OR(IFERROR(R3="",FALSE),ISERROR(R3)),IF(IFERROR(R4="",FALSE),"",R4),IF(IFERROR(R3="",FALSE),"",R3))))
NOTES =IF($A3=1,"--",IF($A4=0,IF(IFERROR(S3="",FALSE),"",S3),IF(OR(IFERROR(S3="",FALSE),ISERROR(S3)),IF(IFERROR(S4="",FALSE),"",S4),IF(IFERROR(S3="",FALSE),"",S3))))
DLR =IF($A3=1,"--",IF($A4=0,IF(IFERROR(T3="",FALSE),"",T3),IF(OR(IFERROR(T3="",FALSE),ISERROR(T3)),IF(IFERROR(T4="",FALSE),"",T4),IF(IFERROR(T3="",FALSE),"",T3))))