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

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

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 <[email protected]>,
[email protected] 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 <[email protected]>,
[email protected] 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!!
>
>