Results 1 to 10 of 10
  1. #1
    martinrrrr
    Guest

    Subtotal Question

    XL 97
    I am trying to create an easy way to determine a Order to Quote ratio from the data below.

    Cust Quote # Order
    ABC 123 yes
    ABC 456 no
    EFG 963 yes
    EFG 875 yes
    LMN 789 yes
    XYZ 147 no

    I am trying to get the info in the followoing form:

    Cust ID Tot Qoutes Tot Orders Ratio
    ABC 2 1 50%
    EFG 2 2 100%
    LMN 1 1 100%
    XYZ 1 1 0%


    I can get to this format but it takes a lot of manual work. I tried using SUBTOTAL and calculated field in Pivot Table but I can't get it to produce the data. Is there a way to do this with other built-in functions?

    Thanks for any help,
    Roger

  2. #2
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    120
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subtotal Question

    Sorry, I can't really tell what it is you want to do here. The way it prints out on the board everything runs together and I can't make a connection between were you starting from and the result you want. Could you be a little clearer.

  3. #3
    martinrrrr
    Guest

    Re: Subtotal Question

    Let me try this again, since the first post didn't display correctly.
    XL 97
    I am trying to create an easy way to determine a Order to Quote ratio from the data below.

    Cust---------Quote------# Order
    ABC----------123----------yes
    ABC----------456----------no
    EFG----------963----------yes
    EFG----------875----------yes
    LMN----------789----------yes
    XYZ----------147----------no

    I am trying to get the info in the followoing form:

    Cust ID------Tot Qoutes------Tot Orders-----Ratio
    ABC----------2-----------------1-------------------50%
    EFG----------2-----------------2------------------100%
    LMN----------1-----------------1------------------100%
    XYZ-----------1-----------------1--------------------0%


    I can get to this format but it takes a lot of manual work. I tried using SUBTOTAL and calculated field in Pivot Table but I can't get it to produce the data. Is there a way to do this with other built-in functions?

    Thanks for any help,
    Roger

  4. #4
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subtotal Question

    Hi,

    FWIW, you can make the indents show be enclosing them in "pre" tags from the button on the right.

    So, from Martin's original post, with the "pre" tags (enclosed by square brackets) it looks like this:
    <pre>Cust Quote # Order
    ABC 123 yes
    ABC 456 no
    EFG 963 yes
    EFG 875 yes
    LMN 789 yes
    XYZ 147 no
    </pre>

    I am trying to get the info in the followoing form:

    <pre>Cust ID Tot Qoutes Tot Orders Ratio
    ABC 2 1 50%
    EFG 2 2 100%
    LMN 1 1 100%
    XYZ 1 1 0%

    </pre>


    Pre tags can cause problems if you're copying the text into somewhere else- the give soft returns instead of hard returns. But I don't think that's applicable here.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Subtotal Question

    Hi Roger,
    The following code should do what you want - please note though that it will overwrite your existing data but it will add all the quote nos. into one row and order status too.
    Give it a try and let me know if you need any explanation/modifications:
    Sub removedups()
    Dim intRow As Integer, intQuotes As Integer, intOrders As Integer
    Dim strQuotes As String, strOrders As String
    On Error Resume Next
    intRow = 2
    Cells(1, 4).Formula = "Total quotes"
    Cells(1, 5).Formula = "Total orders"
    Cells(1, 6).Formula = "Hit ratio"
    Do While Cells(intRow, 1).Formula <> ""
    With Cells(intRow, 1)
    If UCase$(.Offset(0, 2).Formula) = "YES" Then intOrders = 1
    strQuotes = .Offset(0, 1).Formula
    strOrders = .Offset(0, 2).Formula
    intQuotes = 1
    Do While .Formula = .Offset(1, 0).Formula
    intQuotes = intQuotes + 1
    If UCase$(.Offset(1, 2).Formula) = "YES" Then intOrders = intOrders + 1
    strQuotes = strQuotes & ", " & .Offset(1, 1).Formula
    strOrders = strOrders & ", " & .Offset(1, 2).Formula
    .Offset(1, 0).EntireRow.Delete
    Loop
    .Offset(0, 1).Value = strQuotes
    .Offset(0, 2).Value = strOrders
    .Offset(0, 3).Value = intQuotes
    .Offset(0, 4).Value = intOrders
    With .Offset(0, 5)
    .Value = intOrders / intQuotes
    .NumberFormat = "0.00%"
    End With
    intRow = intRow + 1
    End With
    Loop
    End Sub

    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Subtotal Question

    alternatively of course, you could add 2 columns - one with just 1s in every cell (to count quotes) and one using =if(C1="Yes",1,0) (assuming the Order Yes/No is in column C) then you can use subtotals by customer and sum these two new columns. You'll need to add the hit ratio formulae manually though.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    120
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subtotal Question

    If your list is in columns A - C and you create a unique list of customers (using the advanced data filter) in column E then Total Quotes will be:

    {=SUM(N(($A$2:$A$100)=E2))}

    and
    Total orders will be:

    {=SUM(N(($A$2:$A$100)=E2)*N(($C$2:$C$100)="yes"))}

    These are array functions and must be entered using Control + Shift + Enter

    If this is unclear let me know and I will send you a small spreadsheet example.

  8. #8
    Star Lounger
    Join Date
    Jan 2001
    Location
    Newcastle, New South Wales, Australia
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subtotal Question

    You can use a Pivot Table:
    Select the data including the column headings. I copied your data to C10:E16 and renamed # to Num
    choose Data PivotTableReport
    Follow the wizard. Since the range is highlighted you don't need to change that. When you get to the bit where you drag the fields onto the table:
    drag CustomerQuote from the list on the right onto the part of the table marked Row
    drag Order onto Column
    drag Num into the body of the table where it says Data. Then double-click on the Num in the Data section and choose Summarize by Count from the list. It will then say Count of Num.

    Finish the Wizard. I usually put the table into a new sheet, but in this case I put it at G10.
    You may need to play with default settings. My table comes out (G10:J16)
    Count of Num Order
    Cust Quote no yes Grand Total
    ABC 1 1 2
    EFG 0 2 2
    LMN 0 1 1
    XYZ 1 0 1
    Grand Total 2 4 6
    (I can't get the tabs right either, but I hope it makes sense)

    Then in the column beside the
    Grand Total, you can put the formula =I12/J12 in cell K12 (for my example), format as percent and copy down.
    Does this help?
    Ruth Callcott
    [img]/w3timages/icons/dragon.gif[/img][img]/w3timages/icons/spook.gif[/img]

  9. #9
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Subtotal Question

    ... or you can use my inelegant but simple approach attached. (Occam's razor.)
    Attached Files Attached Files
    -John ... I float in liquid gardens
    UTC -7ąDS

  10. #10
    martinrrrr
    Guest

    Re: Subtotal Question

    Thanks everyone for your help. All of your ideas worked and I greatly appreciate the help. It's fantastic to have so many people that are willing to help.[img]/w3timages/icons/smile.gif[/img]

Posting Permissions

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