If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How do I use the data consolidate function?
Hello,
I have one large spreadsheet that includes several scores for the one item eg: a 1 a 90 a 21 b 2 b 50 c 2 c 2 c 45 c 40 c 1 etc. I would like to know how to use the data consolidate function (in simple terms) to consolidate all the scores for a, b and c for example. So the final score for a=112, b=52 and c=88 I understand I could go through the spreadsheet and highlight the values to get a total for each item but the spreadsheet is quite large, approx 30,000 rows and 2.5 alphabets of columns... I am hoping that the data consolidate function will be useful in this regard. Thanks, M. |
#3
|
|||
|
|||
How do I use the data consolidate function?
On Thu, 26 Feb 2009 22:10:02 -0800, Margaret. Margaret.
@discussions.microsoft.com wrote: Hello, I have one large spreadsheet that includes several scores for the one item eg: a 1 a 90 a 21 b 2 b 50 c 2 c 2 c 45 c 40 c 1 etc. I would like to know how to use the data consolidate function (in simple terms) to consolidate all the scores for a, b and c for example. So the final score for a=112, b=52 and c=88 I understand I could go through the spreadsheet and highlight the values to get a total for each item but the spreadsheet is quite large, approx 30,000 rows and 2.5 alphabets of columns... I am hoping that the data consolidate function will be useful in this regard. Thanks, M. Take a look at Pivot Tables. For your simple presentation, I labeled your two columns "Items" and "Scores" I dragged items to "Rows" and Scores to "Values". I got this result: Row Labels Sum of Score a 112 b 52 c 90 Grand Total 254 --ron |
#4
|
|||
|
|||
How do I use the data consolidate function?
Hi Margaret,
As Ron says a pivot table would be the best way, anyway for what it's worth, here is another posssibility. Just using your sample data and assuming your letters are in A1:A10 and the scores are in B1:B10. Then make D1 = a, D2=b, D3=c Then put this formula in E1 and drag down to E3, =SUMIF($A$1:$A$10,D1,$B$1:$B$10) Of course you will have to adjust things to suit your much larger range however the same process should work no problems. HTH Martin "Margaret." Margaret. @discussions.microsoft.com wrote in message ... Hello, I have one large spreadsheet that includes several scores for the one item eg: a 1 a 90 a 21 b 2 b 50 c 2 c 2 c 45 c 40 c 1 etc. I would like to know how to use the data consolidate function (in simple terms) to consolidate all the scores for a, b and c for example. So the final score for a=112, b=52 and c=88 I understand I could go through the spreadsheet and highlight the values to get a total for each item but the spreadsheet is quite large, approx 30,000 rows and 2.5 alphabets of columns... I am hoping that the data consolidate function will be useful in this regard. Thanks, M. |
Thread Tools | |
Display Modes | |
|
|