Gregors15 idea of the extra column really solved my problem I was having, so all the credit should go to Gregors15.
By adding a unique value to each row using the ID and Field column you can perform a lookup of the Value using a formula that uses INDEX and MATCH.
Create a new column called “ROW_NAME” that joins the ID and Field column using the following formula
Rename current sheet “before”
Create a new sheet called “after” with distinct list of the ID column. Use the filter located in the menu “Data>More Filters>Standard Filter” (see the screenshot below how to configure the dialog box)
Capture the columns names you want starting at B1 on the “after” sheet
Enter the following formula at B2 on the “after” sheet
The formula works as follows it calculates the “ROW_NAME” value using the CONCATENATE($A2,"-",B$1) then it searches for it the range “$before.$D$2:$D$5000” and then the INDEX function returns the Value contained in the range “$before.$C$2:$C$5000” that is located at a row that the MATCH function found.
Drag the cell to fill the sheet with calculation needed for each cell.
Here is the final document.solution.ods