Outlining - collapse rows based on repeated column value

I would like to be able to collapse all detail sales transaction rows by
value in right most product code column. I have sorted detail sales
transactions by product code column. If I have 1000 transactions for 20
products, I would like to collapse to 20 rows based on the unique product
code value in the right most column.

When I use Grouping & Outlining, Excel collapses the entire table to 1 row.
thanks for any guidance

Another way to get those outlining symbols is to use Data|Subtotals.

You can choose to count/sum/... some of the fields if you want.

Beebe wrote:
>
> I would like to be able to collapse all detail sales transaction rows by
> value in right most product code column. I have sorted detail sales
> transactions by product code column. If I have 1000 transactions for 20
> products, I would like to collapse to 20 rows based on the unique product
> code value in the right most column.
>
> When I use Grouping & Outlining, Excel collapses the entire table to 1 row.
> thanks for any guidance

--

Dave Peterson

The Advanced Filter feature (under the Data / Filter menu branch) shoud work
for you. For the List Range only select the column that has the 20 unique
values, choose to filter the list in-place and check the 'Unique records
only' option.

This will filter the list, hiding rows that have duplicate values, (and they
can be un-hidden later if needed). You can also select to output the filtered
range to another location if you choose.

This will only filter the data, and will not give any summary statistics. If
you want to summarize the data, say, with a record count for each value, you
should use the Subtotals approach that Dave suggested.

HTH,

TK

"Beebe" wrote:

> I would like to be able to collapse all detail sales transaction rows by
> value in right most product code column. I have sorted detail sales
> transactions by product code column. If I have 1000 transactions for 20
> products, I would like to collapse to 20 rows based on the unique product
> code value in the right most column.
>
> When I use Grouping & Outlining, Excel collapses the entire table to 1 row.
> thanks for any guidance

Thanks Dave, this would give me what I wanted but it stops the grouping at
record 14,840 about half way through. There is no blank line. I checked calc
specs for limits, but limit on iterations is 32K something.

"Dave Peterson" wrote:

> Another way to get those outlining symbols is to use Data|Subtotals.
>
> You can choose to count/sum/... some of the fields if you want.
>
> Beebe wrote:
> >
> > I would like to be able to collapse all detail sales transaction rows by
> > value in right most product code column. I have sorted detail sales
> > transactions by product code column. If I have 1000 transactions for 20
> > products, I would like to collapse to 20 rows based on the unique product
> > code value in the right most column.
> >
> > When I use Grouping & Outlining, Excel collapses the entire table to 1 row.
> > thanks for any guidance
>
> --
>
> Dave Peterson
>

Did you select the complete range to do the data|subtotals against?

If you let excel guess at the range, it may have guessed wrong.

I'd remove the subtotals and select the complete range and try data|subtotals
once more.

I've never seen this problem when I selected the complete range--although with
lots of rows, it can take a long time to put in those subtotals.

Beebe wrote:
>
> Thanks Dave, this would give me what I wanted but it stops the grouping at
> record 14,840 about half way through. There is no blank line. I checked calc
> specs for limits, but limit on iterations is 32K something.
>
> "Dave Peterson" wrote:
>
> > Another way to get those outlining symbols is to use Data|Subtotals.
> >
> > You can choose to count/sum/... some of the fields if you want.
> >
> > Beebe wrote:
> > >
> > > I would like to be able to collapse all detail sales transaction rows by
> > > value in right most product code column. I have sorted detail sales
> > > transactions by product code column. If I have 1000 transactions for 20
> > > products, I would like to collapse to 20 rows based on the unique product
> > > code value in the right most column.
> > >
> > > When I use Grouping & Outlining, Excel collapses the entire table to 1 row.
> > > thanks for any guidance
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson