We help IT Professionals succeed at work.

Search a grid of cells

Enabbar Ocap
Enabbar Ocap asked
on
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?
Comment
Watch Question

"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
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
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.
Rob HensonFinance Analyst

Commented:
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
Danny ChildIT Manager

Commented:
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
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
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
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.
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
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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