Объединение нескольких строк в одну

Question

Из входных данных, как показано ниже:

Фамилия Имя Должность Дата приема на работу Описание Имя поля Значение SSN/Fed ID
AL V Assistant 12/08/2008 Дата истечения срока действия водительских прав 2/23/16 364
AL V Assistant 12/08/2008 дата истечения срока действия Физический DOT Истек 09/17/2014 364
AL V Assistant 12/08/2008 Дактилоскопия Зарегистрировано 3/14/13 364
AL V Assistant 12/08/2008 Получено отпечатков пальцев 3/28/13 364
AL V Assistant 12/08/2008 Жестокое обращение с детьми Отправлено 3/21/13 364
AL V Assistant 12/08/2008 Получено сообщение о жестоком обращении с детьми 04/04/2013 364
AL V Assistant 12/08/2008 Ежегодные нарушения сертификата 3/9/12 364
AL V Assistant 12/08/2008 MVR Последний запуск 3/20/12 364
AL V Assistant 12/08/2008 Driver S Card Exp 364
AL V Assistant 12/08/2008 Водитель Recert Due 364
AL V Assistant 12/08/2008 Физическая SB Эксп. 364
AL V Помощник 12/08/2008 Уголовное Получено 3/18/13 364
AL V Помощник 12/08/2008 Акт 24 12/23/2011 364
AL V Assistant 12/08/2008 Идентификатор отпечатков пальцев # PAE 364
AL V Assistant 12/08/2008 Физический NON-DOT Exp. 364
AL V Assistant 12/08/2008 Дактилоскопирование прошло 3/21/13 364
AL V Assistant 12/08/2008 Повторная подача отпечатков пальцев 364
BA S Assistant 14.07.2009 Дата истечения срока действия водительских прав 22.02.2014 190
BA S Assistant 07/14/2009 дата истечения срока действия Физический DOT Истекает 02/1/14 190
BA S Assistant 14.07.2009 Дактилоскопия Зарегистрирован 17.06.2009 190
BA S Assistant 07/14/2009 Дактилоскопия получена 6/25/2009 190
BA S Assistant 07/14/2009 Жестокое обращение с детьми Отправлено 2/17/2009 190
BA S Assistant 07/14/2009 Получено сообщение о жестоком обращении с детьми 2/26/2009 190
BA S Assistant 14.07.2009 Ежегодные нарушения сертификата 14.03.2012 190
BA S Assistant 14.07.2009 MVR Последний запуск 20.03.2012 190
BA S Assistant 14.07.2009 Driver S Card Exp 190
BA S Assistant 07/14/2009 Водитель Повторный срок 190
BA S Assistant 07/14/2009 Физический SB Эксп. 190
BA S Assistant 07/14/2009 Уголовное дело получено 2/22/13 190
BA S Assistant 14.07.2009 Акт 24 22.12.2011 190
BA S Assistant 14.07.2009 Идентификатор отпечатков пальцев # PAE 190
BA S Assistant 14.07.2009 Физическое NON-DOT Опыт 190
BA S Assistant 07/14/2009 Дактилоскопия Прошла 6/17/2009 190
BA S Assistant 14.07.2009 Повторная подача отпечатков пальцев 190

Как мне объединить строки для получения результата следующим образом?

Фамилия Имя Должность DOHire DL Дата истечения срока SS Физический отпечаток пальца DOT Зарегистрированный отпечаток пальца Полученный отпечаток пальца Отправлено по почте Получен ежегодный сертификат MVR Последний запуск Водитель S Получение водителя Физический SB Полученный преступник Акт 24 Идентификационный номер отпечатка пальца Физический не DOT отпечаток пальца Прошла повторная отправка отпечатка пальца
Al V Помощник 08.12.2008 23.02.2016 364 17.09.2012 14.03.2013 28.03.2013 21.03.2013 04.04.2013 09.03.2012 20.03.2013 18.03.2013 23.12.2013 PAE 21.03.2013   
Ba S Assistant 14.07.2008 22.02.2014 190 01.02.2014 17.06.2009 25.06.2009 17.02.2009 26.02.2009 14.03.2012 20.03.2012 22.02.2013 22.12.2011 PAE 17.06.2009

Answer 1

Вы ищете сводную таблицу Excel. Сводные таблицы берут строки данных, которые разделяют несколько ключевых столбцов и один или несколько элементов данных, и преобразуют их в одну строку на ключ с несколькими агрегированными столбцами.

Вы не используете традиционную функцию сводной таблицы, поскольку не суммируете значения, но это должно сработать.

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

Учебное пособие по Google Excel Pivot или посмотрите это: http://www.wikihow.com/Создание-сводных-таблиц-в-Excel

Answer 2

Если ваши данные изначально находятся в A1:H35заголовках, как вы показываете, мы можем построить вашу новую таблицуI1:AE3

(1-я формула)

Ключевым значением в каждой строке для идентификации уникальности является SSN/FedID, поэтому для получения списка уникальных значений SSN в ячейке M2(наш новый столбец SSN/FedID) введите эту формулу массива. Обязательно введите с CTRL++ SHIFT, ENTERчтобы сделать его массивом.

=INDEX(H$2:H$35, MATCH(0, COUNTIF(M$1:M1, H$2:H$35), 0))

Это заполнит столбец M:Mуникальными номерами SSN, которые мы сможем использовать для заполнения остальной части новой таблицы.

(2-я формула)

Ячейку I2можно заполнить снизу и справа, чтобы заполнить все I2:L3ячейки формулой...

=INDEX(A:A,MATCH($M2,$H:$H,0))

Новые заголовки здесь соответствуют нашим исходным заголовкам данных.

I1= "LastName"| J1= "FirstName"| K1= "JobTitle"| L1="DateHired"

(3-я формула)

Теперь нам нужно заполнить уникальные "FieldName"значения в качестве наших новых заголовков, начиная с N1типа ячеек в этой формуле массива, и не забудьте ввести CTRL+ SHIFT+, ENTERчтобы сделать его массивом.

=INDEX($F$2:$F$35, MATCH(0, COUNTIF($L$1:L1, $F$2:$F$35), 0))

После ввода вы можете перетащить и скопировать эту формулу вправо, чтобы заполнить ячейки N1:AE1.

(4-я формула)

Теперь в ячейку N2можно ввести эту формулу.

=SUMIFS($G:$G,$F:$F,N$1,$H:$H,$M2)

Теперь вы можете перетащить эту формулу вниз и вправо. Она вернет a, "0"если ячейка была пустой в исходной таблице. Если эти ячейки отформатированы как Дата, то она покажет 1/0/1900. Вы можете избежать этого, изменив формулу на IF()оператор, который скажет, что если 0, то return "-"или что-то еще, что вам нравится, например, это...

=IF(SUMIFS($G:$G,$F:$F,N$1,$H:$H,$M2)=0,"-",SUMIFS($G:$G,$F:$F,N$1,$H:$H,$M2))

-Надеюсь, это поможет,

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