Solved

Excel table reformat by organisation and year

Posted on 2012-03-21
6
191 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Find word and 6 digit number 22 97
iPhone excel activation issues 11 68
Problem to With line 4 39
After updating data my Excel graphs are showing too many dates 8 32
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

912 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

22 Experts available now in Live!

Get 1:1 Help Now