Combining the sheet is the issue.
If I just append one list on the end of another there will be duplicate names, and a pivot won't like that (will it?).
I'm having a play with the consolidate function, but it isn't being very cooperative.
| x404.co.uk http://www.x404.co.uk/forum/ |
|
| Excel help please [solved] http://www.x404.co.uk/forum/viewtopic.php?f=3&t=14127 |
Page 1 of 1 |
| Author: | Spreadie [ Thu Jul 07, 2011 9:12 am ] |
| Post subject: | Excel help please [solved] |
Is there an easy way to merge columns of information in Excel? I have two sheets (2010 & 2011), each with a list of names and list of values. Where the names are the same, I want to list one name with a combined value for both years; but there are names in 2010 that don't appear in 2011 (and vice versa) which also need to be in the final list. All I can think of is using lookups, but is there a simpler function? Help! |
|
| Author: | jonlumb [ Thu Jul 07, 2011 9:44 am ] |
| Post subject: | Re: Excel help please |
Whack all the data into one combined sheet and then generate a pivot table to show sums of data? |
|
| Author: | Spreadie [ Thu Jul 07, 2011 9:54 am ] |
| Post subject: | Re: Excel help please |
Combining the sheet is the issue. If I just append one list on the end of another there will be duplicate names, and a pivot won't like that (will it?). I'm having a play with the consolidate function, but it isn't being very cooperative.
|
|
| Author: | jonlumb [ Thu Jul 07, 2011 9:59 am ] |
| Post subject: | Re: Excel help please |
That's exactly what the pivot table is good at. You basically set the pivot table up so that for each name it shows the sum of the amounts listed against that name in the data set... |
|
| Author: | Spreadie [ Thu Jul 07, 2011 10:00 am ] |
| Post subject: | Re: Excel help please |
Scrap that, I didn't realise a pivot table would combine the values for identical names. Thanks Jon, I appreciate it. |
|
| Author: | Fogmeister [ Thu Jul 07, 2011 10:03 am ] |
| Post subject: | Re: Excel help please |
Yup, Do as Jon said and in the pivot table drag the name field into the "Row Labels" section and drag the values you want to merge into the "Values" section. Make sure it says "Sum of ..." in the field. If it says "Count of ..." then click it and click "Value Field Settings" and change it to Sum. |
|
| Page 1 of 1 | All times are UTC |
| Powered by phpBB® Forum Software © phpBB Group https://www.phpbb.com/ |
|