CODEWITHSUNDEEP
excel
abberto
abberto

Reputation: 47

Excel - Find cell containing nth occurrence of data

I have a set of data in Excel that looks similar to this:

24/01/2020      25/01/2020      26/01/2020      27/01/2020
Item A          Item A          Item B          Item C
Item B          Item C          Item C          Item D
Item C                          Item D

I can run a formula to ascertain how many times a certain item occurs in the dataRange: =COUNTIF(dataRange,"Item C")

What I am now looking to do is get the date associated with each nth occurrence. Everything I have found online only deals with finding the nth occurrence in a single column, whereas I would like a formula that would tell me that the cell for each occurence in the full range. E.G. 1st occurrence of 'Item C' is in cell A4, and the 2nd occurrence is B3, the third is C3 and the 4th is in cell D2.

Thank you all!

Upvotes: 0

Views: 125

Answers (2)

Error 1004
Error 1004

Reputation: 8220

You could also try Worksheet Change Event:

Import the Item_Code in Cell G1

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Me.Range("G1")) Is Nothing Then

        Dim i As Long, j As Long, Counter As Long, LastRow As Long
        Dim ItemCode As String

        Counter = 1

        With Me

            ItemCode = Target.Value

            For i = 1 To 4

                For j = 2 To .Cells(.Rows.Count, i).End(xlUp).Row

                    If .Cells(j, i).Value = ItemCode Then

                        LastRow = .Cells(.Rows.Count, "I").End(xlUp).Row + 1

                        Application.EnableEvents = False

                            .Range("I" & LastRow).Value = .Cells(1, i).Value
                            .Range("J" & LastRow).Value = Counter
                            .Range("K" & LastRow).Value = .Cells(j, i).Address

                        Application.EnableEvents = True

                        Counter = Counter + 1

                    End If


                Next j

            Next i

        End With

    End If

End Sub

enter image description here

Upvotes: 0

abberto
abberto

Reputation: 47

Thanks to @JvdV for the assistance with this. Using the Microsoft Docs I was able to find a combination of Range.Find and FindNext, resulting in the following:

Sub cellAddresses()

    With Range("A1:D6")

        Set c = .Find("Item A", LookIn:=xlValues)
        If Not c Is Nothing Then
            firstAddress = c.Address
            Do
                MsgBox c.Address
                Set c = .FindNext(c)
                Loop While c.Address <> firstAddress
        End If
    End With

End Sub

This returns the cell addresses for each occurrence and ensures that once all occurences have been looped through, no further information is fed back.

Thanks again!

Upvotes: 1

Related Questions