• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 238
  • Last Modified:

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
0
Rob-J
Asked:
Rob-J
  • 3
  • 3
1 Solution
 
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
 
Rob-JAuthor Commented:
Thanks helpfinder,

As I think it's close as can get with a pivot table.  Many thanks, Rob
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now