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!
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!