CODEWITHSUNDEEP
excelexcel-formula
asd123
asd123

Reputation: 41

Find address of matching range

In the image below, I am trying to find the address of all matching cells. That is, if OneSource (for example) is found in 3 rows (it will always be continuous rows), then I need to show address like F2:F4.

enter image description here

As you can see in the picture, I have created a formula which gives me the address of the first instance of the match but not the full range.

=CELL("address",INDEX($F$2:$F$12,MATCH(A2,$F$2:$F$12,0),1))

How do I get the full range address?

Upvotes: 0

Views: 475

Answers (3)

user11087823
user11087823

Reputation:

Sorted data allows you to take advantage of binary lookup matches. These are simple to write and functionally faster than a non-sorted lookup.

=ADDRESS(MATCH(A2, E:E, 0), 5, 4)&":"&ADDRESS(MATCH(A2&" ", E:E), 5, 4)

The first address is the conventional exact match. The second address is achieved through looking for the last one in a sorted list by appending a suffix space character.

Since you are dealing with a known column, I've thrown out the CELL function in favor of the ADDRESS constructor.

enter image description here

This method of binary match on sorted data will produce unreliable results (results, not errors, that may or may not be correct) on unsorted data.

Upvotes: 2

Jerry
Jerry

Reputation: 71598

Assuming that the data is sorted, you will need two parts to your formula, the first part to get the address of the first cell, and a second part to get the address of the last cell. You already got the first part. For the second part, you can use a similar formula, but notice how MATCH is different:

=CELL("address",INDEX($F$2:$F$16,MATCH(A2,$F$2:$F$16,0)))&":"&CELL("address",INDEX($F$2:$F$16,MATCH(A2,$F$2:$F$16,1)))

enter image description here

EDIT: I missed the simpler MATCH solution for the second part earlier. In case you are still interested in the previous array formula, it's the following:

=CELL("address",INDEX($F$2:$F$16,MATCH(A2,$F$2:$F$16,0)))&":"&CELL("address",INDEX($F$2:$F$16,MAX(IF($F$2:$F$16=A2,ROW($F$2:$F$16)-1))))

Upvotes: 3

Error 1004
Error 1004

Reputation: 8230

If you are interesting to use VBA Change Event you can try:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim LastrowA As Long, LastrowF As Long, i As Long, y As Long
    Dim Source As String, strAddress As String
    Dim ws As Worksheet

    'Set the sheet which you will use
    Set ws = ThisWorkbook.Worksheets("Sheet1")

    With ws
        'Find Last rows for Column A & F
        LastrowA = .Cells(.Rows.Count, "A").End(xlUp).Row
        LastrowF = .Cells(.Rows.Count, "F").End(xlUp).Row
    End With

    'Chek if the changes intersect our ranges

    If Not Intersect(Target, Range("A:A", "F:F")) Is Nothing Then

        For i = 2 To LastrowA

            Source = ws.Range("A" & i).Value

            Application.EnableEvents = False
                'Count Times
                ws.Range("B" & i).Value = Application.WorksheetFunction.CountIf(ws.Range("F2:F" & LastrowF), Source)

                strAddress = ""
                'Import Address
                For y = 2 To LastrowF
                    If ws.Range("F" & y).Value = Source Then
                        If strAddress = "" Then
                            strAddress = ws.Range("F" & y).Address
                        Else
                            strAddress = strAddress & "," & ws.Range("F" & y).Address
                        End If
                    End If
                Next y

                ws.Range("C" & i).Value = strAddress

            Application.EnableEvents = True

        Next i

    End If

End Sub

Results:

enter image description here

Upvotes: 0

Related Questions