Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA Consolidate Data | Excel Discussion (Misc queries) | |||
Data Consolidate | Excel Discussion (Misc queries) | |||
help to consolidate data | Excel Worksheet Functions | |||
consolidate data using 3d function AND indirect | Excel Worksheet Functions | |||
Is there a function to consolidate multiple records into one recor | Excel Worksheet Functions |