CODEWITHSUNDEEP
excelformula
Nincs
Nincs

Reputation: 11

Finding previous occurence

I need help in this situation:

Some hundreds of rows of column A is filled in random order with color names (white, blue, green, yellow, red). I need a formula in column B that shows the row number of the previous occurrence of that color in column A.

Example:

A   B
white   0 or not found
yellow  0 or not found
yellow  2
green   0 or not found
white   1
yellow  3 (note: not `2`, which is the first occurrence, `3` is the last)

Upvotes: 0

Views: 784

Answers (2)

Excel Hero
Excel Hero

Reputation: 14764

Please take a look at this formula. It will work but it requires your data to begin in row 2:

enter image description here

Place this formula in cell B2:

=IFERROR(LOOKUP(2,1/(A$1:A1=A2),ROW($1:1)),0)

It is NOT an array formula, so just confirm it normally, with the ENTER key.

Now copy B2 down as far as you need.

Upvotes: 1

zedfoxus
zedfoxus

Reputation: 37129

You could use a VBA function. If you haven't done VBA, you might just have to look up how to create a new module.

In a new module, paste this code:

Public Function FindColorPosition(m_Range As Range) As String

    FindColor = "not found"
    If m_Range.Row = 1 Then
        Exit Function
    End If

    Dim i As Integer
    For i = m_Range.Row - 1 To 1 Step -1
        If m_Range.Value = Range("A" & i).Value Then
            FindColor = Str(i)
            Exit Function
        End If
    Next

End Function

Then, in cell B1 type =FindColorPosition(A1) Then, in cell B2 type =FindColorPosition(A2) and so on...

You will either get not found or the row in which that color was last seen.

Example image

Upvotes: 0

Related Questions