Sorting data using roman numerals....

[Guest]

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

 

[Guest]

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

 

[Guest]

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

http://groups-beta.google.com/group...orksheet.functions/msg/63126e7ebd85d9e5?hl=en

 

[Paul Sheppard]

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

 

[Excel_Geek]

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

 

[Guest]

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!

 

[Dave Peterson]

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.

 

[Tushar Mehta]

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