Need to Copy Date Field Across Range Based on Criteria

[manishc1989]

I fetch a report in the below format. I want date value to be copied in column C.
For an example : First group of data is related to 6/8/2018 in which I need date to be copied in corresponding cell to "Group Value" till last ID given in that group or before the next date comes in and so on.
Data format is fixed and there is no blank rows. I have a option of doing it only Excel formulas because I am restricted to use any macros on the source file.

The purpose of doing it to calculate the issues on the given date, for which each time I have to manually copy and paste the date values.

I have tried few solutions such cell function which reads the date format as "D4", but didn't fulfill requirement.

Please suggest a workaround to this problem.

Sample Data:
[TABLE="width: 255"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD][TABLE="width: 190"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[/TR]
[TR]
[TD]Group Value[/TD]
[TD="align: right"]1234[/TD]
[/TR]
[TR]
[TD]Dates:[/TD]
[TD="align: right"]6/8/2018[/TD]
[/TR]
[TR]
[TD]Defined Value[/TD]
[TD]=[/TD]
[/TR]
[TR]
[TD]Employee Name[/TD]
[TD]Issues Handled[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Totals:[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]ID001[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]ID002[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]ID003[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Group Value[/TD]
[TD="align: right"]1234[/TD]
[/TR]
[TR]
[TD]Dates:[/TD]
[TD="align: right"]6/9/2018[/TD]
[/TR]
[TR]
[TD]Defined Value[/TD]
[TD]=[/TD]
[/TR]
[TR]
[TD]Defined Value 2[/TD]
[TD]=[/TD]
[/TR]
[TR]
[TD]Employee Name[/TD]
[TD]Issues Handled[/TD]
[/TR]
[TR]
[TD]Totals:[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD]ID008[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]ID009[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]ID010[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Group Value[/TD]
[TD="align: right"]1234[/TD]
[/TR]
[TR]
[TD]Dates:[/TD]
[TD="align: right"]6/10/2018[/TD]
[/TR]
[TR]
[TD]Defined Value[/TD]
[TD]=[/TD]
[/TR]
[TR]
[TD]Defined Value 2[/TD]
[TD]=[/TD]
[/TR]
[TR]
[TD]Employee Name[/TD]
[TD]Issues Handled[/TD]
[/TR]
[TR]
[TD]Totals:[/TD]
[TD="align: right"]17[/TD]
[/TR]
[TR]
[TD]ID003[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]ID004[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]ID005[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Skill:[/TD]
[TD="align: right"]1451[/TD]
[/TR]
[TR]
[TD]Dates:[/TD]
[TD="align: right"]6/11/2018[/TD]
[/TR]
[TR]
[TD]Defined Value[/TD]
[TD]=[/TD]
[/TR]
[TR]
[TD]Defined Value 2[/TD]
[TD]=[/TD]
[/TR]
[TR]
[TD]Employee Name[/TD]
[TD]Issues Handled[/TD]
[/TR]
[TR]
[TD]Totals:[/TD]
[TD="align: right"]31[/TD]
[/TR]
[TR]
[TD]ID004[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]ID005[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]ID006[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[RasGhul]

Re: Need to Copy Date Field Across Range Based on Creteria

This worked for me as long as the Date row is the next row after Group value in each set;

=IF(A2="Group Value",OFFSET(A2,1,1),OFFSET(A2,-1,2))

 

[manishc1989]

Re: Need to Copy Date Field Across Range Based on Creteria

This worked for me as long as the Date row is the next row after Group value in each set;

=IF(A2="Group Value",OFFSET(A2,1,1),OFFSET(A2,-1,2))

Thanks Rasghul. It worked awesomely well . I could not think of this simple solution. Again thanks for this truly genious solution.

 

[RasGhul]

Re: Need to Copy Date Field Across Range Based on Creteria

You're welcome mate