Sorting a block of page number citations

  • Thread starter Thread starter alfred.cloutier
  • Start date Start date
A

alfred.cloutier

If I had a text block in a txt file, and imported it into Access would
I be able to perform the following sort?

I need to sort and delete dupes in a list of page number citations,
all existing in one cell:

1, 1, 3, 5, 7, 9, 2, 2, 4, 6, 8

Needs to appear as

1, 2, 3, 4, 5, 6, 7, 8

In one cell

Please advise if this would be better accomplished in Excel, or VB.

Thanks!

Al
 
Hi Alfred,

Nice question. The answer is to do it in VBA. Here is how:

Go to <http://www.blueclaw-db.com/quick-sort.htm> and copy Janet Loch's
QuickSort code. Paste it into a regular module (not form, report or class).
Change the "Private" on the first line to "Public". There is problem with
this line:

‘ < comparison of the values is a descending sort

Change the grave accent (‘) at the beginning to an apostrophe ('). Add
this code to the same module:

------------------------------------------------------------------------

Public Function FixCitationPages(ByVal strPagesList As String) As String

Dim intIndex As Integer
Dim strPrevious As String
Dim strReturn As String
Dim strarrPages() As String

' Split the stuff into an array
strarrPages = Split(Replace(strPagesList, " ", ""), ",")
intIndex = LBound(strarrPages)
' Sort the array
QuickSort strarrPages, intIndex, UBound(strarrPages)
' Reassemble, skipping duplicates
strReturn = strarrPages(intIndex)
strPrevious = strReturn
Do While intIndex <= UBound(strarrPages)
If strarrPages(intIndex) <> strPrevious Then
strPrevious = strarrPages(intIndex)
strReturn = strReturn & ", " & strPrevious
End If
intIndex = intIndex + 1
Loop
FixCitationPages = strReturn

End Function

------------------------------------------------------------------------

When you need to fix the citation pages simply call the function:

select FixCitationPages(CitationPages)
from tblCitations;

To update your table with the fixed values do something like this:

update tblCitations
set CitationPages = FixCitationPages(CitationPages)
where CitationsPages <> FixCitationPages(CitationPages);

Note that I did not include error checking in the funciton or checking
to see if there was anything to split. I will leave that to you.

Hope that helps,

Clifford Bass
 
Hi Alfred,

Oh bother! I forgot sorting numeric values stored as strings will not
sort numerically. Make these changes. In Janet Loch's code change the first
two lines to:

Public Sub QuickSort(strArray As Variant, intBottom As Integer, intTop As
Integer)
Dim strPivot As Variant, strTemp As Variant

Then replace my function with this version:

---------------------------------------------------------------------

Public Function FixCitationPages(ByVal strPagesList As String) As String

Dim intIndex As Integer
Dim intLowerBound As Integer
Dim intPrevious As Integer
Dim intUpperBound As Integer
Dim intarrPages() As Integer
Dim strReturn As String
Dim strarrPages() As String

' Split the stuff into an array
strarrPages = Split(Replace(strPagesList, " ", ""), ",")
intLowerBound = LBound(strarrPages)
intUpperBound = UBound(strarrPages)
' Transfer the string array to an integer array
ReDim intarrPages(intLowerBound To intUpperBound)
For intIndex = intLowerBound To intUpperBound
intarrPages(intIndex) = CInt(strarrPages(intIndex))
Next intIndex
' Sort the array
QuickSort intarrPages, intLowerBound, intUpperBound
' Reassemble, skipping duplicates
intPrevious = intarrPages(intLowerBound)
strReturn = intPrevious
For intIndex = intLowerBound + 1 To intUpperBound
If intarrPages(intIndex) <> intPrevious Then
intPrevious = intarrPages(intIndex)
strReturn = strReturn & ", " & intPrevious
End If
Next intIndex
FixCitationPages = strReturn

End Function

---------------------------------------------------------------------

Now, when you use it, the pages should sort numerically, instead of as
strings. Note that if you have non-integer page numbers (i.e. 5-1, 12c) it
will not work.

Clifford Bass
 
Back
Top