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

Question

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

Last Name  First Name  Job Title   Date Hired  Description Field Name  Value   SSN/Fed ID
AL  V   Assistant   12/08/2008      Driver License Expiration Date  2/23/16 364
AL  V   Assistant   12/08/2008  expiration date Physical DOT Exp.   09/17/2014  364
AL  V   Assistant   12/08/2008      Fingerprinting Registered   3/14/13 364
AL  V   Assistant   12/08/2008      Fingerprinting Received 3/28/13 364
AL  V   Assistant   12/08/2008      Child Abuse Mailed  3/21/13 364
AL  V   Assistant   12/08/2008      Child Abuse Received    04/04/2013  364
AL  V   Assistant   12/08/2008      Annual Cert-Violations  3/9/12  364
AL  V   Assistant   12/08/2008      MVR Last Ran    3/20/12 364
AL  V   Assistant   12/08/2008      Driver S Card Exp       364
AL  V   Assistant   12/08/2008      Driver Recert Due       364
AL  V   Assistant   12/08/2008      Physical SB Exp.        364
AL  V   Assistant   12/08/2008      Criminal Received   3/18/13 364
AL  V   Assistant   12/08/2008      Act 24  12/23/2011  364
AL  V   Assistant   12/08/2008      Fingerprinting ID # PAE 364
AL  V   Assistant   12/08/2008      Physical NON-DOT Exp.       364
AL  V   Assistant   12/08/2008      Fingerprinting Went 3/21/13 364
AL  V   Assistant   12/08/2008      Fingerprinting Resubmittal      364
BA  S   Assistant   07/14/2009      Driver License Expiration Date  2/22/2014   190
BA  S   Assistant   07/14/2009  expiration date Physical DOT Exp.   2/1/14  190
BA  S   Assistant   07/14/2009      Fingerprinting Registered   6/17/2009   190
BA  S   Assistant   07/14/2009      Fingerprinting Received 6/25/2009   190
BA  S   Assistant   07/14/2009      Child Abuse Mailed  2/17/2009   190
BA  S   Assistant   07/14/2009      Child Abuse Received    2/26/2009   190
BA  S   Assistant   07/14/2009      Annual Cert-Violations  03/14/2012  190
BA  S   Assistant   07/14/2009      MVR Last Ran    03/20/2012  190
BA  S   Assistant   07/14/2009      Driver S Card Exp       190
BA  S   Assistant   07/14/2009      Driver Recert Due       190
BA  S   Assistant   07/14/2009      Physical SB Exp.        190
BA  S   Assistant   07/14/2009      Criminal Received   2/22/13 190
BA  S   Assistant   07/14/2009      Act 24  12/22/2011  190
BA  S   Assistant   07/14/2009      Fingerprinting ID # PAE 190
BA  S   Assistant   07/14/2009      Physical NON-DOT Exp.       190
BA  S   Assistant   07/14/2009      Fingerprinting Went 6/17/2009   190
BA  S   Assistant   07/14/2009      Fingerprinting Resubmittal      190

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

Last Name  First Name  Title   DOHire  DL Exp Date SS  Physical DOT    Fingerprint Registered  Fingerprint Received    Child Abuse Mailed  Child Abuse Received    Annual Cert MVR last run    Driver S    Driver Recert   Physical SB Criminal Received   Act 24  Fingerprinting ID#  Physical Non DOT    Fingerprinting Went Fingerprint Resubmittal
Al  V   Assistant   12/8/2008   2/23/2016   364 9/17/2012   3/14/2013   3/28/2013   3/21/2013   4/4/2013    3/9/2012    3/20/2013               3/18/2013   12/23/2013  PAE     3/21/2013   
Ba  S   Assistant   7/14/2008   2/22/2014   190 2/1/2014    6/17/2009   6/25/2009   2/17/2009   2/26/2009   3/14/2012   3/20/2012               2/22/2013   12/22/2011  PAE     6/17/2009

Answer 1

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

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

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

Учебное пособие по Google Excel Pivot или ознакомьтесь с ним:http://www.wikihow.com/Create-Pivot-Tables-in-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)

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

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

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

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