MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Merging names and amounts


Posted by Charles Chuateco on July 09, 2001 12:29 PM

Hello everyone. Compared to the wealth of information all of you possess, I am a novice with MS Excel. I currently use a database program that exports to excel the following information: organizations that our Foundation has funded, the dollar amounts and funding areas.

I spend time creating a spreadsheet that reports the total giving by area. Unfortunately, this program exports each dollar amount separately for a singular organization rather than combining that dollar amount into one figure. For example, if we funded USC ten times, it will list USC ten times with ten amounts in the next column rather than listing USC once with a total figure in the next column. So, the exported excel sheet is quite large, since we have funded some organizations many times.

I manually [=sum(X:Y)] calculate the totals for an organization funding area then change that amount into a value that I paste into the organization’s first amount column. Then I go back and delete all of the other entries for the organization that I just totaled. Is there an easier way to solve this issue and have a formula that will both combine an organization’s name and/or an all the amount’s for an organization, in the entire spreadsheet? Merging cells does not merge dollar amounts, but rather keeps the top most cell. Any and all help would be truly appreciated. Thanks!


Posted by Barrie Davidson on July 09, 2001 12:35 PM

Try using a pivot table. For an example, have a look at:
http://ca.geocities.com/b_davidso/Web_Page_Files/Excel/pivottable1.html

If you need any help just let me know.

Regards,
Barrie

Posted by IML on July 09, 2001 1:52 PM

You can also take advantage of subtotal and consolidate under the data menu. First, make sure you have column headers such in line one such as "firm" and "donatation".

Now go to a blank area on the worksheet next to you date. Go to Data, Consolidate.
click to the reference box and highlight your area (including titles). Check the boxes to use lables in top row and left column and then click okay.

Subtotals work in a similar fashion, but your data must be sorted by firm. You can then collapse your list, highlight it, hit control G, special, visable cells only and control C to copy it.
Now go to an area you want to paste it and hit control V.

These may sound confusing if you haven't used them before, but they are quite easy and powerful tools. Also, play with the pivot table. I would, but they still scare me.

Good luck