Rob-J
asked on
Excel table reformat by organisation and year
EE-Future-Payments.xlsxHi, I wonder if you can help and if this is possible?
I have a table in Excel (data based on an SQL connection) which shows organisations, total amount awarded and individual payments due per year (eg 2012, 2013, 2014 etc). (The individual payments add up to the amount awarded) Currently the organisation is repeated in the left column depending on how many payments are made to that organisation.
I'm wondering if possible to run something (VB program?) in Excel that will produce a table where there is one line per organisation and the payments speadout along columns with budget headings 2012, 2013, 2014 etc.
I have attached a spreadsheet:
Sheet 'Original' is the source data and sheet 'Wanted' is the structure of the required table, I hope this makes sense.
Many thanks, Rob
I have a table in Excel (data based on an SQL connection) which shows organisations, total amount awarded and individual payments due per year (eg 2012, 2013, 2014 etc). (The individual payments add up to the amount awarded) Currently the organisation is repeated in the left column depending on how many payments are made to that organisation.
I'm wondering if possible to run something (VB program?) in Excel that will produce a table where there is one line per organisation and the payments speadout along columns with budget headings 2012, 2013, 2014 etc.
I have attached a spreadsheet:
Sheet 'Original' is the source data and sheet 'Wanted' is the structure of the required table, I hope this makes sense.
Many thanks, Rob
ASKER
Hi Helpfinder,
Many thanks for this. This is close but if possible would like to see against each payment in the budget year, the payment date and the payment description.
Also the decision date against the award amount (one award and decision date per organisation)
best wishes, Rob
Many thanks for this. This is close but if possible would like to see against each payment in the budget year, the payment date and the payment description.
Also the decision date against the award amount (one award and decision date per organisation)
best wishes, Rob
hmm, with decision date I am probably able to fight - attachment, but really don´t know how to show description in that pivot
Copy-of-EE-Future-Payments.xlsx
Copy-of-EE-Future-Payments.xlsx
ASKER
Hi Helpfinder,
Thanks for this but I may have confused you a little. With regard to The 'Wanted' sheet, columns B + C (Award + Decision Date) are not in a budget year. They are just in there
own columns just after thye organisation name.
The values under each budget year should be Amount(column D), Date(Column E) and Description(column F) and then repeated for subsequent years. I guess this may not be possible?
Rob
Thanks for this but I may have confused you a little. With regard to The 'Wanted' sheet, columns B + C (Award + Decision Date) are not in a budget year. They are just in there
own columns just after thye organisation name.
The values under each budget year should be Amount(column D), Date(Column E) and Description(column F) and then repeated for subsequent years. I guess this may not be possible?
Rob
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks helpfinder,
As I think it's close as can get with a pivot table. Many thanks, Rob
As I think it's close as can get with a pivot table. Many thanks, Rob
Copy-of-EE-Future-Payments.xlsx