Sorting page-numbers with ranges (i.e.: 6ff or 7-10 etc.)

roderh · #1 08-24-2016, 04:33 PM

Hi,

I have a excel-file with rows of unsorted page-numbers from a book.

After I select a couple of rows I would like to sort them.

Problem is: there are page-ranges like "14f" or "17ff" or "1141-1432". The excel build-in function cannot do this. I would think this requires VBA, but I cant do it myself.

I have attached two screenshots: first is the unsorted input, second is the desired output.
I have attached a excel-file, too. Desired output is in Worksheet 2. Please note, that the output should be in fact in worksheet 1 (i.e. output should replace the unsorted input), not in worksheet 2.

Thank you very much in advance for your help!

roderh · #2 08-27-2016, 08:40 AM

nobody...?

p45cal · #3 08-31-2016, 05:00 PM

IF the worksheet is like yours with data starting row 1 and IF it's only the'-' and 'f' characters that can be within the page numbers, and IF there are no more than 20 page numbers per row, then try:

Code:

Sub blah()
For rw = Cells(Rows.Count, "A").End(xlUp).Row To 1 Step -1
  Rows(rw).Insert
  With Rows(rw).Resize(, 20)
    .NumberFormat = ""
    .FormulaR1C1 = "=LEFT(R[1]C,MIN(IFERROR(SEARCH(""-"",R[1]C),99),IFERROR(SEARCH(""f"",R[1]C),99))-1)"
    .Resize(2).Sort key1:=Cells(rw, 1), order1:=1, Orientation:=2, DataOption1:=1
  End With
  Rows(rw).Delete
Next rw
End Sub

roderh · #4 09-01-2016, 08:51 PM

Thank you very much for answering and especially for the explanation of the "IFs".
In order to change the "there are no more than 20 page numbers per row"-limitation the only thing I have to do, is to change the number "20" this part: "With Rows(rw).Resize(, 20)", right?

Thank again!

p45cal · #5 09-02-2016, 05:12 AM

Yes, that's right.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Take String of numbers, expand ranges, sort, then compress back into ranges	AustinBrister	Word VBA	19	08-22-2016 05:18 PM
Sorting Numbers in a Document	rsrasc	Word VBA	3	01-04-2016 11:47 PM
Sorting Index According to Page Numbers	mohsen.amiri	Word	14	06-25-2015 09:50 PM
Sorting paragraph numbers	CommDude	Excel	1	01-02-2011 04:38 PM