1. ## Statistical Reporting: Data

Excel 97, Part of O97, SR2.

Here's a challenge for you Excel Gurus.

I have a column, with varying data points:

Red
Blue
Green
Magenta
Yellow
Maroon
Pink

I have another data point that specifies what clothing item is used for each color:

Shirt
Pants
Shorts
Sweater
Hat
Socks
Undergarments

I have two columns, one for Colors, one for Items.

The columns will always contain items from these lists, but the numbers of these items will vary immensely. What I want to do, is determine what the top 5 colors are. I would also like to know what the top 5 garments are per color.

If this could be assigned to a formula, that would be great. I'm sure it will take at least one formula per 'color' in this case, but that's fine.

Ideally what will happen is the user will use my custom form to 'request' the type of report they want from the data, which will in turn, 'generate' the requested figures, and print them out neatly.

2. ## Re: Statistical Reporting: Data

It's a Tall order, I know.. but does anyone have any ideas? [img]/forums/images/smilies/smile.gif[/img]

Thanks!

3. ## Re: Statistical Reporting: Data

Drk,

Would it be possible to post just a sample of your data, as it is a little difficult (for me me anyway) how to envision your requirements. Perhaps if the data was available set up the way you want some solution might present itself.

Andrew C

4. ## Re: Statistical Reporting: Data

Well.. let me see if I can describe it...

ColumnA: Name
ColumnB: Date
ColumnC: Time
ColumnD: Color
ColumnE: Garment

Each Row represents a 'call', and may have differing colors or garments based on what the customer is requesting.

I need a method of determining the top 5 garments. Then I need a method of determining the top 5 colors for each of those garments.

So, the result might be: Top Garment: Pants
Top 5 Colors: Biege, Olive, White, Black Blue.

In this example there aren't enough categories to make this request seem pliable, but I have about 40 categories in my main table, and need to conduct this type of reporting on all of them.

The report will be spawned upon request by a userform which has combo boxes to select the criteria. <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Cat=&Board=xl&Number=45376&page=&v iew=&sb=&o=&vc=1#Post45376>This Post</A> has been provided to assist me with that... I'm just not sure how to get a 'top 5' report...

Does this help?

5. ## Re: Statistical Reporting: Data

Drk,

Would a pivot table help. I attach a demo with very limited data based on what you have provided. The table shows colors in descending popularity, with the numbers of each type of garment for the colors, also descending.

if you want to look at the data by garment/color just swap the headings by dragging them left or right.

Does that get anywhere near what you want.

Andrew

6. ## Re: Statistical Reporting: Data

Alright, so I've got the gist of Pivot Tables down... but, how can I create a pivot table if the data isn't there yet?

Do I need to spawn the wizard via VBA? how else can I achieve this without the data?

Thanks!

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•