Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Grouping Cell Values Together
Hi All, I wanted to create a histogram of the number of Birch trees by age using the data below. Height Age Type 4 2 Maple 4 2 Birch 1 3 Spruce 9 5 Spruce 5 1 Birch 1 3 Maple 10 3 Walnut 3 2 Spruce 7 4 Birch 1 2 Walnut 5 4 Pine 9 3 Walnut 2 5 Pine 2 3 Spruce 5 1 Maple 6 4 Pine 1 5 Birch 8 5 Walnut 10 5 Birch 9 3 Maple 3 1 Pine 9 4 Maple 4 3 Birch 8 1 Walnut 8 4 Maple 1 1 Maple 8 4 Birch 9 4 Maple 5 2 Pine 10 1 Maple Is there a better way to group data together, like shown below, other then using Data Filter? Height Age Type 4 2 Birch 5 1 Birch 7 4 Birch 1 5 Birch 10 5 Birch 4 3 Birch 8 4 Birch Steve -- steve65 ------------------------------------------------------------------------ steve65's Profile: http://www.excelforum.com/member.php...o&userid=37510 View this thread: http://www.excelforum.com/showthread...hreadid=571618 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Grouping Cell Values Together
sort on type
"steve65" wrote in message ... Hi All, I wanted to create a histogram of the number of Birch trees by age using the data below. Height Age Type 4 2 Maple 4 2 Birch 1 3 Spruce 9 5 Spruce 5 1 Birch 1 3 Maple 10 3 Walnut 3 2 Spruce 7 4 Birch 1 2 Walnut 5 4 Pine 9 3 Walnut 2 5 Pine 2 3 Spruce 5 1 Maple 6 4 Pine 1 5 Birch 8 5 Walnut 10 5 Birch 9 3 Maple 3 1 Pine 9 4 Maple 4 3 Birch 8 1 Walnut 8 4 Maple 1 1 Maple 8 4 Birch 9 4 Maple 5 2 Pine 10 1 Maple Is there a better way to group data together, like shown below, other then using Data Filter? Height Age Type 4 2 Birch 5 1 Birch 7 4 Birch 1 5 Birch 10 5 Birch 4 3 Birch 8 4 Birch Steve -- steve65 ------------------------------------------------------------------------ steve65's Profile: http://www.excelforum.com/member.php...o&userid=37510 View this thread: http://www.excelforum.com/showthread...hreadid=571618 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Grouping Cell Values Together
Highlight the three columns of data, including the headers, then Insert
| Name | Create and ensure that only Top Row is checked then OK - this creates some named ranges. Then, assuming your data occupies columns A to C, enter "Age" in E1, "Birch" in F1, and put 1 to 5 in cells E2 to E6. Enter this array* formula in F2: =SUM(IF((Age=$E2)*(Type=F$1),1,0)) *As this is an array formula, once you have typed it in (or subsequently edit it) you have to use CTRL-SHIFT-ENTER (CSE) instead of just ENTER. If you do this correctly, then Excel will wrap curly braces { } around the formula - you must not type these yourself. You can then copy the formula to cells F3:F6. This will give you the count of the number of Birch trees by the age specified in column E. If you want a similar count for Maple, put "Maple" in G1 and copy the formulae from F2:F6 into G2:G6, and similarly for other tree types. Hope this helps - not quite what you asked for, but useful for the histograms. Pete steve65 wrote: Hi All, I wanted to create a histogram of the number of Birch trees by age using the data below. Height Age Type 4 2 Maple 4 2 Birch 1 3 Spruce 9 5 Spruce 5 1 Birch 1 3 Maple 10 3 Walnut 3 2 Spruce 7 4 Birch 1 2 Walnut 5 4 Pine 9 3 Walnut 2 5 Pine 2 3 Spruce 5 1 Maple 6 4 Pine 1 5 Birch 8 5 Walnut 10 5 Birch 9 3 Maple 3 1 Pine 9 4 Maple 4 3 Birch 8 1 Walnut 8 4 Maple 1 1 Maple 8 4 Birch 9 4 Maple 5 2 Pine 10 1 Maple Is there a better way to group data together, like shown below, other then using Data Filter? Height Age Type 4 2 Birch 5 1 Birch 7 4 Birch 1 5 Birch 10 5 Birch 4 3 Birch 8 4 Birch Steve -- steve65 ------------------------------------------------------------------------ steve65's Profile: http://www.excelforum.com/member.php...o&userid=37510 View this thread: http://www.excelforum.com/showthread...hreadid=571618 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Grouping Cell Values Together
Thank you for the information. I end up with the attached results: Which are great. If I understand the formula right we are comparing everything in the named ranges type and age with the corresponding row and column labels we created in the second table shown above in red. This is more of what I was looking for because now I can group by the other categories and create histograms for them. Is there a way to automatically adjust the ranges as additional data is added or do I have to continuously update the ranges? Steve +-------------------------------------------------------------------+ |Filename: results.JPG | |Download: http://www.excelforum.com/attachment.php?postid=5203 | +-------------------------------------------------------------------+ -- steve65 ------------------------------------------------------------------------ steve65's Profile: http://www.excelforum.com/member.php...o&userid=37510 View this thread: http://www.excelforum.com/showthread...hreadid=571618 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Grouping Cell Values Together
I have another question, is there a way using this named ranges techniques to copy rows to another section of the worksheet? Something like the attached image? +-------------------------------------------------------------------+ |Filename: results.JPG | |Download: http://www.excelforum.com/attachment.php?postid=5205 | +-------------------------------------------------------------------+ -- steve65 ------------------------------------------------------------------------ steve65's Profile: http://www.excelforum.com/member.php...o&userid=37510 View this thread: http://www.excelforum.com/showthread...hreadid=571618 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Grouping Cell Values Together
Yes, that is what the formula does.
You could make the named ranges cover a much larger range than you need, so that you do not have to adjust them each time - Insert | Name | Define will show you a list of the named ranges you have. Just click on one and you can see what it refers to in the bottom panel - adjust it for each one to cover 1000 rows if you want to, then click OK. The ranges must be the same size for the formula to work. You could set up dynamic ranges which will adjust themselves automatically to the amount of data you have - this site gives more details: http://www.contextures.com/tiptech.html Hope this helps. Pete steve65 wrote: Thank you for the information. I end up with the attached results: Which are great. If I understand the formula right we are comparing everything in the named ranges type and age with the corresponding row and column labels we created in the second table shown above in red. This is more of what I was looking for because now I can group by the other categories and create histograms for them. Is there a way to automatically adjust the ranges as additional data is added or do I have to continuously update the ranges? Steve +-------------------------------------------------------------------+ |Filename: results.JPG | |Download: http://www.excelforum.com/attachment.php?postid=5203 | +-------------------------------------------------------------------+ -- steve65 ------------------------------------------------------------------------ steve65's Profile: http://www.excelforum.com/member.php...o&userid=37510 View this thread: http://www.excelforum.com/showthread...hreadid=571618 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Grouping Cell Values Together
I'm not sure what you mean. Dave Cox's earlier answer would enable you
to obtain your data grouped by tree type, i.e. highlight the 3 columns of data and sort it by type. If you wanted to enter "Birch" and see all the data for that tree type, you could do this by an INDEX/MATCH formula copied down, but in order to pick up all duplicates you would need the range for the second and subsequent searches to avoid the location of the earlier match, so the named ranges wouldn't be used. Hope this helps. Pete steve65 wrote: I have another question, is there a way using this named ranges techniques to copy rows to another section of the worksheet? Something like the attached image? +-------------------------------------------------------------------+ |Filename: results.JPG | |Download: http://www.excelforum.com/attachment.php?postid=5205 | +-------------------------------------------------------------------+ -- steve65 ------------------------------------------------------------------------ steve65's Profile: http://www.excelforum.com/member.php...o&userid=37510 View this thread: http://www.excelforum.com/showthread...hreadid=571618 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert Hard-coded cell values to constants | Excel Discussion (Misc queries) | |||
Compiling macro based on cell values | Excel Discussion (Misc queries) | |||
Format cell color based on multiple cell values | Excel Discussion (Misc queries) | |||
Replacing Linked Cell Values w/ Current Values | Excel Worksheet Functions | |||
Possible Lookup Table | Excel Worksheet Functions |