Search for X number in a list and return numbers below it.

[dwrowe001]

Hi EXCEL'ers,
I need help again... I am trying to develop sheet where I search for a number in a large number list and then return the numbers below it from the list.
This list is historical pick list from a lottery. The number being searched for will be in the list several times, so the formula or VBA code should be configured to find each instance of the number being searched for and then continue down the list finding each and every instance of it, returning the numbers directly below it.

I am attaching a detailed description of what I need as well as an example spreadsheet. I hope this will help detailing what I need.

Thank you in advance for any and all help with this.
Dave

 

[Stevie]

Hi dwrowe001
I have not looked at your files, but this code does what you described:

Sub dwrowe001()
    Dim searchNum As Double
    Dim offsetNum As Double
    Dim outputString As String
    Dim searchRange As Range
    Dim c As Range
    searchNum = Sheets("Sheet1").Range("A1").Value 'set this range to be where you are entering the number to be searched for
    outputString = "|"
    Set searchRange = Sheets("Sheet1").Range("A1:A50") ' set this range to be the list of numbers to search through
    For Each c In searchRange
        If c.Value = searchNum Then
            offsetNum = c.Offset(1, 0).Value
            If c.Row <> searchRange.Rows.Count Then
                outputString = outputString & offsetNum & "|"
            End If
        End If
    Next
    MsgBox ("List of numbers below matched values:" & vbCrLf & outputString)
End Sub

Should you wish to, it should be simple enough to modify for layout reasons etc.

If this was helpful, please click 'Like!'

Stevie

 

[p45cal]

The attached should get you started.
While putting together formulae I worked on your Master sheet (I'm lazy) so it's over to you to put them over to the other sheet, changing the references as needed to the Master sheet cells.

Therre are 3 formulae:
1 in cell K2 which is an array-entered formula and can be copied down and across.
1 in cell O16 which isn't copied anywhere.
1 in cell O17 which is copied down.

Experiment changing xAfter in cell I4 and the ball value in cell I1.

I don't think your expected values of 20,4,11 for the first ball with 12 are correct, the rest seem to be the same.

 

[dwrowe001]

thank you Stevie and p45cal for your options.