List Nth item from a range.

[Tom.Jones]

Howdy,

I need a UDF to return the nth value from a row or a column.

Ex. I want the 4th item (from left to right) in range A2: P2
or
I want the 5th item (top to down) from range A2: A16

If it were not very complicated, it would be perfect if I had the choice of the opposite, ie from right to left or from bottom to top.

Thank you.

 

[Joe4]

Take a look at the OFFSET function (see: https://exceljet.net/excel-functions/excel-offset-function)
You just enter the starting cell, and how many rows/columns you want to move.
I think that will do what you want.

 

[kvsrinivasamurthy]

Why UDF. Worksheet formulas are sufficient.
For 5th item

=INDEX(A2:P2,5)

For last but 3rd item

=INDEX(A2:P2,COLUMNS(A2:P2)-2)

For 5th item

=INDEX(A2:A16,5)

For last but 3rd item

=INDEX(A2:A16,ROWS(A2:A16)-2)

 

[Tom.Jones]

Thanks for reply Joe4,

If you can I need UDF, like "=UDFname(A2:TRS2, 189)

or =UDFname(A2:A24125, 2889)

Thank you.
@kvsrinivasamurthy

Ned UDF as part of my project

Thank you.

 

[Joe4]

If you can I need UDF, like "=UDFname(A2:TRS2, 189)

or =UDFname(A2:A24125, 2889)

Thank you.
@kvsrinivasamurthy

Ned UDF as part of my project

Part of an assignment? We wouldn't want to do it for you then, right? Just lead you in the correct direction.

If you want to create a UDF to do, you may want to look at the VBA OFFSET function as well. You can probably incorporate that into your UDF.
You will probably want to make sure that the cell you end up in is actually in your original range, i.e. if you try to find the 100th cell in A1:Z1, that would be outside that range.

 

[Tom.Jones]

@ Joe4,

I do not know how to create programs, so I asked for help on this forum.
If I need a VBA code, I search the internet and make small adjustments.
Now I have some codes on the NET and I wanted to integrate that UDF for a project I've been working on for a few months.
It seems like I have asked too much or I have bothered somebody.
I apologize.

Thanks.

 

[Joe4]

It seems like I have asked too much or I have bothered somebody.

Why do you say that? You haven't "bothered" anybody.

Typically, when words like "assignment" are used, and people tell us that it HAS to be done using VBA when simple Excel formulas will work, more often than not it is a homework/school assignment that someone is working on. It is the policy of the board not to do other people's homework assignments for them, as that would be unethical. So we just need to try to make sure that is not the case.

If that is not the case, then I apologize, but in our defense, you really haven't explained why it has to be UDF instead of a formula, especially when you said:

If it were not very complicated, it would be perfect

as a formula solution will be simpler than VBA.

If we have a better understanding of why it needs to be a UDF, it will also help us come up with a better solution. Questions, such as:
- What is the structure of the data?
- How is this going to be used (are they "set" formulas on your sheets, or will people be adding new calculations and need an easy way to do it)?
- Will the data range always have either EXACTLY one row or one column? If you have a range of multiple rows AND columns (i.e. A1:Z5), how does should it work (go across first or go down first)?
will go a long way.

 

[Tom.Jones]

@ Joe4,

I do not want to bother or inappropriate anything with people who help beginners on this forum.
As for the homework, I finished the university 50 years ago )).
Now I'm a retiree who likes excel, and I apply the practice I see on this forum. Everything is for me personally and it only uses me. I no longer have the patience to learn VBA, but I can modify some of the lighter VBA codes found on the internet.
As I said, it's all for my fun. That's why I'm not upset if someone does not help me, knowing that everything is done by the goodwill of some Excel specialists who consume their free time in order to help others.
I have called the "project" an attempt to automate something I "work" (it's a way of saying work because I use some internet codes I'm trying to make them work the way I want, trying to learn VBA ).

Once again, I apologize for making you waste time with me.

 

[Joe4]

No worries, Tom. I am glad to see that you are still having fun with Excel and enjoy playing around with it and doing/learning new things. I enjoy helping not just beginners, but also people who use use it for their own enjoyment!

I was actually playing around with your request and came up with something for you. It assumes that it will always be a single row or single column.
I added lots of documentation to my code so that hopefully you can follow along with my logic.

Function NthItem(rng As Range, itm As Long) As Variant

    Dim numRows As Long
    Dim numCols As Long
    Dim sCell As Range
    Dim fRow As Long
    Dim fCol As Long
    Dim tarCell As Range

'   Get number of rows and columns in range
    numRows = rng.Rows.Count
    numCols = rng.Columns.Count
    
'   Get first cell in range, and row and column of first cell
    Set sCell = rng.Cells(1, 1)
    fRow = sCell.Row
    fCol = sCell.Column

'   Make sure that range is either a single row to single column
    If (numRows > 1) And (numCols > 1) Then
        NthItem = "Range cannot be both multiple columns and multiple rows"
        Exit Function
    End If

'   Check to see if item number is a valid number greater than 0
    If itm < 1 Then
        NthItem = "Invalid item number entered"
        Exit Function
    End If
    
'   If dealing with a single row
    If numRows = 1 Then
'       Find target cell
        Set tarCell = Cells(fRow, fCol + itm - 1)
    Else
'       If dealing with single column
        If numCols = 1 Then
'           Find target cell
            Set tarCell = Cells(fRow + itm - 1, fCol)
        End If
    End If
    
'   Check to see if target cell is in our original range
    If Intersect(rng, tarCell) Is Nothing Then
        NthItem = "Nth Item is greater than number of cells in range"
    Else
'       If it is, return its value
        NthItem = tarCell.Value
    End If
        
End Function

So, it follow the structure you requested:

=UDFname(A2:A24125, 2889)

so it would look like:
=NthITem(A2:A24125, 2889)

 

[shg]

An alternative, which does allow indexing outside the range:

Function Nth(r As Range, n As Long) As Range
  Set Nth = r.Cells(n)
End Function