Find first/last instance of a value in an array

[George]

Hi All,

Nice little challenge for you all (I can solve this with VBA, but it would be interesting to see if there's a formula solution to it).
What I'm looking for is a formula to find the row containing the first instance of a value in an array, so if I were looking for "Two" in the following array:

1, seven, twelve
2, 2, 2
Two, 3, 4
Three, Two, Two

I would get 3 for the first instance and 4 for the last.
I presume it would be some combination of MIN(), MATCH() and array formula, but I can't quite figure it out.

Any pointers would be great (although as I said, the macro route is probably smartest really).

Thanks,

George.

 

[Sajan]

Hello George,
I am not sure I understand your question since it can be interpreted multiple ways. Can you tell me what the results would be for the following data set?
1, TwoTwo, twelve
1,2,3,4,5,Two
Two, 3, 4
1,2,3,4,5,6,Two,Two
2, Two, 2
Three, Two, Two

-Sajan.

 

[NARAYANK991]

Hi George ,

While we wait for you to clarify Sajan's doubts , here is one possibility :

Minimum : =MATCH(TRUE,ISNUMBER(SEARCH(LookFor,List)),0) entered as an array formula using CTRL SHIFT ENTER.

Maximum : =LOOKUP(99999999,SEARCH(LookFor,List),ROW(List)-MIN(ROW(List))+1)

The above formulae will not distinguish between the item being looked for occurring on its own in the List or if it is embedded within other text ; thus looking for the word two will return a true result even if the text in List is twosome.

Narayan

 

[kchiba]

Hi,

Is this a two-dimension array, and are you looking to find the "Row", containing Two?

kanti

 

[Sajan]

Hi,
I should also ask if each row of your example data is in a single cell, or if they are in individual cells. If individual cells, this problem becomes straightforward to solve. If each row is in a single cell, that poses some unique challenges.

Cheers,
Sajan.

 

[George]

Hello George,
I am not sure I understand your question since it can be interpreted multiple ways. Can you tell me what the results would be for the following data set?
1, TwoTwo, twelve
1,2,3,4,5,Two
Two, 3, 4
1,2,3,4,5,6,Two,Two
2, Two, 2
Three, Two, Two

-Sajan.

so if we were searching for "Two" we would get 2 for first and 6 for last.

Narayan - thanks, but is there a way of doing an exact match?

Sajan - each entry is an individual cell (retrospect should have thrown together an example spreadsheet).

Basically my example range would be a CSV sheet.

 

[kchiba]

Are the cells in one row or 4 rows by three columns?

 

[Xiq]

@George

retrospect should have thrown together an example spreadsheet

It's not to late! You can still save the day!

 

[NARAYANK991]

Hi George ,

Try these two formulae :

Minimum : =MATCH(TRUE,ISNUMBER(SEARCH(","&LookFor&",",","&SUBSTITUTE(List," ","")&",")),0) entered as an array formula , using CTRL SHIFT ENTER

Maximum : =LOOKUP(99999999,SEARCH(","&LookFor&",",","&SUBSTITUTE(List," ","")&","),ROW(List)-MIN(ROW(List))+1)

Narayan

 

[Sajan]

What are the expected results in the following example:
1,2,3,Two,Two
2,3,4,Two
1,2,2,Two,Two

Since "two" is found in row 1 also, will the MIN row be 1 or 2? Will the MAX row be 1 or 3?

I am basically trying to understand if the position of a substring in a row is relevant.

 

[George]

Decided to go the macro route in the end (although thanks for all your suggestions, they were quite interesting in spite of my really poor explanation). In case it helps anyone searching for this in the future, here's the code:

Public Sub FirstAndLastDate()

Dim SourceLastRow As Double                                                                        'the last row in the source data
Dim SourceLastCol As Double                                                                        'the last column in the source data
Dim OutputLastRow As Double                                                                        'the last row in the output file
Dim a As Double                                                                                    'loop variable
Dim i As Double                                                                                    'loop variable
Dim j As Double                                                                                    'loop variable
Dim FirstDate As Double                                                                            'row holder for the first date
Dim LastDate As Double                                                                              'row holder for the last date


    OutputLastRow = Sheets("OutputData").Range("A1048576").End(xlUp).Row                            'setting the last rows and columns up
    SourceLastRow = Sheets("SourceData").Range("A1048576").End(xlUp).Row
    SourceLastCol = Sheets("SourceData").Range("XFD1").End(xlToLeft).Column - 1
   
    For a = 2 To OutputLastRow                                                                      'do this for any instance of the search value (put the list in worksheet "OutputData", column A)
        FirstDate = 0                                                                              'set as 0 - needs to be reset every time
        LastDate = 0
   
       
        For i = 2 To SourceLastRow                                                                  'cycle through every day
            For j = 5 To SourceLastCol                                                              'cycle through every column, in this case it's a list of names in row 1, with some extra information
                If Sheets("SourceData").Cells(i, j).Value = Sheets("OutputData").Cells(a, 1).Value Then      'if it's the right campaign
                    If FirstDate = 0 Then                                                          'if we haven't had the first date yet
                        FirstDate = i                                                              'set the first date row holder variable to this row
                    End If
                    LastDate = i                                                                    'set the last date row holder to this row
                End If
            Next j
        Next i
   
        If FirstDate > 0 Then                                                                      'if it exists in the data
            Sheets("OutputData").Cells(a, 6).Value = Sheets("SourceData").Cells(FirstDate, 1).Value & ", " & Sheets("SourceData").Cells(FirstDate, 4).Value      'set column F to the first occurance - I've stored AM and PM in SourceData column D
            Sheets("OutputData").Cells(a, 7).Value = Sheets("SourceData").Cells(LastDate, 1).Value & ", " & Sheets("SourceData").Cells(LastDate, 4).Value        'set column G to the last occurance
        End If
   
   
    Next a
   
   
End Sub

The data we're searching through is stored in the sheet SourceData, with the search values in column A and row 1 and the data itself from cell B5 to wherever the end is.