Sorting Request Help Needed

rsrasc · #1 10-14-2015, 01:37 PM

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.

macropod · #2 10-14-2015, 03:01 PM

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

rsrasc · #3 10-15-2015, 12:40 AM

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

macropod · #4 10-15-2015, 01:09 AM

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.

rsrasc · #5 12-28-2015, 06:59 AM

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!!!

macropod · #6 12-28-2015, 09:48 PM

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

Note that there are additional code lines interspersed with these that you'd need even if not deleting the prefix #s.

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

rsrasc · #7 12-29-2015, 02:30 AM

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!

macropod · #8 01-03-2016, 11:54 PM

In that case, you should be able to delete the code blocks indicated in my previous post.

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