Grouping Cell Values Together

[steve65]

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

[David Cox]

sort on type


"steve65" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Pete_UK]

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

[steve65]

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

[steve65]

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?

[Pete_UK]

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

[Pete_UK]

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