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
Rob-JAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

helpfinderIT ConsultantCommented:
please check if something like this (attachment) is sufficient for you (pivot table, not VBA) or not
Copy-of-EE-Future-Payments.xlsx
0
Rob-JAuthor Commented:
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
0
helpfinderIT ConsultantCommented:
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
0
Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

Rob-JAuthor Commented:
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
0
helpfinderIT ConsultantCommented:
I added "Award" values to pivot rows (so iz is not exactly as you have it on the /wanted" sheet).
But this Description column I am not able to add with appropriate values for you into this pivot. Maybe somebody else, or using VBA which I am not able to do.
Copy-of-EE-Future-Payments.xlsx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rob-JAuthor Commented:
Thanks helpfinder,

As I think it's close as can get with a pivot table.  Many thanks, Rob
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.