Results 1 to 6 of 6
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.

    Thanks for all your help!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  2. #2
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    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. #4
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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?
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    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
    Attached Files Attached Files

  6. #6
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

Posting Permissions

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