+ Reply to Thread
Results 1 to 8 of 8

Sorting data using roman numerals....

  1. #1
    Hokie Bear
    Guest

    Sorting data using roman numerals....

    Is there any way to sort data using Roman numerals as the "sort by" column?
    Excel treats it as text and places IX before V. I can make it work by adding
    a column and manually converting the Roman numerals to regular numbers and
    sorting on that, but for some of my reports, that too much data to manually
    convert.

    Come on guys, make me look like a hero!!

  2. #2
    JEV
    Guest

    RE: Sorting data using roman numerals....

    I'm thinking you could build a lookup table once to add a converted column of
    arabic or regular numbers for sorting purposes on all your worksheets.

    Going forward you could use arabic numbers and use the ROMAN function to
    convert to the roman numerals. You'd have both in one worksheet.

    Maybe you can build a custom list and use that for your sort order...

    Hope some of these ideas help,
    Jeanine

    "Hokie Bear" wrote:

    > Is there any way to sort data using Roman numerals as the "sort by" column?
    > Excel treats it as text and places IX before V. I can make it work by adding
    > a column and manually converting the Roman numerals to regular numbers and
    > sorting on that, but for some of my reports, that too much data to manually
    > convert.
    >
    > Come on guys, make me look like a hero!!


  3. #3
    Gary's Student
    Guest

    RE: Sorting data using roman numerals....

    You will need to convert the Roman numerals into numbers that Excel can sort.
    See:

    http://groups-beta.google.com/group/...bd85d9e5?hl=en
    --
    Gary's Student


    "Hokie Bear" wrote:

    > Is there any way to sort data using Roman numerals as the "sort by" column?
    > Excel treats it as text and places IX before V. I can make it work by adding
    > a column and manually converting the Roman numerals to regular numbers and
    > sorting on that, but for some of my reports, that too much data to manually
    > convert.
    >
    > Come on guys, make me look like a hero!!


  4. #4
    Valued Forum Contributor
    Join Date
    06-30-2005
    Location
    Verwood, Dorset, England
    MS-Off Ver
    Excel 2000
    Posts
    479
    Quote Originally Posted by Hokie Bear
    Is there any way to sort data using Roman numerals as the "sort by" column?
    Excel treats it as text and places IX before V. I can make it work by adding
    a column and manually converting the Roman numerals to regular numbers and
    sorting on that, but for some of my reports, that too much data to manually
    convert.

    Come on guys, make me look like a hero!!
    Hi Hokie Bear

    Instead of manually converting the Roman numerals why not use a lookup table

    In a separate area of the sreadsheet create a table with Col 1 Roman Numerals Column 2 Regular numbers

    then in the column next to your roman numerals use the VLOOKUP function to return the value for you
    Paul

  5. #5
    Registered User
    Join Date
    08-18-2005
    Posts
    59

    RE: Sorting data using roman numerals....

    Hokie Bear,

    Here's an excel file (attached) in which I crafted a macro that will sort a selected group of cells using Roman numerals.

    It's another option for you to explore.
    Attached Files Attached Files
    Excel_Geek
    http://blog.excelgeek.com/

    "...I'll do that in Excel for $50..."

  6. #6
    Hokie Bear
    Guest

    RE: Sorting data using roman numerals....

    Thanks to all for the quick response. I think a VLOOKUP table is the way to
    go. While I'd hoped for an easier solution (i.e. a way for excel to
    recognize Roman numerals by formatting or something), a VLOOKUP table is the
    least painful.

    This is my first experience on this board and while I'm unsure about using
    macros from unknown people, I appreciated those responses also.

    Thanks, again!

  7. #7
    Dave Peterson
    Guest

    Re: Sorting data using roman numerals....

    If you want a UDF to convert those Roman numerals to Arabic:

    I don't remember who wrote this, but I grabbed this a few years ago:


    Option Explicit
    Function Arabic(Roman)
    'Declare variables
    Dim Arabicvalues() As Integer
    Dim convertedvalue As Long
    Dim currentchar As String * 1
    Dim i As Integer
    Dim message As String
    Dim numchars As Integer

    'Trim argument, get argument length, and redimension array
    Roman = LTrim(RTrim(Roman))
    numchars = Len(Roman)
    If numchars = 0 Then 'if arg is null, we're outta here
    Arabic = ""
    Exit Function
    End If

    ReDim Arabicvalues(numchars)
    'Convert each Roman character to its Arabic equivalent
    'If any character is invalid, display message and exit
    For i = 1 To numchars
    currentchar = Mid(Roman, i, 1)
    Select Case UCase(currentchar)
    Case "M": Arabicvalues(i) = 1000
    Case "D": Arabicvalues(i) = 500
    Case "C": Arabicvalues(i) = 100
    Case "L": Arabicvalues(i) = 50
    Case "X": Arabicvalues(i) = 10
    Case "V": Arabicvalues(i) = 5
    Case "I": Arabicvalues(i) = 1
    Case Else
    Arabic = "Sorry, " & Roman & " is not a valid Roman numeral! "
    Exit Function
    End Select
    Next i

    'If any value is less than its neighbor to the right,
    'make that value negative
    For i = 1 To numchars - 1
    If Arabicvalues(i) < Arabicvalues(i + 1) Then
    Arabicvalues(i) = Arabicvalues(i) * -1
    End If
    Next i
    'Build Arabic total
    For i = 1 To numchars
    Arabic = Arabic + Arabicvalues(i)
    Next i

    End Function

    Then you could use a column of helper cells to get the numeric value and sort
    your range by that.



    Hokie Bear wrote:
    >
    > Is there any way to sort data using Roman numerals as the "sort by" column?
    > Excel treats it as text and places IX before V. I can make it work by adding
    > a column and manually converting the Roman numerals to regular numbers and
    > sorting on that, but for some of my reports, that too much data to manually
    > convert.
    >
    > Come on guys, make me look like a hero!!


    --

    Dave Peterson

  8. #8
    Tushar Mehta
    Guest

    Re: Sorting data using roman numerals....

    In article <43062887.F85C0F86@verizonXSPAM.net>,
    petersod@verizonXSPAM.net says...
    > If you want a UDF to convert those Roman numerals to Arabic:
    >
    > I don't remember who wrote this, but I grabbed this a few years ago:

    {snip}
    >
    > 'If any value is less than its neighbor to the right,
    > 'make that value negative
    >

    While there is probably no harm in this rule, from what I recall, there
    were certain combinations that were actually disallowed. I believe
    Harlan Grove posted a solution that took that into account.
    Unfortunately(?), it was a typical Harlan, i.e., a complicated formula.

    --
    Regards,

    Tushar Mehta
    www.tushar-mehta.com
    Excel, PowerPoint, and VBA add-ins, tutorials
    Custom MS Office productivity solutions

    In article <43062887.F85C0F86@verizonXSPAM.net>,
    petersod@verizonXSPAM.net says...
    > If you want a UDF to convert those Roman numerals to Arabic:
    >
    > I don't remember who wrote this, but I grabbed this a few years ago:
    >
    >
    > Option Explicit
    > Function Arabic(Roman)
    > 'Declare variables
    > Dim Arabicvalues() As Integer
    > Dim convertedvalue As Long
    > Dim currentchar As String * 1
    > Dim i As Integer
    > Dim message As String
    > Dim numchars As Integer
    >
    > 'Trim argument, get argument length, and redimension array
    > Roman = LTrim(RTrim(Roman))
    > numchars = Len(Roman)
    > If numchars = 0 Then 'if arg is null, we're outta here
    > Arabic = ""
    > Exit Function
    > End If
    >
    > ReDim Arabicvalues(numchars)
    > 'Convert each Roman character to its Arabic equivalent
    > 'If any character is invalid, display message and exit
    > For i = 1 To numchars
    > currentchar = Mid(Roman, i, 1)
    > Select Case UCase(currentchar)
    > Case "M": Arabicvalues(i) = 1000
    > Case "D": Arabicvalues(i) = 500
    > Case "C": Arabicvalues(i) = 100
    > Case "L": Arabicvalues(i) = 50
    > Case "X": Arabicvalues(i) = 10
    > Case "V": Arabicvalues(i) = 5
    > Case "I": Arabicvalues(i) = 1
    > Case Else
    > Arabic = "Sorry, " & Roman & " is not a valid Roman numeral! "
    > Exit Function
    > End Select
    > Next i
    >
    > 'If any value is less than its neighbor to the right,
    > 'make that value negative
    > For i = 1 To numchars - 1
    > If Arabicvalues(i) < Arabicvalues(i + 1) Then
    > Arabicvalues(i) = Arabicvalues(i) * -1
    > End If
    > Next i
    > 'Build Arabic total
    > For i = 1 To numchars
    > Arabic = Arabic + Arabicvalues(i)
    > Next i
    >
    > End Function
    >
    > Then you could use a column of helper cells to get the numeric value and sort
    > your range by that.
    >
    >
    >
    > Hokie Bear wrote:
    > >
    > > Is there any way to sort data using Roman numerals as the "sort by" column?
    > > Excel treats it as text and places IX before V. I can make it work by adding
    > > a column and manually converting the Roman numerals to regular numbers and
    > > sorting on that, but for some of my reports, that too much data to manually
    > > convert.
    > >
    > > Come on guys, make me look like a hero!!

    >
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1