Large clothing orders

[annas3]

I'm a Silk Screen Printer and when I do fund raisers I spend hours organizing the orders. For instance, a football team sells t-shirts, long sleeved t-shirts, sweatshirts, and hooded sweatshirts. There are always two different prints to choose from. One about the school and the other about the football team. All of the shirts come in red, gray, and white. And the sizes go from youth xsmall to adult 6x. First I have to combine the orders to find out how many red youth xsmall t-shirts to order etc
I have to separate each size by the color to order them. Then when it's time to print I need to find out how many youth xsmall red Boyd County t-shirts to print, and how many yxs red football t-shirts to print, and the same with white. Then I go on to youth small. Clean through every size of the t-shirts sorting which print by color and size. On to the long sleveed t's, sweatshirts, and hooded sweatshirts. around 800 shirts in all. Could I possibly do this with Excell? I've stumbled through excel and looked at templetes. I can't do it. I don't expect anyone will answer thsi, but if you do your my hero. Norma

 

[Ken Wright]

Depending on what format your data is in, a Pivot Table might do this for you
easily. Do you have a set of data in Excel or some kind of softcopy format?

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

I'm a Silk Screen Printer and when I do fund raisers I spend hours organizing

the orders. For instance, a football team sells t-shirts, long sleeved t-shirts,
sweatshirts, and hooded sweatshirts. There are always two different prints to
choose from. One about the school and the other about the football team. All of
the shirts come in red, gray, and white. And the sizes go from youth xsmall to
adult 6x. First I have to combine the orders to find out how many red youth
xsmall t-shirts to order etc.

I have to separate each size by the color to order them. Then when it's time

to print I need to find out how many youth xsmall red Boyd County t-shirts to
print, and how many yxs red football t-shirts to print, and the same with white.
Then I go on to youth small. Clean through every size of the t-shirts sorting
which print by color and size. On to the long sleveed t's, sweatshirts, and
hooded sweatshirts. around 800 shirts in all. Could I possibly do this with
Excell? I've stumbled through excel and looked at templetes. I can't do it. I
don't expect anyone will answer thsi, but if you do your my hero. Norma

 

[David McRitchie]

Hi Anna,
use View, fixed

Name QTY Size Hooded Color Logo
Abe 1 S Y Red Team
Bob 1 XL N Blue School
Chuck 2 L N Blue Team
Chuck 2 M Y Red Team
Dwain 1 S Y Red Team
Evan 1 M N Blue School
Frank 2 XXL Y Blue School
Fred 2 M Y Red Team
George 3 S N Blue School
Hal 1 S Y Red Team
Harvey 1 XL Y Blue School
Jim 2 XXL Y Blue Team
Paul 1 M N Blue Team

Pivot table might look something like this
but the lines would make it a lot more readable
I don't want the subtotals (size, color, hooded, logo),
but don't know how to get rid of them or create
the Pivot Table without the subtotals..

Color..... Size Hooded Logo Total
Blue...... L N Team 2
N Total 2
L Total 2
M N School 1
Team 1
N Total 2
M Total 2
S N School 3
N Total 3
S Total 3
XL N School 1
N Total 1
Y School 1
Y Total 1
XL Total 2
XXL Y School 2
Team 2
Y Total 4
XXL Total 4
Blue...... Total 13
Red M Y Team 4
Y Total 4
M Total 4
S Y Team 3
Y Total 3
S Total 3
Red Total 7
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

I'm a Silk Screen Printer and when I do fund raisers I spend hours organizing the orders. For instance, a football team sells

t-shirts, long sleeved t-shirts, sweatshirts, and hooded sweatshirts. There are always two different prints to choose from. One
about the school and the other about the football team. All of the shirts come in red, gray, and white. And the sizes go from youth
xsmall to adult 6x. First I have to combine the orders to find out how many red youth xsmall t-shirts to order etc.

I have to separate each size by the color to order them. Then when it's time to print I need to find out how many youth xsmall red

Boyd County t-shirts to print, and how many yxs red football t-shirts to print, and the same with white. Then I go on to youth
small. Clean through every size of the t-shirts sorting which print by color and size. On to the long sleveed t's, sweatshirts, and
hooded sweatshirts. around 800 shirts in all. Could I possibly do this with Excell? I've stumbled through excel and looked at
templetes. I can't do it. I don't expect anyone will answer thsi, but if you do your my hero. Norma

 

