Sorting data using roman numerals....

1. 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. 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. 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. 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

5. 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.

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

>
>

Thread Information

Users Browsing this Thread

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

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