Consulting

Results 1 to 5 of 5

Thread: Calculate percentages in Excel, select data, and merge into Word?

  1. #1
    VBAX Regular
    Joined
    Feb 2015
    Posts
    11
    Location

    Calculate percentages in Excel, select data, and merge into Word?

    Hi, I'm not asking anyone to figure this out, but just wanting to know if it's possible so I can suggest it to someone! I would like to know if one has a table of items and amounts, with the amounts in columns by year, along with the percentage change (say, from 2020 to 2021), like so...

    2019 2020 2021
    Item 1 amount amount amount percentage change
    Item 2 amount amount amount percentage change
    Item 3 amount amount amount percentage change
    Item 4 amount amount amount percentage change

    Also, one has some boilerplate MS word text that says something like the following: "The amount of Item 1 produced in 2021 increased by X% compared with that of 2020."

    I would like Excel to determine what amounts increased by more than 10% and then somehow magically produce text that says "The amount of Item 1 produced in 2021 increased by X% compared with that of 2020; Item 2, XX%: and Item 3, XX%." It would have to know to insert the correct number of items, and the percentage increases should be in decreasing order in the text. If necessary, there could be boilerplate text in the Word document that could allow for 10 or more item increases and then the user could manually delete the extraneous blank items if only 6 increased by more than 10%, but that would be less helpful. Finally, if it would be easier for the text to be created in Excel and then transferred to Word, that would be fine also.

    What I'm most interested in is the automated nature of this task and the resulting accuracy. Does it seem that this would be possible to do with a VBA script or is some other sort of software needed? Or is it even possible at all?

    Sorry for the length of this question, and thanks in advance for advice!
    Last edited by jish; 02-13-2021 at 10:33 AM. Reason: formatting was messed up

  2. #2
    VBAX Expert Dave's Avatar
    Joined
    Mar 2005
    Posts
    829
    Location
    Hi jish. I see U haven't had much luck with this. Yes it's very doable and XL is only as accurate as U tell it to be. If U provide a sheet and table name and the cell addresses of the data, I'm guessing someone would be able to VBA U something up. I would suggest just creating a Word document on the fly rather than using a template being that the amount of data transfer is variable. If there is else U want in the Word doc other than the ordered statements "The amount of Item 1 produced in 2021 increased by X% compared with that of 2020." is also needed along with what the resulting Word doc file would be. Good luck. HTH. Dave

  3. #3
    VBAX Regular
    Joined
    Feb 2015
    Posts
    11
    Location
    Hi, Dave---

    Thanks very much for your thoughtful reply.

    The “automated” paragraph I would be trying to create is part of a writeup that summarizes information in an accompanying set of tables—there are hundreds of writeups about hundreds of sets of tables, all with unique names. I'm a lazy editor and would like to suggest to the authors that they could have the software transfer information directly from a selected spreadsheet into a Word document automatically to ensure the accuracy of the information and keep me from having to cross-check each percentage manually…ugh.). It sounds as though it’s reasonable to at least mention it to the authors as something to explore, although the macro would also need to figure out which information to extract (any change greater than 10%).

    Thanks again for taking the time to reply! I’m very grateful.

  4. #4
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    Assuming you have the table in Word, such math and cross-referencing are well within Word's capabilities. To see how to do a wide range of calculations in Word, check out my Microsoft Word Field Maths Tutorial, at:
    https://www.msofficeforums.com/word/...-tutorial.html
    or:
    http://www.gmayor.com/downloads.htm#Third_party

    Otherwise, for a table in Excel, what you're describing can all be done with mailmerge.
    Last edited by macropod; 02-15-2021 at 07:23 PM.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  5. #5
    VBAX Regular
    Joined
    Feb 2015
    Posts
    11
    Location
    Thank you! Your tutorial looks very helpful. I'm not sure that it will help with this particular issue but it will definitely help with other ones---so, great! I was not able to access the gmayor.com link. I will check into the mailmerge option. Thanks, again!

Tags for this Thread

Posting Permissions

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