Combine data from different rows

[MGordinho]

Hello,

I have a table like this:

ID1	ID2	Startdate	Enddate
MS	PT668	14-11-2015	15-10-2016
MS	PT668	16-10-2016	31-12-9999
WZ	PT443	15-03-2014	16-10-2016
WZ	PT443	17-10-2016	28-12-2016
WZ	PT320	29-12-2016	31-12-9999
AT	PT123	01-07-2016	31-12-9999

<tbody>
</tbody>

I need to combine the dates for each row with the same ID1 and ID2 (Startdate should be the oldest and the Enddate should be the newest). I should get a table like this:

ID1	ID2	Startdate	Enddate
MS	PT668	14-11-2015	31-12-9999
WZ	PT443	15-03-2014	28-12-2016
WZ	PT320	29-12-2016	31-12-9999
AT	PT123	01-07-2016	31-12-9999

<tbody>
</tbody>

Can someone help me?

 

[Joe4]

Welcome to the Board!

Perhaps you might be able to use a Pivot Table or Subtotals, though to me the easiest way would be to use Access. This would be an Aggregate Query in Access, and is very easy to do in Access.

 

[MGordinho]

Are you able to help me and explain me how it can be done in Access? How do I build the query you mention? (My Access skills are very limited)

 

[miless2111s]

Pivot table method (very simple):
Insert the pivot:

• Select your data
• Data / Pivot
• Confirm your data range
• Chose where you want the pivot to display

Set up your table:

• Row labels will be ID1 and then ID2
• Insert / drag and drop StartDate as a value entry, open up the field settings and chose to display Minimum and change the title name to Start
• Insert / drag and drop StartDate as a value entry, open up the field settings and chose to display Maximum and change the title name to Finish

Remove the grand totals and format:

• Open pivot table options and de-select "show grand totals for rows" and "show grand totals for columns"
• Select the column headings for both the date fields and chose the format of the dates you want to see

When you change the date you need to remember to update the pivot table (right mouse, refresh)

Hope that helps

Miles

 

[hiker95]

MGordinho,

Welcome to the MrExcel forum.

If I understand you correctly, then 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
1	ID1	ID2	Startdate	Enddate
2	MS	PT668	14-11-2015	15-10-2016
3	MS	PT668	16-10-2016	31-12-9999
4	WZ	PT443	15-03-2014	16-10-2016
5	WZ	PT443	17-10-2016	28-12-2016
6	WZ	PT320	29-12-2016	31-12-9999
7	AT	PT123	7-1-2016	31-12-9999
8
Sheet1

And, after the macro:

Excel 2007
	A	B	C	D	E	F	G	H	I	J
1	ID1	ID2	Startdate	Enddate			ID1	ID2	Startdate	Enddate
2	MS	PT668	14-11-2015	15-10-2016			MS	PT668	14-11-2015	31-12-9999
3	MS	PT668	16-10-2016	31-12-9999			WZ	PT443	15-03-2014	28-12-2016
4	WZ	PT443	15-03-2014	16-10-2016			WZ	PT320	29-12-2016	31-12-9999
5	WZ	PT443	17-10-2016	28-12-2016			AT	PT123	7-1-2016	31-12-9999
6	WZ	PT320	29-12-2016	31-12-9999
7	AT	PT123	7-1-2016	31-12-9999
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 CombineData()
' hiker95, 04/12/2017, ME1000199
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) = "ID1": o(1, 2) = "ID2": o(1, 3) = "Startdate": o(1, 4) = "Enddate"
Set d = CreateObject("Scripting.Dictionary")
d.CompareMode = vbTextCompare
n = 1
For r = 2 To UBound(a, 1)
  t = a(r, 1) & "," & a(r, 2)
    If Not d.Exists(t) Then
      n = n + 1
      For c = 1 To 4
        If Not a(r, c) = vbEmpty Then
          o(n, c) = a(r, c)
        End If
      Next c
    d.Add t, n
  Else
    If a(r, 4) > o(d(t), 4) Then o(d(t), 4) = a(r, 4)
  End If
Next r
Range("G1").Resize(d.Count + 1, 4) = o
Range("I2:J" & UBound(o, 1)).NumberFormat = "d-m-yyyy"
Range("G1").Resize(, 4).Font.Bold = True
Columns(7).Resize(, 4).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 CombineData macro.

 

[hiker95]

Startdate should be the oldest and the Enddate should be the newest

MGordinho,

Here is an updated macro that will adjust to the above quote.

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).

Sub CombineData_V2()
' hiker95, 04/12/2017, ME1000199
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) = "ID1": o(1, 2) = "ID2": o(1, 3) = "Startdate": o(1, 4) = "Enddate"
Set d = CreateObject("Scripting.Dictionary")
d.CompareMode = vbTextCompare
n = 1
For r = 2 To UBound(a, 1)
  t = a(r, 1) & "," & a(r, 2)
    If Not d.Exists(t) Then
      n = n + 1
      For c = 1 To 4
        If Not a(r, c) = vbEmpty Then
          o(n, c) = a(r, c)
        End If
      Next c
    d.Add t, n
  Else
    If a(r, 3) < o(d(t), 3) Then o(d(t), 4) = a(r, 3)
    If a(r, 4) > o(d(t), 4) Then o(d(t), 4) = a(r, 4)
  End If
Next r
Range("G1").Resize(d.Count + 1, 4) = o
Range("I2:J" & UBound(o, 1)).NumberFormat = "d-m-yyyy"
Range("G1").Resize(, 4).Font.Bold = True
Columns(7).Resize(, 4).AutoFit
End Sub

Then run the CombineData_V2 macro.

 

[Joe4]

Are you able to help me and explain me how it can be done in Access? How do I build the query you mention? (My Access skills are very limited)

1. Either import or link the data into an Access table (see: https://support.office.com/en-us/ar...-to-data-f4a5b6e6-c98d-43a0-9588-0eab19a75619 if you need help).
2. Then go to Create -> Query -> Query Design, and choose your table.
3. Double-click on all four fields to add them to the Query grid
4. Click on the Totals icon in the ribbon (looks like a Sigma/summation sign)
5. This will add a Totals line to the Query Grid with the phrase "Group By" under each field
6. Under the StartDate field, change the "Group By" option on the Totals row to "Min"
7. Under the EndDate field, change the "Group By" option on the Totals row to "Max"
8. View the results

That's it!