Data Manipulation

I am in need of VBA code or something that can manipulate the data shown in Sheet 1 (Current) to be what is shown in Sheet 2 (After). In order for me to create a Dashboard and use Indexing, the data needs to be manipulated. Small amount of data is fine, but I will be working with large amounts that need to be converted over to that. Is there a simple effective way? Pivot Tables dont accurately represent the data either. Thank you.