software

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
35887538MM HI-8MP6-120 7$56.005
3571700ANTIBIOTIC ADHESIVE STRP24$34.505
3547585AAA 2PACK 11$30.805
3571726TYLENOL 4 CUP 20$28.655
3572187KLEENEX 19$8.605
3571718BAYER ASPIRIN 4 CUP5$6.905
3658606CAP CASTLE STUD BLACK51$880.206
3892536SWEAT EMB CASTLE OXF X32$793.606
3892510SWEAT EMB CASTLE OXF M29$727.806
3892528SWEAT EMB CASTLE OXF L19$456.806
3894516S/S NAVY LOGO TEE M37$440.876
38846993/4 ZIP BLUE SI LOGO X14$392.346
38846813/4 ZIP BLUE SI LOGO L13$341.436
3892544SWEAT EMB CASTLE OXF XX12$315.806
4053419SWEAT CASTLE NAVY EMB L9$315.406
38846733/4 ZIP BLUE SI LOGO M11$305.496
3771383CAP NAVY PATCH SUNBURST20$299.206
4053427SWEAT CASTLE NAVY EMB X9$292.606
3771375CAP STONE PATCH SUNBURST18$276.806

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
3658606CAP CASTLE STUD BLACK51$880.206
SWEAT EMB CASTLE OXF 92$2294.006
3894516S/S NAVY LOGO TEE M37$440.876
3/4 ZIP BLUE SI LOGO 38$1039.266

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 quot;Subtotalsquot; 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.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 amp; the data
still be valid you could do that in a function amp; 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 (amp; 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 Masterquot;rtjeterquot; wrote:

gt;
gt; I am trying to sort and subtotal a very large table of data. The format of
gt; the data is as such:
gt;
gt; Item # Item Description Quantity Amt Sold
gt; Dept
gt; 35887538MM HI-8MP6-120 7$56.005
gt; 3571700ANTIBIOTIC ADHESIVE STRP24$34.505
gt; 3547585AAA 2PACK 11$30.805
gt; 3571726TYLENOL 4 CUP 20$28.655
gt; 3572187KLEENEX 19$8.605
gt; 3571718BAYER ASPIRIN 4 CUP5$6.905
gt; 3658606CAP CASTLE STUD BLACK51$880.206
gt; 3892536SWEAT EMB CASTLE OXF X32$793.606
gt; 3892510SWEAT EMB CASTLE OXF M29$727.806
gt; 3892528SWEAT EMB CASTLE OXF L19$456.806
gt; 3894516S/S NAVY LOGO TEE M37$440.876
gt; 38846993/4 ZIP BLUE SI LOGO X14$392.346
gt; 38846813/4 ZIP BLUE SI LOGO L13$341.436
gt; 3892544SWEAT EMB CASTLE OXF XX12$315.806
gt; 4053419SWEAT CASTLE NAVY EMB L9$315.406
gt; 38846733/4 ZIP BLUE SI LOGO M11$305.496
gt; 3771383CAP NAVY PATCH SUNBURST20$299.206
gt; 4053427SWEAT CASTLE NAVY EMB X9$292.606
gt; 3771375CAP STONE PATCH SUNBURST18$276.806
gt;
gt; I need to keep all of the data and subtotal it. The problem is that I need
gt; to subtotal similar descriptions within the range. For example in department
gt; 6 I have CAP CASTLE STUD BLACK, SWEAT EMB CASTLE OXF X, SWEAT EMB CASTLE
gt; OXF M, SWEAT EMB CASTLE OXF L, S/S NAVY LOGO TEE M, 3/4 ZIP BLUE SI
gt; LOGO X, and 3/4 ZIP BLUE SI LOGO L
gt;
gt; I want the table to look like:
gt; Item # Item Description Quantity Amt Sold
gt; Dept
gt; 3658606CAP CASTLE STUD BLACK51$880.206
gt; SWEAT EMB CASTLE OXF 92$2294.006
gt; 3894516S/S NAVY LOGO TEE M37$440.876
gt; 3/4 ZIP BLUE SI LOGO 38$1039.266
gt;
gt; I'm trying to keep all of the information and subtotal similar description.
gt; Since I have three types of Sweat Emb Castle I want to subtotal all those
gt; together. But I need to keep the entire table together.
gt;
gt; When I attempt to use quot;Subtotalsquot; I get a separate line for each
gt; description since there is slight difference in some of the descriptions.
gt; And when I attempt to use a pivot table I still don't get what I want. I'm
gt; not sure of any other way to do this except to manually go throught the
gt; entire list and a row. Sum the information myself and then delete the data
gt; used to sum together and leave the sum.
gt;
gt; Does anyone know of a way to do this outside of a macro?
gt;
gt; Thanks.
gt;
gt;
gt;
gt;

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 Colvinquot;rtjeterquot; wrote:

gt;
gt; I am trying to sort and subtotal a very large table of data. The format of
gt; the data is as such:
gt;
gt; Item # Item Description Quantity Amt Sold
gt; Dept
gt; 35887538MM HI-8MP6-120 7$56.005
gt; 3571700ANTIBIOTIC ADHESIVE STRP24$34.505
gt; 3547585AAA 2PACK 11$30.805
gt; 3571726TYLENOL 4 CUP 20$28.655
gt; 3572187KLEENEX 19$8.605
gt; 3571718BAYER ASPIRIN 4 CUP5$6.905
gt; 3658606CAP CASTLE STUD BLACK51$880.206
gt; 3892536SWEAT EMB CASTLE OXF X32$793.606
gt; 3892510SWEAT EMB CASTLE OXF M29$727.806
gt; 3892528SWEAT EMB CASTLE OXF L19$456.806
gt; 3894516S/S NAVY LOGO TEE M37$440.876
gt; 38846993/4 ZIP BLUE SI LOGO X14$392.346
gt; 38846813/4 ZIP BLUE SI LOGO L13$341.436
gt; 3892544SWEAT EMB CASTLE OXF XX12$315.806
gt; 4053419SWEAT CASTLE NAVY EMB L9$315.406
gt; 38846733/4 ZIP BLUE SI LOGO M11$305.496
gt; 3771383CAP NAVY PATCH SUNBURST20$299.206
gt; 4053427SWEAT CASTLE NAVY EMB X9$292.606
gt; 3771375CAP STONE PATCH SUNBURST18$276.806
gt;
gt; I need to keep all of the data and subtotal it. The problem is that I need
gt; to subtotal similar descriptions within the range. For example in department
gt; 6 I have CAP CASTLE STUD BLACK, SWEAT EMB CASTLE OXF X, SWEAT EMB CASTLE
gt; OXF M, SWEAT EMB CASTLE OXF L, S/S NAVY LOGO TEE M, 3/4 ZIP BLUE SI
gt; LOGO X, and 3/4 ZIP BLUE SI LOGO L
gt;
gt; I want the table to look like:
gt; Item # Item Description Quantity Amt Sold
gt; Dept
gt; 3658606CAP CASTLE STUD BLACK51$880.206
gt; SWEAT EMB CASTLE OXF 92$2294.006
gt; 3894516S/S NAVY LOGO TEE M37$440.876
gt; 3/4 ZIP BLUE SI LOGO 38$1039.266
gt;
gt; I'm trying to keep all of the information and subtotal similar description.
gt; Since I have three types of Sweat Emb Castle I want to subtotal all those
gt; together. But I need to keep the entire table together.
gt;
gt; When I attempt to use quot;Subtotalsquot; I get a separate line for each
gt; description since there is slight difference in some of the descriptions.
gt; And when I attempt to use a pivot table I still don't get what I want. I'm
gt; not sure of any other way to do this except to manually go throught the
gt; entire list and a row. Sum the information myself and then delete the data
gt; used to sum together and leave the sum.
gt;
gt; Does anyone know of a way to do this outside of a macro?
gt;
gt; Thanks.
gt;
gt;
gt;
gt;