[budward]

I have this formula
-=IF(ISERROR(MATCH($B$1,A:A,0)),"not found",INDEX(A:A,MATCH($B$1,A:A,0)-1))
=IF(ISERROR(MATCH($B$1,A:A,0)),"not found",INDEX(A:A,MATCH($B$1,A:A,0)))
=IF(ISERROR(MATCH($B$1,A:A,0)),"not found",INDEX(A:A,MATCH($B$1,A:A,0)+1))

It will let me type a number in B1 and search my list of numbers in A1.It returns the matching number plus the number 1 up and 1 down from it.
It only returns 1 instance of matching numbers. I need to modify it to return every match in my list with the 1 up and 1 down plus matching number because there are usually more than 1 matches in my list.

 

[bomb #21]

Hi Bud.

With formulas, not so easy. Maybe code would be better. HTH.

Sub test()
x = Rows.Count
On Error Resume Next
For Each Cell In Range("A1", Range("A" & x).End(xlUp))
If Cell = Range("B1") Then
Cell.Offset(-1, 0).Resize(3).Copy Range("B" & x).End(xlUp).Offset(1, 0)
End If
Next Cell
End Sub

 

[budward]

Thanks bomb 21. Now I've decided all I want is the number on top of my matched number. How do I change the code for that?
Bud

 

[budward]

Oops - never mind .
I figured it out. Duh!
Thanks again...Bud

 

[budward]

bomb 21, I've got a question for you.
When i am running the macro you made,if I copy and paste numbers from another sheet, the macro doesn't work but if I type each 3 digit number individually the macro works. I have tried all different types of formatting but still no change. Can you figure it out?
Bud

 

[bomb #21]

Sorry I wasn't around to answer your PM.

Not sure what you mean by "copy and paste numbers from another sheet" ... since the workbook you attached only contains one sheet. Can you explain more fully?

EDIT:

Note1: you don't need to select Range("B7:B5000") to clear contents -- Range("B7:B5000").ClearContents will do.

Note2: try If Cell = Range("B1").Text to compensate for the custom format of B1.

 

[budward]

I have some Pick 3 data bases set up with all the numbers drawn in a different folder.I plan on copy and pasting them into my followers search spreadsheet. Your note # 2 did the trick. Thanks again...Bud

 

[Zack Barresse]

I'm curious why VBA is even being used here. Maybe I don't understand the requirements too well. That may be due to information being passed via pm and not on the open board. From what I understand you want to find every match in column A which matches the value of B1, take that value and the value physically directly above it (for every match/iteration) and add (is that right??) them together.

Please let me know if this is right or not. Also, in your sample file, it was blank. I found no data in it whatsoever. Is that what it was supposed to be??

 

[budward]

Thanks for your interest in my question.
Hmmm.... I tried both of my example sheets and everything was there. Something screwy goin on. In answer to your question I wanted to list every match from Col A in Col B with the number immediatly on top of it. I didn't need to add them. Just checking to see if certain numbers follow other numbers in Pick 3 lottery drawings.
Thanks..Bud

 

[Zack Barresse]

I just don't understand why you need VBA here. In B2 enter the following formula...

=IF(OR(A2=$B$1,A1=$B$1),1,0)

This assumes you want to match the value of B1 in column A and that A1 holds a header (it can even not hold a header but you would need to create one for the next part to work). Autofilter columns A & B for column B of a value of 1. There is your list in column A. Copy/paste to another location if you want the list separate. Would you like that in code? Do it and record a macro, post here for updates of efficiency/speed.

HTH

 

[bomb #21]

I just don't understand why you need VBA here. In B2 enter the following formula...

=IF(OR(A2=$B$1,A1=$B$1),1,0)

Hey, happy new year.

Of course I'm only guessing but ... IMO, what's required is to extract every match(ing pair). So with formulas you'd need: find first match in A, then find first match after first found, etc. etc. Which would get pretty messy. Plus there'd be no way to know in advance how many matches there's be "this time".

HTH.

 

[Zack Barresse]

Happy new year to you too Andy!

No, not right off hand, there wouldn't be a way to know how many matches there are "this time", but it wouldn't take very long to develop a system of formulas which could provide you with this information. Besides, if this is wanting to be done repeatedly, they should probably rethink their data structure anyway, possibly even look into databases.

 

[bomb #21]

... it wouldn't take very long to develop a system of formulas which could provide you with this information ...

I think what you mean is, it wouldn't take you very long to ... etc.

 

[budward]

LoL, it would take me forever!
Now I have a new twist on this and have included an example below.
What I would like to do now is enter a number in U4, search row Q, in Row V, return the number from row S that is beside every instance of the number in Row Q. I don't have any preference over macro or formula.
Thanks....Bud
After posting I realized my third number in Row V is wrong. It should be 148.

 

[Zack Barresse]

In your example in cell V6 you have a value of 132. Should that not be 148? That is what matches on the last line of your list (row 36).

Solution:
Use a helper column with the following formula (I entered in T6):
=IF((--Q6)=$U$4,S6,"")
Copy down to end of data (double click bottom right corner, mouse icon should be a bold + sign).

Then use this as a formula in V4 and copy down as needed:

=INDEX($Q$6:$S$36,SMALL(IF($Q$6:$Q$36=TEXT($U$4,"@"),ROW($Q$6:$Q$36)-ROW($Q$6)+1,ROW($Q$36)+1),ROW(U1)),3)

This is an array formula which needs to be entered with Ctrl + Shift + Enter. If you sometimes enter formulas as a multiple range with Ctrl + Enter (very few people do, I find it easier, but can lead to problems if you don't watch yourself) you'll want to not do it here. Enter as an array in a single cell, then copy down. If you copy down to row 6, you'll see all three of your values.

The benefit a VBA solution would have over this is that with the formulas, you'll need to copy down an unknown amount. Could you have 10 matches? Probably not. Statistically speaking, you probably will not get more than 5 matches ever. So copying the formula down 5 cells (including the first cell the function is entered in) you will see blanks where there may not be data. If you clear these out because there is currently no value in them, when there is a value that could be shown they may not have that opportunity. It is much more hands-on approach. With VBA we can utilize only the space that is necessary.

To do exactly what the formula does, the only difference being no helper column would be needed, the following code would be needed:

Sub ExtractValues()
    Dim ws As Worksheet, rngView As Range, c As Range, iRow As Long
    Set ws = ThisWorkbook.Sheets("Sheet1")
    Set rngView = ws.Range("Q6", ws.Cells(ws.Rows.Count, "Q").End(xlUp))
    iRow = 4
    ws.Range("V:V").ClearContents
    For Each c In rngView
        If CLng(c.Value) = ws.Range("U4").Value Then
            ws.Cells(iRow, "V").Value = CLng(c.Offset(0, 2).Value)
            iRow = iRow + 1
        End If
    Next c
End Sub

Edit: Added a line of code to clear column V as to not leave trailing false-positives.

HTH

 

[budward]

Thanks firefytr,
I used the VBA and it works perfectly!

 

[Zack Barresse]

You're very welcome budward.

You can mark your thread as Solved by going to Thread Tools | Mark Solved | Perform Action.