Share via

Using macro to format a large report

Anonymous
2020-01-05T13:41:01+00:00

Hi. I receive large reports in Excel that I need to format to be useful. I am writing several functions to handle this, but I am getting stuck. I would like to perform something similar to the Sub-totals function such that on every change in the value in Column A I want to show the:

Value of Columns C - F

Count of column G

Sums of columns M - U

Max value of columns W and X (date values)

I would like these to all be on one line which is bold.

How can I do this? Thanks.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

11 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Jim G 134K Reputation points MVP Volunteer Moderator
    2020-01-08T20:30:44+00:00

    I bet everything you need can be done with a PivotTable. Watch this video:

    Excel Basics 4: PivotTables & SUMIFS Function to Create Summary Reports (Intro Excel #4)

    Was this answer helpful?

    0 comments
  2. Anonymous
    2020-01-08T10:40:33+00:00

    You can use the screenshot for reference. I currently use the Sub-totals feature. The criteria is on change in Org name (Column A).

    For Count of Column G, I want the number of companies per each Organization (Column A).

    For Value of Columns C-F, those cells will be the same for every row in the Org. I just want to copy it to the totals row as well.

    For Sums of columns M - U, I want to sum each column separately.

    I use the total per organization as an indicator of usage for each organization.

    Thanks for your help.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2020-01-08T10:39:20+00:00

    Hi YoramSchwell,

    Do you still need assistance about your Excel issue? Welcome back for any updates.

    Best regards,

    Gloria

    Was this answer helpful?

    0 comments
  4. Anonymous
    2020-01-06T05:51:47+00:00

    Hi YoramSchwell,

    As Dave said, more details can help us better understand your scenario. May I know the exact meanings of “Value of Columns C-F”, “Count of column G”, “Sums of columns M – U” and “Max value of columns W and X (date values)”? Please describe more information for us to further investigate it and we are here to help you and solve it as soon as possible.

    At the same time, could you capture the screenshot of your required worksheet or could you send a sample worksheet for us to better narrow down this?

    Looking forward to your reply and any updates are welcome back to here.

    Regards,

    Amiu

    Was this answer helpful?

    0 comments
  5. DaveM121 891K Reputation points Independent Advisor
    2020-01-05T13:57:11+00:00

    Hi Yoram

    All this should be easy enough to achieve in Excel

    Can you please be a bit more specific on your exact requirements

    For example, Count of Column G, do you want to count non-blank cells in Column G or sum the contents of Column G

    What do you mean by Value of Columns C - F

    Does Sums of columns M - U mean you just want to sum each column individually or together . . .

    Also a screenshot of your spread sheet would also help us to understand your data . . .

    Was this answer helpful?

    0 comments