Solved

Excel table reformat by organisation and year

Posted on 2012-03-21
6
200 Views
Last Modified: 2012-03-23
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
Comment
Question by:Rob-J
  • 3
  • 3
6 Comments
 
LVL 19

Expert Comment

by:helpfinder
ID: 37746695
please check if something like this (attachment) is sufficient for you (pivot table, not VBA) or not
Copy-of-EE-Future-Payments.xlsx
0
 

Author Comment

by:Rob-J
ID: 37746875
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
 
LVL 19

Expert Comment

by:helpfinder
ID: 37746924
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
Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

 

Author Comment

by:Rob-J
ID: 37747121
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
 
LVL 19

Accepted Solution

by:
helpfinder earned 500 total points
ID: 37747154
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
 

Author Closing Comment

by:Rob-J
ID: 37756078
Thanks helpfinder,

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

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question