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

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

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

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

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

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

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