Results 1 to 10 of 10
  1. #1
    Lounger
    Join Date
    Jun 2003
    Location
    Singapore, Singapore
    Posts
    39
    Thanks
    0
    Thanked 1 Time in 1 Post

    Automate an Excel Chore (Excel 2003)

    Hi Guys, I have a spreadsheet chore that I’d very much like to automate. I’ve attached a file that contains three tabs populated with data for the purpose of this query. One is called Target, the other Pipeline, followed by Funnel. The only tab that has a fixed format is Pipeline, the others can be changed to work in with a solution that might occur to you guys.
    Data is downloaded form an online database and is copied into the Pipeline sheet as shown. Each month this data changes, so that for example, in January all “Months” may be present. In February, the data won’t include January as this data will have been converted to an “actual” as opposed to a “pipeline” so only months from February will appear. This occurs throughout the year month by month. The size of the data in the Pipeline tab will grow by an indeterminate number of rows each month (it may also decrease in row size due to the “actual” values disappearing).
    What I would like to accomplish is to populate the Target tab from the Pipeline tab for the four sales people “AM” (from column C) with the sum of their individual “MRV” (from column E) based on a criteria that the MRV is only added to this total if the “%” (from column D) in the corresponding row is 70 or more. In addition, a criteria from “Month” (from column A) must be taken into account so that the totals that appear in the Target tab are based individually on Months 1, 2 and 3 in a quarter and for the next quarter Months 4, 5 and 6 and so on for a whole year. In other words, Monthly totals per sales person “AM”. The Month column contains dates that are in the form month/day/year.
    I’ve only populated the Target tab for one quarter and will add formulas to compare the sales persons fixed target with the data that is placed into cells B3 to D6 from the above “calculation”. This sheet will get extended to create the data for the four quarters.
    In addition, I would like to populate the tab Funnel with the same data as I’ve described above, but this time the summation is for “MRV” with a value in the “%” column that is below 70.
    I’m wondering if this requires a macro that could be run or if it is better served by formulae in the cells B3 to D6 of the Target and Funnel tabs? If anyone fells they would like to help solve this “chore”, I’d be very grateful! Cheers, Davy
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Automate an Excel Chore (Excel 2003)

    You could use this formula in Target!E3:

    =SUMPRODUCT((UPPER(Pipeline!$C$2:$C$1000)=UPPER($A 3))*(YEAR(Pipeline!$A$2:$A$1000)=YEAR(E$2))*(MONTH (Pipeline!$A$2:$A$1000)=MONTH(E$2))*(Pipeline!$D$2 :$D$1000>=70)*Pipeline!$E$2:$E$1000)

    Then fill down to row 6 and right to column G. The formula for the Funnel sheet would be the same except that it has Pipeline!$D$2:$D$1000<70 instead of Pipeline!$D$2:$D$1000>=70.

  3. #3
    Lounger
    Join Date
    Jun 2003
    Location
    Singapore, Singapore
    Posts
    39
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Automate an Excel Chore (Excel 2003)

    Hi Hans,
    Excellent! That seems to have cracked the problem. Was wondering if you could give me a brief explanation of how your formula works if you have the time? It certainly knocks off the task in double quick time!
    Many thanks, Davy

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Automate an Excel Chore (Excel 2003)

    The formula =SUMPRODUCT((UPPER(Pipeline!$C$2:$C$1000)=UPPER($A 3))*(YEAR(Pipeline!$A$2:$A$1000)=YEAR(E$2))*(MONTH (Pipeline!$A$2:$A$1000)=MONTH(E$2))*(Pipeline!$D$2 :$D$1000>=70)*Pipeline!$E$2:$E$1000) can be broken down into several parts.

    UPPER(Pipeline!$C$2:$C$1000)=UPPER($A3) compares each of the names in C2:C1000 on the Pipeline sheet to the name in column A on the Target sheet, using UPPER to make the comparison case-independent. The result is an array of TRUE/FALSE values.

    YEAR(Pipeline!$A$2:$A$1000)=YEAR(E$2) compares the year of each of the dates in A2:A1000 on the Pipeline sheet to the year of the date in row 2 on the Target sheet. The result is again an array of TRUE/FALSE values.

    MONTH(Pipeline!$A$2:$A$1000)=MONTH(E$2) compares the month of each of the dates in A2:A1000 on the Pipeline sheet to the month of the date in row 2 on the Target sheet.

    Pipeline!$D$2:$D$1000>=70 compares each of the values in D21000 on the Pipeline sheet to 70.

    For calculation purposes, TRUE is equivalent to 1 in Excel, and FALSE to 0. Multiplying the four arrays results in an array of 1 and 0 values, with 1 where each of the arrays has a 1, i.e. if all four conditions are met: the name matches, the year matches, the month matches and the % is at least 70. This is multiplied with the MRV values in E2:E1000, so each MRV from a row that meets the conditions is included (because it is multiplied by 1) and each MRV from a row that doesn't meet the conditions is discarded (because it is multiplied by 0). SUMPRODUCT adds it all together.

    Note: I used 1000 as a value likely to be large enough. If you increase it, the formulas can handle more rows, but you'll notice that recalculation becomes slower. If you use a smaller value, your formulas can handle fewer rows but they will recalculate faster.

  5. #5
    Lounger
    Join Date
    Jun 2003
    Location
    Singapore, Singapore
    Posts
    39
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Automate an Excel Chore (Excel 2003)

    Hi Hans,
    Again, sincere thanks for this very clear explanation. I would never have thought of this method of doing what I wanted, let alone creating the formula to do it! True class !!
    Cheers, David
    BTW - In which part of the world are you located ?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Automate an Excel Chore (Excel 2003)

    > In which part of the world are you located ?

    The Netherlands (see my profile)

  7. #7
    Lounger
    Join Date
    Jun 2003
    Location
    Singapore, Singapore
    Posts
    39
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Automate an Excel Chore (Excel 2003)

    Thanks again Hans!

  8. #8
    Lounger
    Join Date
    Jun 2003
    Location
    Singapore, Singapore
    Posts
    39
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Automate an Excel Chore (Excel 2003)

    Hi Hans, Subsequent to you providing the excellent formula for calculating this task, I've tried to apply it to a modified worksheet, an example of which is attached. FOr some reason it only calculates TRUE or FALSE. Was wondering if you could help me to figure out why my edits to the formula have not produced the results as for the original example? The relevant columns in the Pipeline sheet are highlighted in yellow. Look forward to hearing from you! Cheers, Davy
    Attached Files Attached Files

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Automate an Excel Chore (Excel 2003)

    The parentheses weren't placed correctly. The formula in J2 should be

    =SUMPRODUCT((UPPER(Pipeline!$F$2:$F$672)=UPPER($E2 ))*(YEAR(Pipeline!$A$2:$A$672)=YEAR(J$1))*(MONTH(P ipeline!$A$2:$A$672)=MONTH(J$1))*(Pipeline!$G$2:$G $672>=70)*Pipeline!$H$2:$H$672)

  10. #10
    Lounger
    Join Date
    Jun 2003
    Location
    Singapore, Singapore
    Posts
    39
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Automate an Excel Chore (Excel 2003)

    Hi Hans, Again, many 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
  •