find a row index of a number that occurred first

Question

I have A column that has values in random order like

       A column
           2
           3
           4
           2
           5
           6
           4
           3
           4

I want the row index of a particular number that occurred first. say if i say the number is 4 the value returned should be 3

I also want the row index of a particular number that occured last.say if i say the number is 3 then the value returned must be 8

I was thinking Vlookup or find function must do the task but unable to put them in order.please help me with these

Answer 1

Hi friend you may use macros to do this

Use the following code

Sub FindNumbers()

    Sheet1.Range("B:D") = ""
    Application.ScreenUpdating = False
    tot = Sheet1.Range("A1048575").End(xlUp).Row
    i = 1
    k = 1
    m = 1
    n = 1
    o = 1
    p = 1

    For i = 1 To tot
            c = Application.WorksheetFunction.CountIf(Sheet1.Range("B:B"), Sheet1.Range("A" & i).Value)
            If c <= 0 Then
            Sheet1.Range("B" & k).Value = Sheet1.Range("A" & i).Value
            k = k + 1
            End If
    Next

    tots = Sheet1.Range("B1048575").End(xlUp).Row

    For m = 1 To tots
        For n = 1 To tot
            If Sheet1.Range("B" & m).Value = Sheet1.Range("A" & n).Value Then
            Sheet1.Range("D" & m).Value = n
            End If
        Next
    Next

    For o = 1 To tots
        For p = 1 To tot
            If Sheet1.Range("B" & o).Value = Sheet1.Range("A" & p).Value Then
            Sheet1.Range("C" & o).Value = p
            p = tot
            End If
        Next
    Next

    Application.ScreenUpdating = True

End Sub

How to use the code? (In case you are new to macro)

1. Open a new excel file

2. Press Alt + F11

3. Insert a new module

4. Paste the code into the module

5. Go back to the excel sheet and add a button

6. Assign the macro ‘FindNumbers’ to the button

7. Save excel in .xlsm format if you are using excel 2007 or 2010

8. Help

9. Column A: Enter your data in column A and click the button or run the macro

10. Press the button or run the macro

Your result will be like this

Column B: Numbers that are unique in the data entered in Column A

Column C: First occurrence of data

Column D: Last occurrence of data

Answer 2

My examples are looking for the number 3 but it is easy to adapt.

To find the first occurence, you can use:

=MATCH(3,A:A,0)

To find the last one, you can use an array formula (validate with Ctrl+Shift+Enter)

{=MAX(IF(A1:A10=3,ROW(A1:A10),0))}

Note that you could also have used an array formula for the first one with a MIN but it would be quite complicated for what it's worth.