Data Subtotals (similar but not exactly the same data)

[rtjeter]

I am trying to sort and subtotal a very large table of data. The format of
the data is as such:

Item # Item Description Quantity Amt Sold
Dept
3588753 8MM HI-8MP6-120 7 $56.00 5
3571700 ANTIBIOTIC+ADHESIVE STRP 24 $34.50 5
3547585 AAA 2PACK 11 $30.80 5
3571726 TYLENOL 4 + CUP 20 $28.65 5
3572187 KLEENEX 19 $8.60 5
3571718 BAYER ASPIRIN 4 + CUP 5 $6.90 5
3658606 CAP CASTLE STUD BLACK 51 $880.20 6
3892536 SWEAT EMB CASTLE OXF X 32 $793.60 6
3892510 SWEAT EMB CASTLE OXF M 29 $727.80 6
3892528 SWEAT EMB CASTLE OXF L 19 $456.80 6
3894516 S/S NAVY LOGO TEE M 37 $440.87 6
3884699 3/4 ZIP BLUE SI LOGO X 14 $392.34 6
3884681 3/4 ZIP BLUE SI LOGO L 13 $341.43 6
3892544 SWEAT EMB CASTLE OXF XX 12 $315.80 6
4053419 SWEAT CASTLE NAVY EMB L 9 $315.40 6
3884673 3/4 ZIP BLUE SI LOGO M 11 $305.49 6
3771383 CAP NAVY PATCH SUNBURST 20 $299.20 6
4053427 SWEAT CASTLE NAVY EMB X 9 $292.60 6
3771375 CAP STONE PATCH SUNBURST 18 $276.80 6

I need to keep all of the data and subtotal it. The problem is that I need
to subtotal similar descriptions within the range. For example in department
6 I have CAP CASTLE STUD BLACK, SWEAT EMB CASTLE OXF X, SWEAT EMB CASTLE
OXF M, SWEAT EMB CASTLE OXF L, S/S NAVY LOGO TEE M, 3/4 ZIP BLUE SI
LOGO X, and 3/4 ZIP BLUE SI LOGO L

I want the table to look like:
Item # Item Description Quantity Amt Sold
Dept
3658606 CAP CASTLE STUD BLACK 51 $880.20 6
SWEAT EMB CASTLE OXF 92 $2294.00 6
3894516 S/S NAVY LOGO TEE M 37 $440.87 6
3/4 ZIP BLUE SI LOGO 38 $1039.26 6

I'm trying to keep all of the information and subtotal similar description.
Since I have three types of Sweat Emb Castle I want to subtotal all those
together. But I need to keep the entire table together.

When I attempt to use "Subtotals" I get a separate line for each
description since there is slight difference in some of the descriptions.
And when I attempt to use a pivot table I still don't get what I want. I'm
not sure of any other way to do this except to manually go throught the
entire list and a row. Sum the information myself and then delete the data
used to sum together and leave the sum.

Does anyone know of a way to do this outside of a macro?

Thanks.

[Chris Marlow]

Hi,

Unless you have a rule for how you shorten the item description you are
always going to end up doing an amount of manual work on this type of issue
(in my experience).

If you could drop the last 'word' from each item description & the data
still be valid you could do that in a function & pivot/outline on that. I
suspect this would drop details you don't want dropped in some cases. You
could build a formula that only dropped the sizes ... as I'm sure you can see
things are getting more complicated here ... but still within the realms of
possibility.

Depending on how often you are gong to do this I'd build a look up table
that I could re-use going forward on another sheet (& then use a VLOOKUP to
return my shortened descriptions), so at least my effort was not wasted.

Regards,

Chris.

--
Chris Marlow
MCSD.NET, Microsoft Office XP Master


"rtjeter" wrote:

>
> I am trying to sort and subtotal a very large table of data. The format of
> the data is as such:
>
> Item # Item Description Quantity Amt Sold
> Dept
> 3588753 8MM HI-8MP6-120 7 $56.00 5
> 3571700 ANTIBIOTIC+ADHESIVE STRP 24 $34.50 5
> 3547585 AAA 2PACK 11 $30.80 5
> 3571726 TYLENOL 4 + CUP 20 $28.65 5
> 3572187 KLEENEX 19 $8.60 5
> 3571718 BAYER ASPIRIN 4 + CUP 5 $6.90 5
> 3658606 CAP CASTLE STUD BLACK 51 $880.20 6
> 3892536 SWEAT EMB CASTLE OXF X 32 $793.60 6
> 3892510 SWEAT EMB CASTLE OXF M 29 $727.80 6
> 3892528 SWEAT EMB CASTLE OXF L 19 $456.80 6
> 3894516 S/S NAVY LOGO TEE M 37 $440.87 6
> 3884699 3/4 ZIP BLUE SI LOGO X 14 $392.34 6
> 3884681 3/4 ZIP BLUE SI LOGO L 13 $341.43 6
> 3892544 SWEAT EMB CASTLE OXF XX 12 $315.80 6
> 4053419 SWEAT CASTLE NAVY EMB L 9 $315.40 6
> 3884673 3/4 ZIP BLUE SI LOGO M 11 $305.49 6
> 3771383 CAP NAVY PATCH SUNBURST 20 $299.20 6
> 4053427 SWEAT CASTLE NAVY EMB X 9 $292.60 6
> 3771375 CAP STONE PATCH SUNBURST 18 $276.80 6
>
> I need to keep all of the data and subtotal it. The problem is that I need
> to subtotal similar descriptions within the range. For example in department
> 6 I have CAP CASTLE STUD BLACK, SWEAT EMB CASTLE OXF X, SWEAT EMB CASTLE
> OXF M, SWEAT EMB CASTLE OXF L, S/S NAVY LOGO TEE M, 3/4 ZIP BLUE SI
> LOGO X, and 3/4 ZIP BLUE SI LOGO L
>
> I want the table to look like:
> Item # Item Description Quantity Amt Sold
> Dept
> 3658606 CAP CASTLE STUD BLACK 51 $880.20 6
> SWEAT EMB CASTLE OXF 92 $2294.00 6
> 3894516 S/S NAVY LOGO TEE M 37 $440.87 6
> 3/4 ZIP BLUE SI LOGO 38 $1039.26 6
>
> I'm trying to keep all of the information and subtotal similar description.
> Since I have three types of Sweat Emb Castle I want to subtotal all those
> together. But I need to keep the entire table together.
>
> When I attempt to use "Subtotals" I get a separate line for each
> description since there is slight difference in some of the descriptions.
> And when I attempt to use a pivot table I still don't get what I want. I'm
> not sure of any other way to do this except to manually go throught the
> entire list and a row. Sum the information myself and then delete the data
> used to sum together and leave the sum.
>
> Does anyone know of a way to do this outside of a macro?
>
> Thanks.
>
>
>
>

