• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Find 1st instance of text across multiple columns [SOLVED]

Jack

Member
Hi everyone


This one I think is and I have certainly found to be harder than it sounds. I have a range of data spanning 13 columns G6:S30 with each column representing different jobs on any given day in a business for a roster. Each row is a 1/2 hour time slot. So an employee's initials say " FA" can be entered anywhere in this range G6:S30 to give their hours worked and they might start the day in one column and end in another later in the day. I want to pull out their start and end times which appear in F6:F30. So to do this I just need the row for the first occurance of their initials anywhere in that range of 13 columns to use in an index formula to return a value from F for the same row. I got the last instance OK as below but the 1st is tricky because if I replace below MAX with MIN it returns 0. Note the -Row($F6)+1 bit just caibrates to the starting row of 6 used vs the row number of the sheet and works fine. I also tried MINA but this didn't work either.


=INDEX($F6:$S30,SUMPRODUCT(MAX(($G6:$S30=F$35)*ROW($G6:$S30))-ROW($F6)+1),1)


I guess I could concatenate each of the 13 columns in a array entered match formula but that seems tedious and hoping there is a better way as I need to use a few of these formulas.


I have done a lot of googling and it's the fact that I am looking in multiple columns that is causing the problem, single column with multiple criteria is easy but one criteria across a range seems to be tricky.


Any help would really be appreciated.


Cheers


John
 
All I decided to write a UDF to do this to put myself out of misery...this seems to work and I have it in an Index formula with F35 having the text to look for... =INDEX($F6:$S31,Rowfor_Nth_TextinRange($G6:$S31, F35, 1)-ROW(F6)+1,1)

Thanks to Ozgrid for the headstart.

[pre]
Code:
Function Rowfor_Nth_TextinRange(SearchRange As Range, FindThis As String, Occurrence As Long)
Dim lCount As Long
Dim rFound As Range
Set rFound = SearchRange.Cells(1, 1)

For lCount = 1 To Occurrence
Set rFound = SearchRange.Find(FindThis, rFound, xlValues, xlWhole)
Next lCount

Rowfor_Nth_TextinRange = rFound.Row
End Function
[/pre]
 
Try following formulas (array entered i.e. CTRL + SHIFT + ENTER simultaneously)


Minimum row:

=INDEX($F$6:$F$30,MIN(IF($G$6:$S$30="FA",ROW($G$6:$S$30)))-5)


Maximum row:

=INDEX($F$6:$F$30,MAX(IF($G$6:$S$30="FA",ROW($G$6:$S$30)))-5)
 
Hello Shrivallabha yes great formulas, they work well, thanks very much for this.


This issue can be closed now.


Cheers

John
 
Back
Top