Search a grid of cells

I'm looking for a search function to return the row and column of a cell containing matched data, or a combination of worksheet functions to do the same thing.

 

example:

set up numbers 1 to 12 across B1 to M1, and in A2 down to A13.

enter formula in B2 to M13 to multiply the number in row 1 by the number in column A.

Now I have a range B2:M13 containing all the various multiples.

 

A countif function tells me that there are 6 appearances of the number 12 in this range.

 

I want to be able to find the column and row numbers (actually the numbers from the top row and the left column), where these 12s appear so I can use the values in further calculations.

 

The Find & Select engine returns the cells using the Find All, but I want to be able to search over the range with a function.

As there is more than one match, either returning the first one or being able to add a 1st, 2nd, 3rd etc parameter would be nice.

 

Is there any worksheet function to search over a range of cells?
LVL 17
Thibault St john Cholmondeley-ffeatherstonehaugh the 2ndAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
The attached workbook contains this macro.
Sub FindAllNumber()
Dim intToFind As Integer
Dim cel As Range
Dim intRow As Integer

intToFind = InputBox("Please enter the number to be found", "Enter Number", 12)
intRow = 15

For Each cel In Range("B2:M13")
    If cel.Value = intToFind Then
        Cells(intRow, 1) = cel.Address
        intRow = intRow + 1
    End If
Next

End Sub

Open in new window

Give it a try.
Q-28625087.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Thibault St john Cholmondeley-ffeatherstonehaugh the 2ndAuthor Commented:
It finds them all. Is there a tidy way to get that to work as a function? I could hack it about but you might have something close already.

Guessing it needs a separate row and column function and an input parameter for the instance required.

If it's too much to ask just say so. If it requires a lot of coding then I'd be better approaching the while project as a coding exercise rather than a spreadsheet.
0
Rob HensonFinance AnalystCommented:
Assuming with the same setup as described in your question, put the value that you are looking for in cell B18.

Then cells P2 this formula and copied down:

=IFERROR(MATCH($B$18,$B2:$M2,0),"")

Then Q2 and copied down:

=IFERROR($B$18/P2,"")

Columns P & Q now give the grid references for the value you are looking for. They could be combined into one result if so required with a colon separator.

However, I suspect the situation as described above is purely for illustration purposes, and the above is too simplistic.

Thanks
Rob H
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

Danny ChildIT ManagerCommented:
This strikes me as a bit of unscrambling an egg...

If you want to find the factors of a number, there is an array formula that can do it.

Number in A1

Formula
=LARGE((ROUND($A$1/ROW(INDIRECT("$1:$"&$A$1)),0)=$A$1/ROW(INDIRECT("$1:$"&$A$1)))*ROW(INDIRECT("$1:$"&$A$1)),ROW(1:1))
 - and press CTRL + SHIFT + ENTER to make it an array formula.  Then, copy downwards for as many cells as you have factors.

More info here:
http://www.excelforum.com/excel-formulas-and-functions/488946-how-do-i-find-prime-factors-of-a-number.html
0
Martin LissOlder than dirtCommented:
One of the above solutions may well be better than mine but try this.

Put the formula =FindAllNumber(12) in any cell. Note that "12" could be any number.
Q-28625087a.xlsm
0
Thibault St john Cholmondeley-ffeatherstonehaugh the 2ndAuthor Commented:
Thanks all. I am away from my machine until tomorrow.  It is indeed unscrambling an egg,  but not unfortunately as simple as factoring. This is just to explain what I am looking for. The full lump covers at the moment a grid of 1000 by 1000 and is likely to probably quadruple if the processor can keep up. I am also checking for more than one number so copying down something to check every line is not really ideal.
Martin's first macro basically does the job and with a bit of tweaking ( which may be just above) should provide the function I need.
I was surprised not to find an existing function to search a block, the count functions are happy to read more than a column or row.
I'll give it a try tomorrow and come back.
0
Thibault St john Cholmondeley-ffeatherstonehaugh the 2ndAuthor Commented:
Thanks Martin. I'll twiddle it a little, but that finds them ok. I'm going to bend it into a function something like FindNo(numbertofind,range,instance,[row|col]) to return into the cell where I put the function so I can use the returned values directly into other functions. Your work has given me a good start. It does take a strangely long time to run, but that might be because my machine is busy doing other things as well.
Thanks
0
Martin LissOlder than dirtCommented:
You're welcome and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2014
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.