Copy data from one sheet to another and concat and sort data

I have excel workbook as attached. This is sample only.  I want to automate the below task using VBA. I have requirement like below, would highly appreciate any help with the below:   Consideratio...