Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

From input as below:

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

How can I combine rows for a result as follows?

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
share|improve this question
2  
What is your question and what have you tried? –  chuff Jun 6 '13 at 14:02
    
I think this can be done with Excel Formulas as I did here, Excel - Finding all values in one column for unique values in another column It is not an exact answer but It could help you start to parse the data the way you want. –  Scheballs Jun 6 '13 at 14:36

2 Answers 2

You are looking for an Excel pivot table. Pivot tables take data rows that share several key columns and one or a few data elements and convert them into a single row per key, with multiple aggregated columns.

You are not doing a traditional pivot function, because you are not summing values, but it should work for you.

Pivot tables can be confusing at first, so I recommend you start with just 3 or so test rows, get them to show up as your 3 columns, then add a second user with 3 test rows and make sure it still works correctly, then add all of your dozen or so pivots.

Google excel pivot tutorial or check out this: http://www.wikihow.com/Create-Pivot-Tables-in-Excel

share|improve this answer

If your data originally is in A1:H35 with headers as you show we can build your new table in I1:AE3

enter image description here

(1st Formula)

The key value in each row to identify a unique is the SSN/FedID so in order to get a list of the unique values of SSN, in cell M2 ( Our new SSN/FedID column) type in this array formula. Be sure to enter with CTRL+SHIFT+ENTER to make it an array.

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

This will populate column M:M with the unique SSN numbers so we can use those to fill the rest of the new table. enter image description here

(2nd Formula)

Cell I2 can be filled down and to the right to populate all the I2:L3 cells with the formula...

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

The new headings here are corresponding to our original data headers.

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

enter image description here

(3rd Formula)

Now we have to fill in the unique "FieldName" values as our new headers starting in cell N1 type in this array formula and be sure to enter with CTRL+SHIFT+ENTER to make it an array.

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

Once entered you can drag and copy this formula to the right, to fill cells N1:AE1.

enter image description here

(4th Formula)

Now in cell N2 you can enter this formula.

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

You can now drag that formula down and to the right. It will return a "0" if the cell was blank in the original table. If these cells are fomatted as a Date and it will show 1/0/1900. You can avoid this by changing the formula with an IF() statement to say if 0 then return "-" or whatever you like such as this...

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

enter image description here

-I hope this helps,

More Info on the array formulas I used can be found here... How to extract a unique distinct list from a column in excel

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.