UDF to give position of item If list was Alpabetic

C

cae

I would like to create a UDF to return the "position" (#)
of an item in a list, if the list "was" sorted
alphabetically.

OR

I have not found an existing combination of Excel
functions that can do this if there is let me know as well.

I am just learning VBA and not quite able to do this on my
own yet. I appreciate the help.
 
F

Frank Kabel

Hi
if your list is in A1:A100 try the following function (non VBA
solution):
=MATCH(value,A1:A100,0)
this returns the index number of 'value' in this list
 
N

Niek Otten

Look at the MATCH() function. More details in HELP

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel
 
C

CAE

Sorry, I should of been more specific.

The current list is "NOT" alphabetic, I hope that the UDF
could tell me the Position # if the list was sorted
Alphabetic.

Currently the NON-Alphabetic list is "Dynamic/changebale"
and generated by formulas, I hope that on a second sheet I
can create an equivalent Alphabetic listing that updates
automatically using the UDF formula in a column on the
first page with a Vlookup on the second page to create
Alpha list)

Thanks
 
B

BrianB

I have noticed the possibility of comparing strings with > and < an
strangely this seems to work. This macro runs down column A and pic
out the highest "value" string.

'-----------------------------------------
Sub test()
Dim MyString As String
Dim Rw As Long
'---------------------
MyString = ""
Rw = 1
'---------------------
While ActiveSheet.Cells(Rw, 1).Value <> ""
If ActiveSheet.Cells(Rw, 1).Value > MyString Then
MyString = ActiveSheet.Cells(Rw, 1).Value
End If
Rw = Rw + 1
Wend
MsgBox (MyString)
End Sub
'----------------------------------------
 
Top