Office challenge: How do you select multiple instances of the same type of data in Word?

In this week's challenge, learn an Excel array function for summing the top values in a range and see if you know how to shave time off a tedious Word task.

Several times a week, I must reduce a table of contents to just its chapter titles. The Word document contains chapter titles and section headings, but I only need to capture the chapter titles. A simple cut and paste of the chapter titles into a another document works well enough when there's only a few chapters. However, it's a monotonous task I need to perform pretty often. Without using VBA, how can you reduce this task to a few simple clicks? Use whatever feature you like, but I do have a specific solution in mind. Last week we asked: How do you sum the top n values in a range? SouthBayTechWriter was the first to respond with the solution I was thinking of —  using the LARGE() function in an Excel array as follows: =SUM(LARGE(range,{a,b,…})) As you might expect, range identifies the data you want to analyze. The ,{a,b,…} component is a bit more complex — you must enter values, in sequence, up to and including the n top values you want to sum. In other words, if you want to sum the two top values, you'd use {1,2}. For instance, the array function in the spreadsheet shown below sums only the two largest values in C2:C10. Remember, to enter a function as an array, press [Ctrl]+[Shift]+[Enter]. Grahamrice offered a great solution that uses Excel's filtering feature. It's a bit more complicated, but a winner if you need to filter the visible data as well as sum it. I won't repeat the instructions here, as Grahamrice did a great job of explaining it already. Cajonaitis and Steve both suggested simple sorting techniques, which are good for those one-time tasks. Heward.simpson offered another array function that uses INDIRECT(): =SUM(LARGE(data,ROW(INDIRECT("a:z")))) This solution is more dynamic than the one I had in mind because you can incorporate a cell value by specifying a formula for z instead of revamping the formula when you want to change that value — a  clever idea. Thanks to everyone for another great challenge! Now, I have a personal challenge for you: Think you can stump your fellow TechRepublic members? Send us your Office puzzlers (and solutions). We may turn them into future Office challenges!


Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks