#1
|
|||
|
|||
Sorting Request Help Needed
I have a file containing a series of multiple questions with their answers. The sample below have only three (3) questions. As you can see they are listed with a sequential number starting with one but the ACA numbers associated to them are not sorted in sequence.
1. ACA-88026 ...... 2. ACA-11001 ...... 3. ACA-05560 ..... After applying the macro or code, the output should looks like the one below (also with their associated text): 1. ACA-05560 2. ACA-11001 3. ACA-88026 As always thanks in advance for your support. Sample text: 1. ACA-88026 Which of the follsosdwing procedures XXXXwoSSSSuld an entity most lSSSSSikely incSSSSlude in its disaster recovery plan? a. xxxCcocncvcecrctc a. b. Maintssfadisns sas stsrojan horse program to prevent illicit activity. c. Devselop an auxiliasfdsssbsrsy sspsty. d. Stodrse dupsddslsiscsastfes ffdcfof. Answer: D Answer (A) xxxx. Answer (B) xxxx. Answer (C) xxxx. Answer (D} xxxx. 2. ACA-11001 Which of the follsosdwing procedures XXXXwoSSSSuld an entity most lSSSSSikely incSSSSlude in its disaster recovery plan? a. xxxCcocncvcecrctc a. b. Maintssfadisns sas stsrojan horse program to prevent illicit activity. c. Devselop an auxiliasfdsssbsrsy sspsty. d. Stodrse dupsddslsiscsastfes ffdcfof. Answer: D Answer (A) xxxx. Answer (B) xxxx. Answer (C) xxxx. Answer (D} xxxx. 3. ACA-05560 Which of the follsosdwing procedures XXXXwoSSSSuld an entity most lSSSSSikely incSSSSlude in its disaster recovery plan? a. xxxCcocncvcecrctc a. b. Maintssfadisns sas stsrojan horse program to prevent illicit activity. c. Devselop an auxiliasfdsssbsrsy sspsty. d. Stodrse dupsddslsiscsastfes ffdcfof. Answer: D Answer (A) xxxx. Answer (B) xxxx. Answer (C) xxxx. Answer (D} xxxx. |
#2
|
||||
|
||||
The prefix numbers you've added makes any sorting difficult, because they will ordinarily end up being part of the sort key. Sorting would have been easier if adding the prefixes had been left till after the questions were sorted. That said, try:
Code:
Sub Demo() Application.ScreenUpdating = False Dim Rng As Range, i As Long With ActiveDocument With .Range Set Rng = .Duplicate .InsertBefore vbCr With .Find .ClearFormatting .Replacement.ClearFormatting .Text = "(^13)[0-9]{1,}. (ACA-[0-9]{5})" .Replacement.Text = "\1\2" .Forward = True .Wrap = wdFindContinue .Format = False .MatchWildcards = True .Execute Replace:=wdReplaceAll .Text = "^13ACA-[0-9]{5}" .Replacement.Text = "" .Wrap = wdFindStop .Execute End With Do While .Find.Found .Start = .Start + 1 Rng.Start = .End With Rng With .Find .Text = "^13ACA-[0-9]{5}" .Replacement.Text = "" .Forward = True .Wrap = wdFindStop .Format = False .MatchWildcards = True .Execute End With If .Find.Found Then Rng.Start = .Start Else Rng.Start = ActiveDocument.Range.End End If End With .End = Rng.Start .ConvertToTable NumColumns:=1, Format:=wdTableFormatNone, AutoFitBehavior:=wdAutoFitWindow .Duplicate.Cells.Merge .Collapse wdCollapseEnd .Find.Execute Loop End With While .Tables.Count > 1 .Tables(1).Range.Characters.Last.Next.Delete Wend With .Tables(1) .Sort .ConvertToText End With With .Range With .Find .Text = "^13ACA-[0-9]{5}" .Replacement.Text = "" .Forward = True .Wrap = wdFindStop .Format = False .MatchWildcards = True .Execute End With Do While .Find.Found i = i + 1 .Start = .Start + 1 .InsertBefore vbCr & i & ". " .Collapse wdCollapseEnd .Find.Execute Loop End With .Range.Characters.First.Delete .Range.Characters.First.Delete End With Application.ScreenUpdating = True End Sub
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
|||
|
|||
Hello Paul,
Not sure what you meant when you said "prefix numbers" but I test your code and it is working great. Thank you so much for your support. v/r |
#4
|
||||
|
||||
The prefix numbers were the ones you had put in front of the ACA. After running the macro, you'll see they've all been re-numbered.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#5
|
|||
|
|||
Hello Paul,
Would it be possible to update for me the macro previously provided for this thread? Basically, what I would like to see is the macro without the coding information pertaining to the prefix numbers? If so, it will help me solve a pending issue that I have with a document. Thank you in advance for your support. Cheers!!! |
#6
|
||||
|
||||
You'll need to explain what you mean. The only code pertaining to the prefix #s you start out with is:
Code:
.Text = "(^13)[0-9]{1,}. (ACA-[0-9]{5})" .Replacement.Text = "\1\2" .Wrap = wdFindContinue .Execute Replace:=wdReplaceAll Subsequently, all the following code reinstates the prefixing: Code:
With .Range With .Find .Text = "^13ACA-[0-9]{5}" .Replacement.Text = "" .Forward = True .Wrap = wdFindStop .Format = False .MatchWildcards = True .Execute End With Do While .Find.Found i = i + 1 .Start = .Start + 1 .InsertBefore vbCr & i & ". " .Collapse wdCollapseEnd .Find.Execute Loop End With
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#7
|
|||
|
|||
First, thank you for your help.
Hope this time I can explain it better. My initial request was based on the possibility to sort the following information (including the prefixes numbers). 1. ACA-88026 ...... 2. ACA-11001 ...... 3. ACA-05560 ..... My second request was for the possibility of sorting the same information without the prefixes, and without having to reinstates the prefixing. ACA-88026 ...... ACA-11001 ...... ACA-05560 ..... So, after applying the macro or code, the output should looks like the one below (also with their associated text): ACA-05560 ACA-11001 ACA-88026 I think with this new information I should be able to try something new with a document that I'm working with. Thanks! |
#8
|
||||
|
||||
In that case, you should be able to delete the code blocks indicated in my previous post.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
A Formula Request | Planner18 | Project | 8 | 01-10-2017 01:16 PM |
Urgent help needed: Sorting/searching fields | cvrm | Excel | 2 | 04-17-2014 05:47 AM |
Formula Help Request | OTPM | Excel | 16 | 12-16-2013 01:57 PM |
I need help with macro...code needed for automatic sorting | chefmate | Excel Programming | 1 | 08-26-2012 01:04 AM |
Formula help request | JAMS | Excel | 2 | 04-06-2012 10:14 PM |