Return latest date for multiple, concatenated values

[WAshburner]

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"

	A	B	C
1	Reference	Milestone	Date
2	ref1	M1.1	1/6/14
3	ref2	M1.1; M1.2	2/6/14

<tbody>
</tbody>

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:

	A	B	C	D
1		M1.1	M1.2	M2.1
2	Ref1	*
3	Ref2
4	Ref3

<tbody>
</tbody>

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!

 

[RickXL]

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

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

 

[WAshburner]

You are a legend, that works perfectly.

Thank you!

 

[RickXL]

No problem.

Thanks for letting me know.

Regards,