[darkangelstorm]

You could create seperate tables for each colours, White : Gray : Red
And then have under each table XX,X,L,M,S,Y etc.

then use nested IF statements with vlookups to find, then simply sor
them in order

 

[David McRitchie]

Hi Anna,
Better example can be created with

To Create a Pivot Table
Select all of the table i.e. A1:F14
Data, Pivot Tables and ….
Create on separate sheet
drop Color, Hooded, and Logo on left side for rows
drop Size into Columns
drop Qty into Data

make sure you SUM of Qty and not Count of Qty

as I indicated before would prefer to see without
the subtotals for Color, Hooded, Logo, and Size
but can't see how to do that.

(view the following table in Outlook Express with View, font, fixed)

Name QTY Size Hooded Color Logo
Abe 1 S Y Red Team
Bob 1 XL N Blue School
Chuck 2 L N Blue Team
Chuck 2 M Y Red Team
Dwain 1 S Y Red Team
Evan 1 M N Blue School
Frank 2 XXL Y Blue School
Fred 2 M Y Red Team
George 3 S N Blue School
Hal 1 S Y Red Team
Harvey 1 XL Y Blue School
Jim 2 XXL Y Blue Team
Paul 1 M N Blue Team

I emailed you a sample workbook t-shirt.xls

 

[Ken Wright]

Hi Dave - should be able to right click on any of the subtotals, select field
settings and change option selected from automatic to none.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

Hi Anna,
Better example can be created with

To Create a Pivot Table
Select all of the table i.e. A1:F14
Data, Pivot Tables and ….
Create on separate sheet
drop Color, Hooded, and Logo on left side for rows
drop Size into Columns
drop Qty into Data

make sure you SUM of Qty and not Count of Qty

as I indicated before would prefer to see without
the subtotals for Color, Hooded, Logo, and Size
but can't see how to do that.

(view the following table in Outlook Express with View, font, fixed)

Name QTY Size Hooded Color Logo
Abe 1 S Y Red Team
Bob 1 XL N Blue School
Chuck 2 L N Blue Team
Chuck 2 M Y Red Team
Dwain 1 S Y Red Team
Evan 1 M N Blue School
Frank 2 XXL Y Blue School
Fred 2 M Y Red Team
George 3 S N Blue School
Hal 1 S Y Red Team
Harvey 1 XL Y Blue School
Jim 2 XXL Y Blue Team
Paul 1 M N Blue Team

I emailed you a sample workbook t-shirt.xls
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

 

[David McRitchie]

Thanks Ken, don't know why subtotals within Pivot Tables never
bothered me before. Probably because I don't answer many
questions on Pivot Tables, or never had four categories.
..
Have created a web page involving everything from this thread
including your tip on suppressing subtotals within Pivot Tables.
Equal Length Cells per Column
http://www.mvps.org/dmcritchie/excel/equicols.htm

Next minor annoyance is that sizes of S, M, L, XL, XXL
appear in alphabetical order instead of custom logical order.

 

[David McRitchie]

To get the sizes in the correct order (S, M, L, XL, XXL), first create
a custom order (tools, options, custom lists), then select the area
to be sorted left to right (D4:H15), then
Data, Sort, Options, Order: S, M, L,..., the Ascending. To sort

 

[Ken Wright]

Depends very much on the data as to whether or not I want to see them. If I end
up with nearly as many subtotals as I do categories then it's often somewhat of
a turn off visually so I'll blitz them.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

Thanks Ken, don't know why subtotals within Pivot Tables never
bothered me before. Probably because I don't answer many
questions on Pivot Tables, or never had four categories.
.
Have created a web page involving everything from this thread
including your tip on suppressing subtotals within Pivot Tables.
Equal Length Cells per Column
http://www.mvps.org/dmcritchie/excel/equicols.htm

Next minor annoyance is that sizes of S, M, L, XL, XXL
appear in alphabetical order instead of custom logical order.
----
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Hi Dave - should be able to right click on any of the subtotals, select field
settings and change option selected from automatic to none.