How to sort a list to a table in word

aptremblay · #1 06-16-2021, 07:01 AM

I am new to the world of macros and I am trying to find a way to sort a list of this sort:
Anaheim Ducks 17$ Emily
Arizona Coyotes 20$ Max
Boston Bruins 53$ Jim
Buffalo Sabres 62$
Calgary Flames 8$ Bob
Carolina Hurricanes 26$ Alex
Chicago Blackhawks 80$ Sam
Colorado Avalanche 86$ Sam

Columbus Blue Jackets 56$ Tyler
Dallas Stars 68$ Chris
Detroit Red Wings 29$ Al
Edmonton Oilers 77$ Bob
Florida Panthers 14$ Miguel
...
would be sorted in a table where the name of the person next to the $ is found in the same cell but under the team's name.
Is there a way to do it without having to type every single name under the team they selected?
I joined a word document including my table and the fictive list that can be found above.

Guessed · #2 06-16-2021, 06:50 PM

If this is a fictional list then you need to be more specific about what you need. The Table and the list are already sorted so that can't be what you wanted

Do you actually want the text from the list below the table to be put into the relevant cells? How are you planning on showing the macro where this list is? Is the table always going to be the first table in the document?

aptremblay · #3 06-17-2021, 07:31 AM

Quote:

Originally Posted by Guessed

If this is a fictional list then you need to be more specific about what you need. The Table and the list are already sorted so that can't be what you wanted

Do you actually want the text from the list below the table to be put into the relevant cells? How are you planning on showing the macro where this list is? Is the table always going to be the first table in the document?

Okay so basically, I want to find a way to put the names of the people from the list, in the table (appropriate cells) without having to type the names. By fictional, I meant that the prices and the names are fictional just so that I don't expose the names of my clients. And like I said, I just started using macros so I have no clue on what would be appropriate to do. The table doesn't have to be at that specific place, I just want you guys' advice on the most efficient way to do that

Guessed · #4 06-17-2021, 05:24 PM

Approaching this as a coding exercise, there are a steps to break down the problem into bite sized chunks.
1. How do we define the source data range
2. How do we iterate through the source paragraphs to process each one
3. How do we separate the key (team name) from the value (##$ Person)
4. How do we define the table where the data goes and locate the relevant cell in the table
5. How do we add the value in that cell

I'll give you the first couple of steps to get you started.
1. There are numerous ways to achieve this. The simplest is to expect the user to select the source range before running the macro.
2. This is relatively easy because each data set is a paragraph within that range.

Combining both steps 1 and 2 we have this code

Code:

Sub ILike2MoveIt()
  Dim aRng As Range, aPara As Paragraph
  Set aRng = Selection.Range  
  For Each aPara In aRng.Paragraphs
    Debug.Print aPara.Range.Text  'this writes to the Immediate window
  Next aPara
End Sub

The next step is to work out how to separate the team name from the value you want to move. This is kind of complicated because you haven't separated it with anything other than a space and the point at which this begins varies from paragraph to paragraph. So your homework is to work out a clean way to separate the key and value.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Sort Excel table using a list.	mthwi	Excel Programming	1	05-16-2020 05:57 AM
Can't sort list in report	Sharif	Project	0	10-21-2019 03:02 AM
Sort items in a list into various customers for a report	Lluewhyn	Excel	5	10-21-2016 05:56 AM
list of documents come up in random order - any ideas how to sort this?	tallscot49	Word	0	07-18-2010 12:40 AM
Sort By Last Name in a List	eliz.bell	Word	8	05-07-2009 01:33 PM