I have a sheet that tracks milestone payments associated with a specific type of project contract. Each row is a contract and columns are payment dates or amounts (see screenshot below). There are up to 5 milestone payments for each contract resulting in 5 pairs of columns (date and amount).
I was hoping to use Pivot App to create a cash flow report that shows contract payments throughout the year. Each row would be a contract and each pivot column would be a month. The values would be the dollar amounts for that month.
Pivot App only allows you to select one column in your source data to generate the pivot columns. Because I have 5 milestone payments, I have 5 columns of dates.
Eventually, there will be other sheets of other contracts on the same projects that I'd like to be able to bring in as well, resulting in hierarchical rows in the pivot table (project = parent, contract = child).
Can anyone think of a creative way to get the 5 date and 5 amount columns into 1 date column and 1 amount column so that I can pivot off of it? I have access to other apps like DataMesh and DataShuttle, if helpful.
