+ Reply to Thread
Results 1 to 13 of 13

Summarise few columns when the name match - various rows and jobs

  1. #1
    Registered User
    Join Date
    03-27-2019
    Location
    Brisbane
    MS-Off Ver
    2016
    Posts
    19

    Question Summarise few columns when the name match - various rows and jobs

    Hi all

    I am new to VBA, I have tried some of the metholds but it's seems not work for me.

    I have a list of individuals(100+), each individuals have various shifts(100+).
    I want to summarise the shift data for each individual in a cell below the last row.
    When the shift name match, I want to add up the quantity of hours and the total cost of the matched shifts.

    In example file:
    Column A-G are the Sample data, Column J-P are the result I am looking for.

    Thank you so much ^^

    Please let me know if you need more information, cheers
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Summarise few columns when the name match - various rows and jobs

    Since it is VBA, I can't help you enough with your question.

    But I bet it will be appreciated if you add manualy the expected result in your excel file.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    42,129

    Re: Summarise few columns when the name match - various rows and jobs

    The OP said this:

    In example file:
    Column A-G are the Sample data, Column J-P are the result I am looking for.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  4. #4
    Valued Forum Contributor
    Join Date
    08-31-2015
    Location
    Kuwait
    MS-Off Ver
    365
    Posts
    825

    Re: Summarise few columns when the name match - various rows and jobs

    Hi bb1228,

    Try the below code based on your sample file ...

    Please Login or Register  to view this content.
    Last edited by nankw83; 08-02-2020 at 03:39 PM. Reason: Code adjustment
    If I was able to help, you can thank me by clicking the * Add Reputation under my user name

  5. #5
    Registered User
    Join Date
    03-27-2019
    Location
    Brisbane
    MS-Off Ver
    2016
    Posts
    19

    Re: Summarise few columns when the name match - various rows and jobs

    Thank you so much nankw83 , it works very well.

    I should have a long way to learn on VBA

  6. #6
    Valued Forum Contributor
    Join Date
    08-31-2015
    Location
    Kuwait
    MS-Off Ver
    365
    Posts
    825

    Re: Summarise few columns when the name match - various rows and jobs

    Glad to help & thanks for the reps

  7. #7
    Registered User
    Join Date
    03-27-2019
    Location
    Brisbane
    MS-Off Ver
    2016
    Posts
    19

    Re: Summarise few columns when the name match - various rows and jobs

    Hi nankw83

    I would like to change the column from 1, 3, 5, 7 to 21, 27, 29, 30
    Which part of the code do I need to change? I tried to change the column number in the first FOR loop but it does not work.

    Can I please ask if you can put in some simple explanations in the code? thanks

  8. #8
    Valued Forum Contributor
    Join Date
    08-31-2015
    Location
    Kuwait
    MS-Off Ver
    365
    Posts
    825

    Re: Summarise few columns when the name match - various rows and jobs

    Hi bb1228,

    Try below code ... I have tried to put as much comments as I could, hope that helps

    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    03-27-2019
    Location
    Brisbane
    MS-Off Ver
    2016
    Posts
    19

    Re: Summarise few columns when the name match - various rows and jobs

    Quote Originally Posted by nankw83 View Post
    Hi bb1228,

    Try below code ... I have tried to put as much comments as I could, hope that helps

    Please Login or Register  to view this content.
    Thank you so much nankw83 for your comment and updating the codes it's work well, I am really appreaciate

    I am sorry to keep changing, just want to see if you can help me to add one more thing to the last row of each [individual code]
    Could you please add the total of the total column(Column AC) for each individual into the same cell of the calculate data.... sorry for the inconvenience it caused, thanks.

  10. #10
    Valued Forum Contributor
    Join Date
    08-31-2015
    Location
    Kuwait
    MS-Off Ver
    365
    Posts
    825

    Re: Summarise few columns when the name match - various rows and jobs

    Apologies bb1228 ... Forgot to reply back to this thread until I saw your PM today. I have added the last block of the code to add the summation in column AC
    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    03-27-2019
    Location
    Brisbane
    MS-Off Ver
    2016
    Posts
    19

    Smile Re: Summarise few columns when the name match - various rows and jobs

    Hi nankw83

    Thanks for your quick response I am appreciated.
    However, when I tried to apply it to my actual excel with some of my codes,
    it is not work properly.

    It's my bad,I didn't mention it in details.

    When I apply you summary calcuation in to the excel file,
    I will need to run some of my codes to adding one big Header few lines for each individual
    and a small header above each different Employee Code, please see the screenshot below.

    Below is the screenshot after run your codes and my codes, except the red numbers.
    After run code.PNG

    Since the column AC is broken up, I think it may affect your calculation of "Total" codes

    I am so sorry to being a pain. Do you think it's possible to add up the total and put it
    after the word "Summary: "?

    Thanks
    Attached Files Attached Files

  12. #12
    Valued Forum Contributor
    Join Date
    08-31-2015
    Location
    Kuwait
    MS-Off Ver
    365
    Posts
    825

    Re: Summarise few columns when the name match - various rows and jobs

    I have amended the code to add the summary line & total as per your above picture ...
    Please Login or Register  to view this content.
    Last edited by nankw83; 09-18-2020 at 06:11 AM. Reason: Added a code

  13. #13
    Registered User
    Join Date
    03-27-2019
    Location
    Brisbane
    MS-Off Ver
    2016
    Posts
    19

    Re: Summarise few columns when the name match - various rows and jobs

    Thank you so much nankw83

    It took me a night to figure out why it works on the example file but not on my actual file.
    Eventually, I found that the individual column can not be a TEXT type,
    after change the column to NUMBER type. Your codes work perfectly, thanks
    Last edited by bb1228; 09-20-2020 at 07:22 PM.

+ 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. [SOLVED] Add Jobs to rows on Calendar that are on Weekdays only OR Weekends only
    By leovfx in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 09-16-2017, 07:28 AM
  2. [SOLVED] Look down table and add Jobs to rows on calendar (in status priority)
    By leovfx in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-13-2017, 05:37 PM
  3. Correct combination of SUMIF/INDEX/MATCH to summarise monthly totals
    By QuantumP in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-13-2017, 07:10 AM
  4. Replies: 2
    Last Post: 03-14-2012, 09:10 AM
  5. Merge and summarise rows with VBA
    By andy_kerslake in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 02-10-2012, 01:59 PM
  6. Match similar rows from two sheets using three columns to match the "paired" rows
    By sanpedro_nz in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-04-2011, 07:15 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