Solved

Excel table reformat by organisation and year

Posted on 2012-03-21
6
184 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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
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…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

757 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now