Return latest date for multiple, concatenated values

WAshburner

New Member
Joined
Apr 27, 2015
Messages
6
Hi,

I have data pulled from various sources that ends up in a table in the following format (but with 260 rows) on a the sheet titled "Targetdatemerge"

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Reference[/TD]
[TD]Milestone[/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]ref1[/TD]
[TD]M1.1[/TD]
[TD]1/6/14[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]ref2[/TD]
[TD]M1.1; M1.2[/TD]
[TD]2/6/14[/TD]
[/TR]
</tbody>[/TABLE]

I need to be able to find the latest date for each milestone (there are 24) to be able to export it into a cell on a separate tab (call it "Progress report")

The problem I have is that some milestones have multiple in one cell separated by a semicolon (see 'ref2' above).

What is the easiest way of doing this?

So far I have tried putting each individual Milestone as a column header in a separate table, with each Reference as the row header; see below:



[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]M1.1[/TD]
[TD]M1.2[/TD]
[TD]M2.1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Ref1[/TD]
[TD]*[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Ref2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Ref3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I have then used the following formula in the table cells to return the date for each milestone against each reference:

*=IF(INDEX(Targetdatemerge!$B:$B,MATCH($A2,Targetdatemerge!$A:$A,0))=B$1,Targetdatemerge!$D4,"")

The theory being I could then create a pivot table which would show the latest date for each milestone, which I could then pull off to fill out the "Progress report" tab.

This works fine when there is only one milestone in column B in the "Targetdatemerge" tab, however not when there are multiple separated by a semicolon. Is there a way to work around this?

I have also tried converting text to columns on column B in the "Targetdatemerge" tab to separate the milestones out; however when I try to use the same formula but change the array to multiple columns, like so:

=IF(INDEX(Targetdatemerge!$D:$F,MATCH($A2,Targetdatemerge!$A:$A,0))=B$1,Targetdatemerge!$D4,"")

It comes back with the #REF! error.


I have been searching high and low for a solution to my problem but not managed to find one that fits. Any help would be greatly appreciated!

Thanks,

PS. Sorry if the title doesn't fit the question, I wasn't sure how to explain it best! :confused:
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Hi and welcome to the MrExcel Message Board.

If it were me I would write a little program to arrange the data so that each milestone had a separate row.

The following code will take data from Sheet1 and rearrange it into Sheet2. Note, it clears Sheet2 so make sure you are using names appropriate for you. You can change the sheet names in the macro by overtyping where it says Sheet1 and Sheet2.

First, it copies the row headings from A1:C1 to sheet2.
The macro uses the Split function to split the milestones into separate strings. It then writes each string to a separate line on the output sheet.

Just insert it into a regular Module and run it. (Probably best tp try it in a copy of the real spreadsheet if you are not sure.)


Code:
Sub Get_MS_Dates()

    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim Element As Variant
    Dim i As Long, j As Long
    Set ws1 = ThisWorkbook.Worksheets("Sheet1")
    Set ws2 = ThisWorkbook.Worksheets("Sheet2")
    
    ws2.Cells.Clear
    
    ws2.Range("A1:C1").Value = ws1.Range("A1:C1").Value
    
    j = 2
    For i = 2 To ws1.Cells(ws1.Rows.Count, "A").End(xlUp).Row
        For Each Element In Split(ws1.Range("B" & i).Value, ";")
            ws2.Range("A" & j).Value = ws1.Range("A" & i).Value
            ws2.Range("B" & j).Value = Element
            ws2.Range("C" & j).Value = ws1.Range("C" & i).Value
            j = j + 1
        Next
    Next
    
End Sub
 
Upvote 0

Forum statistics

Threads
1,223,238
Messages
6,170,939
Members
452,368
Latest member
jayp2104

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top