+ Reply to Thread
Results 1 to 30 of 30

Macro to sort data and add totals

  1. #1
    Registered User
    Join Date
    05-22-2019
    Location
    USA
    MS-Off Ver
    Office 365
    Posts
    66

    Macro to sort data and add totals

    Hello,
    I need to create a complicated macro for my business. The attached excel sheet shows the data that the macro needs to process and the end result. This is what the macro need to do:

    When I run the macro in sheet named "Before" it will do the following:
    1) It will rearrange the data in the sheet according to column "Campaign"
    2) It will calculate =IF(D3="Check", C3-(C3*0.08),C3-(C3*0.11).
    3) It will calculate the total of step two for each campaign and create a new excel sheet that show the name of the campaign and the total of all the cells from step 2.

    In the attached example, the data in sheet "Before" was processed then Sheet "After" was created (I want a new excel workbook not only a sheet) with the total for each campaign.

    I hope this is helpful.

    THANK YOU FOR ALL YOUR HELP!
    Attached Files Attached Files
    Last edited by manuscriptguru; 03-03-2020 at 12:11 AM.

  2. #2
    Registered User
    Join Date
    10-29-2012
    Location
    US
    MS-Off Ver
    Excel 365 ProPlus
    Posts
    11

    Re: Complicated Macro

    This seems straightforward, but I have questions.
    Will the "After" sheet always be present? What is the name of the resulting new workbook(s) - is it the campaign name?

  3. #3
    Registered User
    Join Date
    05-22-2019
    Location
    USA
    MS-Off Ver
    Office 365
    Posts
    66

    Re: Complicated Macro

    No, I created the "After" sheet to make it easier to explain the final product I want to see. The new workbook can be named "Final Payouts"

  4. #4
    Registered User
    Join Date
    10-29-2012
    Location
    US
    MS-Off Ver
    Excel 365 ProPlus
    Posts
    11

    Re: Complicated Macro

    OK, so everything seen on the "After" Tab is what you want in a single new workbook, named "Final Payouts"? and the results shown on that tab right now are the results you would expect from the data shown on the "before" tab? I'll take a stab at the code.

  5. #5
    Registered User
    Join Date
    05-22-2019
    Location
    USA
    MS-Off Ver
    Office 365
    Posts
    66

    Re: Complicated Macro

    Yes to both questions. Thank you for your help.

  6. #6
    Registered User
    Join Date
    10-29-2012
    Location
    US
    MS-Off Ver
    Excel 365 ProPlus
    Posts
    11

    Re: Complicated Macro

    Copy and paste this code into your file. Is should produce a new file with compiled results as shown on your "After" tab.
    Cheers!

    Please Login or Register  to view this content.
    Last edited by jrevball; 03-03-2020 at 09:01 AM.

  7. #7
    Registered User
    Join Date
    10-29-2012
    Location
    US
    MS-Off Ver
    Excel 365 ProPlus
    Posts
    11

    Re: Complicated Macro

    I'm not sure why that didn't indent properly. Shouldn't affect the macro, but indenting makes it easier to read.

  8. #8
    Registered User
    Join Date
    05-22-2019
    Location
    USA
    MS-Off Ver
    Office 365
    Posts
    66

    Re: Complicated Macro

    I got an error. Please see the attached screenshots

  9. #9
    Registered User
    Join Date
    10-29-2012
    Location
    US
    MS-Off Ver
    Excel 365 ProPlus
    Posts
    11

    Re: Complicated Macro

    This macro includes the Dictionary object.

    You will have to add Microsoft Scripting Runtime reference in VBA, it is not loaded by default.

    Go to Tools-> References
    Then scroll to "Microsoft Scripting Runtime". Check that box, and hit OK.
    Then you should be good to go.

    It looks like you are using an Apple computer. I don't know if it's different on an Apple computer.
    Last edited by jrevball; 03-02-2020 at 10:54 PM.

  10. #10
    Registered User
    Join Date
    05-22-2019
    Location
    USA
    MS-Off Ver
    Office 365
    Posts
    66

    Re: Complicated Macro

    I am sorry but I can't find "Microsoft Scripting Runtime" in my Mac. I am sorry, but it is still not working.

  11. #11
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Complicated Macro

    Dictionary will not work on Mac OS.
    jrevball,

    Administrative Note:

    Please take a moment to add the tags. Posting code between tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

    Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here
    Last edited by jindon; 03-03-2020 at 12:19 AM.

  12. #12
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Easy VBA …

    (code removed 'cause of moderator request,
    can be done just with easy Excel basics like a beginner can operate manually : an advanced filter and a SUMIFS formula with less than 20 codelines …)
    Last edited by Marc L; 03-03-2020 at 12:03 AM.

  13. #13
    Registered User
    Join Date
    05-22-2019
    Location
    USA
    MS-Off Ver
    Office 365
    Posts
    66

    Re: Complicated Macro

    Thank you for your instructions. I am still new to this forum and still learning how to operate.

  14. #14
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Macro to sort data and add totals

    Thanks for the title.
    This uses Collection object instead.
    Please Login or Register  to view this content.

  15. #15
    Registered User
    Join Date
    05-22-2019
    Location
    USA
    MS-Off Ver
    Office 365
    Posts
    66

    Re: Macro to sort data and add totals

    THANK YOU! This is very helpful. It works.
    Last edited by manuscriptguru; 03-03-2020 at 12:42 AM.

  16. #16
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Macro to sort data and add totals

    Multiplier is determinded based on
    2) It will calculate =IF(D3="Check", C3-(C3*0.08),C3-(C3*0.11).
    So if Col.D is "Check", 0.08 else 0.11 and should be added within a same Campaign.
    Please Login or Register  to view this content.
    So, myVal is the result of calculation above...

    Am I missing something?

  17. #17
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Macro to sort data and add totals

    Please Login or Register  to view this content.
    Figures in right most column is a calculated value, so sum = 1095.07, not 1211.

    I need to go out soon, so sort part will be done later...

  18. #18
    Registered User
    Join Date
    10-29-2012
    Location
    US
    MS-Off Ver
    Excel 365 ProPlus
    Posts
    11

    Re: Macro to sort data and add totals

    Jindon,
    Thanks for showing me how to format the code. I'm also new to posting on the forum.
    My next approach was going to be the use of a collection as you used. I wasn't aware dictionary objects couldn't be used in Mac Excel, and wasn't aware it was for a mac application.
    Nice efficient code!

  19. #19
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Cool Try this …


    As a reminder, I never give any code by PM, you just must create a thread as any forum expects for …

    According to your attachment a Collection / Dictionary is a bit superfluous here
    as yet stated this can be done just in two Excel basics steps like
    any beginner can operate manually via an advanced filter and an easy SUMIFS formula.

    So as a beginner starter the VBA demonstration below well works for the smart enough readers,
    you just must paste it to the source 'Before' worksheet module (so obviously not in a standard module) :

    PHP Code: 
    Sub Demo1()
         
    Dim F$
             
    Workbooks.Add
        With Me
    .UsedRange.Columns
            
    .Item(2).AdvancedFilter xlFilterCopy, , ActiveSheet.[A1], True
            
    .Cells(3).Copy ActiveSheet.[B1]
             
    "SUMIFS(" & .Item(3).Address(External:=True) & "," & .Item(2).Address(External:=True) & ",A2," _
                           
    & .Item(4).Address(External:=True) & ","""
        
    End With
        With ActiveSheet
    .UsedRange
                 
    .Columns(1).AutoFit
                 
    .Columns(1).Sort .Cells(1), xlAscendingHeader:=xlYes
            With 
    .Range("B2:B" & .Rows.Count)
                 .
    Formula "=" "Check"")*0.92+" "<>Check"")*0.89"
                 
    .Formula = .Value2
            End With
        End With
    End Sub 
    ► Do you like it ? ► ► So thanks to click on bottom left star icon « Add Reputation » !

  20. #20
    Registered User
    Join Date
    05-22-2019
    Location
    USA
    MS-Off Ver
    Office 365
    Posts
    66

    Re: Macro to sort data and add totals

    Can I please ask for two adjustments to the macro? Below are the adjustments:
    1) The equation in step 2 (in the original post) I want it to be =IF(OR(D3="check",D3=""), C3-(C3*0.08),C3-(C3*0.11)).
    2) I want the macro to create a new Excel sheet, but it does not need to save somewhere. Just create the sheet, then I will save it manually somewhere.

    Thank you for your help.

  21. #21
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Macro to sort data and add totals

    Quote Originally Posted by jrevball View Post
    Jindon,
    I wasn't aware dictionary objects couldn't be used in Mac Excel, and wasn't aware it was for a mac application.
    Mac doesn't support ActiveX objects except objects used on a UserForm.

    Quote Originally Posted by manuscriptguru View Post
    1) The equation in step 2 (in the original post) I want it to be =IF(OR(D3="check",D3=""), C3-(C3*0.08),C3-(C3*0.11)).
    2) I want the macro to create a new Excel sheet, but it does not need to save somewhere. Just create the sheet, then I will save it manually somewhere.
    Change to
    Please Login or Register  to view this content.

  22. #22
    Registered User
    Join Date
    05-22-2019
    Location
    USA
    MS-Off Ver
    Office 365
    Posts
    66

    Re: Macro to sort data and add totals

    I keep getting #VALUE! in the new sheet with no results. Any idea?

  23. #23
    Registered User
    Join Date
    05-22-2019
    Location
    USA
    MS-Off Ver
    Office 365
    Posts
    66

    Re: Macro to sort data and add totals

    Never-mind
    , i found the problem. I started from row 5 instead of 2. Thank you!!!

  24. #24
    Registered User
    Join Date
    05-22-2019
    Location
    USA
    MS-Off Ver
    Office 365
    Posts
    66

    Re: Macro to sort data and add totals

    Hello,
    Can I ask you to adjust this macro to add two more columns to the new sheet?

    The first added column equals the total amounts of donations in column C of the original sheet that belongs to a specific campaign * 0.08.

    The Second added column equals the total amounts of donations in column C of the original sheet that belongs to a specific campaign and the method is "credit card" * 0.03.

    I hope this is not too much to ask.

  25. #25
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Macro to sort data and add totals

    I need to see your workbook with before/after to avoid misunderstanding.

  26. #26
    Registered User
    Join Date
    05-22-2019
    Location
    USA
    MS-Off Ver
    Office 365
    Posts
    66

    Re: Macro to sort data and add totals

    Thank you for your reply.

    The original information will be manually populated into the attached Excel sheet (titled: Total Payouts). Then I run the macro.
    The result of running the macro is creating a new sheet with two columns.

    What I want to do is that I want the new sheet to have 2 additional columns as described above. After adjusting the macro, the Excel sheet will look like the one attached here and titled "Book1"

    I hope this makes sense.
    Attached Files Attached Files

  27. #27
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Macro to sort data and add totals

    Can not test the code without data.

    Need to see the data and the result.

  28. #28
    Registered User
    Join Date
    05-22-2019
    Location
    USA
    MS-Off Ver
    Office 365
    Posts
    66

    Re: Macro to sort data and add totals

    Hello Jindon,

    Attached are three files:

    1) The first file contains the data I need to analyize. It is titled "Total Payouts"

    2) The seocnd file shows you the current outcome when I run the macro. Titled "current outcome"

    3) The third file shows you what I want the macro to do. Titled "New Desired outcome"

    I hope this is helpful.

    Thank you for your help.
    Attached Files Attached Files

  29. #29
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Macro to sort data and add totals

    Hope this outputs correctly.
    Please Login or Register  to view this content.

  30. #30
    Registered User
    Join Date
    05-22-2019
    Location
    USA
    MS-Off Ver
    Office 365
    Posts
    66

    Re: Macro to sort data and add totals

    That is absoloutly perfect! You are such a life saver!!!!!! THANK YOU SOOOOO MUCH

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Complicated look up macro possible?
    By TravisB in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-25-2012, 04:29 PM
  2. Complicated Calculation Macro
    By DarkChocobo in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-29-2011, 12:04 PM
  3. Complicated Macro Help
    By einstein9999999 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-15-2010, 04:53 PM
  4. Creating a complicated macro
    By nacho in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-18-2007, 11:48 PM
  5. Very complicated macro
    By DazHAT in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 11-29-2006, 06:14 AM
  6. HELP with complicated macro
    By JackR in forum Excel General
    Replies: 0
    Last Post: 03-20-2006, 03:10 PM
  7. [SOLVED] How do I do this complicated macro???
    By tweacle in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-04-2006, 01:10 PM
  8. Complicated Macro
    By =LEN(NICK) in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-18-2005, 10:39 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1