How can I combine data from multiple rows into one row?

[k1njo]

I have a spreadsheet where multiple entries are made for the same person, but on different rows. I'd like to somehow combine all data for a single person into one row. The first table is how it currently is. I'd like to create a formula to somehow combine all data for the rows with duplicate names. The second table is what I'm hoping it to look like.

Last Name	First Name	Sun	Mon	Tue	Wed	Thu	Fri	Sat
Smith	John	1
Smith	John			1
Adams	Ken					1
Smith	John						1
Adams	Ken	1
Adams	Ken		1
Smith	John					1

<tbody>
</tbody>

Last Name	First Name	Sun	Mon	Tue	Wed	Thu	Fri	Sat
Adams	Ken	1	1			1
Smith	John	1		1		1	1

<tbody>
</tbody>

 

[Roderick_E]

why not just a pivot table which easily accomplishes this?

 

[k1njo]

why not just a pivot table which easily accomplishes this?

Thanks for the suggestion Roderick. I haven't used that in a long while and totally forgot about pivot tables. Such a time saver! Thanks!

 

[hiker95]

k1njo,

Here is a macro solution for you to consider.

I assume that your raw data is in Sheet1, and, the results will be in Sheet2, and, that both worksheets already exist.

After testing the macro, if you want the results to replace the raw data, then I can re-write the macro.

Sample raw data (before, and, after the macro):

Excel 2007
	A	B	C	D	E	F	G	H	I	J
1	Last Name	First Name	Sun	Mon	Tue	Wed	Thu	Fri	Sat
2	Smith	John	1
3	Smith	John			1
4	Adams	Ken					1
5	Smith	John						1
6	Adams	Ken	1
7	Adams	Ken		1
8	Smith	John					1
9
Sheet1

And, after the macro in Sheet2:

Excel 2007
	A	B	C	D	E	F	G	H	I	J
1	Last Name	First Name	Sun	Mon	Tue	Wed	Thu	Fri	Sat
2	Adams	Ken	1	1			1
3	Smith	John	1		1		1	1
4
Sheet2

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sub ReorgData()
' hiker95, 09/09/2016, ME963815
Dim oa As Variant
Dim a As Variant, i As Long, c As Long, lr As Long, lc As Long
Dim o As Variant, j As Long
Application.ScreenUpdating = False
With Sheets("Sheet1")   '<-- you can change the sheet name here
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  lc = .Cells(1, Columns.Count).End(xlToLeft).Column
  oa = .Range(.Cells(1, 1), .Cells(lr, lc))
  .Range(.Cells(2, 1), .Cells(lr, lc)).Sort key1:=.Range("A2"), order1:=1, key2:=.Range("B2"), order2:=1
  a = .Range(.Cells(1, 1), .Cells(lr, lc))
  ReDim o(1 To UBound(a, 1), 1 To UBound(a, 2))
  .Range("A1").Resize(UBound(oa, 1), UBound(oa, 2)) = oa
End With
With CreateObject("Scripting.Dictionary")
  .CompareMode = vbTextCompare
  For i = 1 To UBound(a, 1)
    If Not .Exists(a(i, 1) & a(i, 2)) Then
      .Add a(i, 1) & a(i, 2), j
      j = j + 1
      For c = 1 To UBound(a, 2)
        If Not a(i, c) = vbEmpty Then
          o(j, c) = a(i, c)
        End If
      Next c
    Else
      For c = 3 To UBound(a, 2)
        If Not a(i, c) = vbEmpty Then
          o(j, c) = a(i, c)
        End If
      Next c
    End If
  Next i
End With
With Sheets("Sheet2")   '<-- you can change the sheet name here
  .UsedRange.ClearContents
  .Range("A1").Resize(UBound(o, 1), UBound(o, 2)) = o
  .Columns(1).Resize(, UBound(o, 2)).AutoFit
  .Activate
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the ReorgData macro.