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

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

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

http://groups-beta.google.com/group/microsoft.public.excel.worksheet.functions/msg/63126e7ebd85d9e5?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!!

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 b
> adding
> a column and manually converting the Roman numerals to regular number
> and
> sorting on that, but for some of my reports, that too much data t
> 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 looku
table

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

then in the column next to your roman numerals use the VLOOKUP functio
to return the value for yo

--
Paul Sheppar

-----------------------------------------------------------------------
Paul Sheppard's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2478
View this thread: http://www.excelforum.com/showthread.php?threadid=39723

Hokie Bear,

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

It's another option for you to explore

+-------------------------------------------------------------------
|Filename: For_Hokie Bear.zip                                       
|Download: http://www.excelforum.com/attachment.php?postid=3712     
+-------------------------------------------------------------------

--
Excel_Gee

-----------------------------------------------------------------------
Excel_Geek's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2642
View this thread: http://www.excelforum.com/showthread.php?threadid=39723

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!

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

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