Consolidate rows with some duplicate and some unique data

[Rgittinger]

I want to consolidate multiple rows into one row. Some of the data is the same, and some are unique fields in each row. Here is a sample, in which I would like Jane And John to each be consolidated to one row, with each column filled. When I use the "Consolidate" function on excel, the repeat data (State, Name, Country) keep disappearing.


Let me know if you have any insight! Thanks!







Sample Original Data
[TABLE="width: 500"]
<tbody>[TR]
[TD]State[/TD]
[TD]Name[/TD]
[TD]Country[/TD]
[TD]Application Date[/TD]
[TD]Test Score[/TD]
[TD]Interview Date[/TD]
[/TR]
[TR]
[TD]DC
[/TD]
[TD]Jane [/TD]
[TD]United States[/TD]
[TD]12-1-2015[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DC[/TD]
[TD]Jane [/TD]
[TD]United States[/TD]
[TD][/TD]
[TD]98[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DC[/TD]
[TD]Jane [/TD]
[TD]United States[/TD]
[TD][/TD]
[TD][/TD]
[TD]1-15-2016[/TD]
[/TR]
[TR]
[TD]MD
[/TD]
[TD]John[/TD]
[TD]Canada[/TD]
[TD]1-10-2016[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MD[/TD]
[TD]John[/TD]
[TD]Canada[/TD]
[TD][/TD]
[TD]50[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MD[/TD]
[TD]John[/TD]
[TD]Canada[/TD]
[TD][/TD]
[TD][/TD]
[TD]2-20-2016[/TD]
[/TR]
</tbody>[/TABLE]

This is my desired result
[TABLE="width: 500"]
<tbody>[TR]
[TD]State[/TD]
[TD]Name[/TD]
[TD]Country[/TD]
[TD]Application Date[/TD]
[TD]Test Score[/TD]
[TD]Interview Date[/TD]
[/TR]
[TR]
[TD]DC
[/TD]
[TD]Jane [/TD]
[TD]United States[/TD]
[TD]12-1-2015[/TD]
[TD]98[/TD]
[TD]1-15-2016[/TD]
[/TR]
[TR]
[TD]MD[/TD]
[TD]John[/TD]
[TD]United States[/TD]
[TD]1-10-2016[/TD]
[TD]50[/TD]
[TD]2-20-2016[/TD]
[/TR]
</tbody>[/TABLE]

 

[hiker95]

Rgittinger,

Welcome to the MrExcel forum.

Here is a macro solution for you to consider, that is based on your flat text displays.

Sample raw data in the active worksheet:

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	State	Name	Country	Application Date	Test Score	Interview Date
2	DC	Jane	United States	12-1-2015
3	DC	Jane	United States		98
4	DC	Jane	United States			1-15-2016
5	MD	John	Canada	1-10-2016
6	MD	John	Canada		50
7	MD	John	Canada			2-20-2016
8
Sheet1

And, after the macro:

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	State	Name	Country	Application Date	Test Score	Interview Date			State	Name	Country	Application Date	Test Score	Interview Date
2	DC	Jane	United States	12-1-2015					DC	Jane	United States	12/1/2015	98	1/15/2016
3	DC	Jane	United States		98				MD	John	Canada	1/10/2016	50	2/20/2016
4	DC	Jane	United States			1-15-2016
5	MD	John	Canada	1-10-2016
6	MD	John	Canada		50
7	MD	John	Canada			2-20-2016
8
Sheet1

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 ConsolidateData()
' hiker95, 04/11/2017, ME10000266
Dim r  As Long, t As String
Dim d As Object, a As Variant, o As Variant, c As Long, n As Long
a = ActiveSheet.Range("A1").CurrentRegion
ReDim o(1 To UBound(a, 1), 1 To UBound(a, 2))
o(1, 1) = "State": o(1, 2) = "Name": o(1, 3) = "Country"
o(1, 4) = "Application Date": o(1, 5) = "Test Score"
o(1, 6) = "Interview Date"
Set d = CreateObject("Scripting.Dictionary")
d.CompareMode = vbTextCompare
n = 1
For r = 2 To UBound(a, 1)
  t = a(r, 1) & "," & a(r, 2) & "," & a(r, 3)
    If Not d.Exists(t) Then
      n = n + 1
      For c = 1 To 6
        If Not a(r, c) = vbEmpty Then
          o(n, c) = a(r, c)
        End If
      Next c
    d.Add t, n
  Else
    If Not a(r, 4) = vbEmpty Then o(d(t), 4) = a(r, 4)
    If Not a(r, 5) = vbEmpty Then o(d(t), 5) = a(r, 5)
    If Not a(r, 6) = vbEmpty Then o(d(t), 6) = a(r, 6)
  End If
Next r
Range("I1").Resize(d.Count + 1, 6) = o
Columns(9).Resize(, 6).AutoFit
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 ConsolidateData macro.