[Michael]

Hi rtjeter. You could use two helper columns. First, I would insert a new
column A, call it Row ID and number all your rows from 1 to the end. This is
so that you can always resort your data on this column to return your data to
its original position.

In the second helper column, say next to Dept., put in a unique identifier
for all the items you want to be subtotaled together. In your example it
could be castle and logo. Finally, sort and subtotal on the helper column.
I'm sure that setting this up initially will be time consuming, but you only
have to do it once.

As a precaution, before doing anything like this, please make a copy of your
spreadsheet. HTH
--
Sincerely, Michael Colvin


"rtjeter" wrote:

>
> I am trying to sort and subtotal a very large table of data. The format of
> the data is as such:
>
> Item # Item Description Quantity Amt Sold
> Dept
> 3588753 8MM HI-8MP6-120 7 $56.00 5
> 3571700 ANTIBIOTIC+ADHESIVE STRP 24 $34.50 5
> 3547585 AAA 2PACK 11 $30.80 5
> 3571726 TYLENOL 4 + CUP 20 $28.65 5
> 3572187 KLEENEX 19 $8.60 5
> 3571718 BAYER ASPIRIN 4 + CUP 5 $6.90 5
> 3658606 CAP CASTLE STUD BLACK 51 $880.20 6
> 3892536 SWEAT EMB CASTLE OXF X 32 $793.60 6
> 3892510 SWEAT EMB CASTLE OXF M 29 $727.80 6
> 3892528 SWEAT EMB CASTLE OXF L 19 $456.80 6
> 3894516 S/S NAVY LOGO TEE M 37 $440.87 6
> 3884699 3/4 ZIP BLUE SI LOGO X 14 $392.34 6
> 3884681 3/4 ZIP BLUE SI LOGO L 13 $341.43 6
> 3892544 SWEAT EMB CASTLE OXF XX 12 $315.80 6
> 4053419 SWEAT CASTLE NAVY EMB L 9 $315.40 6
> 3884673 3/4 ZIP BLUE SI LOGO M 11 $305.49 6
> 3771383 CAP NAVY PATCH SUNBURST 20 $299.20 6
> 4053427 SWEAT CASTLE NAVY EMB X 9 $292.60 6
> 3771375 CAP STONE PATCH SUNBURST 18 $276.80 6
>
> I need to keep all of the data and subtotal it. The problem is that I need
> to subtotal similar descriptions within the range. For example in department
> 6 I have CAP CASTLE STUD BLACK, SWEAT EMB CASTLE OXF X, SWEAT EMB CASTLE
> OXF M, SWEAT EMB CASTLE OXF L, S/S NAVY LOGO TEE M, 3/4 ZIP BLUE SI
> LOGO X, and 3/4 ZIP BLUE SI LOGO L
>
> I want the table to look like:
> Item # Item Description Quantity Amt Sold
> Dept
> 3658606 CAP CASTLE STUD BLACK 51 $880.20 6
> SWEAT EMB CASTLE OXF 92 $2294.00 6
> 3894516 S/S NAVY LOGO TEE M 37 $440.87 6
> 3/4 ZIP BLUE SI LOGO 38 $1039.26 6
>
> I'm trying to keep all of the information and subtotal similar description.
> Since I have three types of Sweat Emb Castle I want to subtotal all those
> together. But I need to keep the entire table together.
>
> When I attempt to use "Subtotals" I get a separate line for each
> description since there is slight difference in some of the descriptions.
> And when I attempt to use a pivot table I still don't get what I want. I'm
> not sure of any other way to do this except to manually go throught the
> entire list and a row. Sum the information myself and then delete the data
> used to sum together and leave the sum.
>
> Does anyone know of a way to do this outside of a macro?
>
> Thanks.
>
>
>
>