If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Lookup based on matrix values
I am struggling to develop one of those "copy down" functions that can
produce a set of values. In this simplified example, the formula needs to tell me the name from column A if there is a value in the remaining array (B15). Dave 1 2 3 Bob 1 Sarah 3 George 2 Sally 1 So the first instance of the formula would produce "Dave", the second would produce "Bob", the third ""George", the fourth is "Dave" again, followed by "Sarah", etc. Yes, I need it to read the array down the first column before it moves on to the next column. For what it's worth, here's what is not working for me: =INDEX($B$15:$B$68,SMALL(IF($C$15:$Z$68"",ROW($C $15:$N$68)-14),ROWS($B$70:H70))). It's an INDEX function using the SMALL function to produce sequential values and the IF function to identify all the places where the array is populated. It's entered as an array function, of course. Any ideas? TIA |
#2
|
|||
|
|||
Lookup based on matrix values
Andy,
This is no simple task and is easiest achieved by wrtting a custom function. Open your VB editor (Alt+F11). Insert a Module in your workbook. Copy the below function into the module. Now go back to your worksheet. You will see you can insert the NAMEFINDER function. The first argument is the column of names. the second argument is the ENTIRE number matrix excluding the names column. The function returns a COLUMN array so you must execute with Ctrl+Shift+Enter. I know this is not the copy down formula you were looking for but you need only extend the number of selected cells before you execute to achieve a similar result. Function NAMEFINDER(ByVal Names_Range As Range, ByVal Matrix As Range) As Variant Dim Answer() As String Dim TheRows As Long, TheColumns As Long Dim i As Long, j As Long, z As Long z = 0 TheRows = Matrix.Rows.Count TheColumns = Matrix.Columns.Count For j = 1 To TheColumns For i = 1 To TheRows If Matrix.Cells(i, j).Value "" Then ReDim Preserve Answer(z) Answer(z) = Names_Range(i).Value z = z + 1 End If Next i Next j NAMEFINDER = Application.WorksheetFunction.Transpose(Answer) End Function -- If this helps, please click "Yes" "andy62" wrote: I am struggling to develop one of those "copy down" functions that can produce a set of values. In this simplified example, the formula needs to tell me the name from column A if there is a value in the remaining array (B15). Dave 1 2 3 Bob 1 Sarah 3 George 2 Sally 1 So the first instance of the formula would produce "Dave", the second would produce "Bob", the third ""George", the fourth is "Dave" again, followed by "Sarah", etc. Yes, I need it to read the array down the first column before it moves on to the next column. For what it's worth, here's what is not working for me: =INDEX($B$15:$B$68,SMALL(IF($C$15:$Z$68"",ROW($C $15:$N$68)-14),ROWS($B$70:H70))). It's an INDEX function using the SMALL function to produce sequential values and the IF function to identify all the places where the array is populated. It's entered as an array function, of course. Any ideas? TIA |
#3
|
|||
|
|||
Lookup based on matrix values
Andy,
What you are trying to do is not simple and is best achieved by writing a custom function in VB. Open VB editor (Alt+F11). Insert a Module and copy the below code into the module. Now go back to your spreadsheet and you will see you can insert the NAMEFINDER function. The first argument is the column of names you have, the second is the ENTIRE matrix. The function returns a COLUMN array so you must select a vertical range, type the formula and execute with Ctrl+Shift+Enter. I know this is not the copy down result you needed but you can simply extend the number of selected cells before you execute the formula to achieve a similar effect. Function NAMEFINDER(ByVal Names_Range As Range, ByVal Matrix As Range) As Variant Dim Answer() As String Dim TheRows As Long, TheColumns As Long Dim i As Long, j As Long, z As Long z = 0 TheRows = Matrix.Rows.Count TheColumns = Matrix.Columns.Count For j = 1 To TheColumns For i = 1 To TheRows If Matrix.Cells(i, j).Value "" Then ReDim Preserve Answer(z) Answer(z) = Names_Range(i).Value z = z + 1 End If Next i Next j NAMEFINDER = Application.WorksheetFunction.Transpose(Answer) End Function -- If this helps, please click "Yes" "andy62" wrote: I am struggling to develop one of those "copy down" functions that can produce a set of values. In this simplified example, the formula needs to tell me the name from column A if there is a value in the remaining array (B15). Dave 1 2 3 Bob 1 Sarah 3 George 2 Sally 1 So the first instance of the formula would produce "Dave", the second would produce "Bob", the third ""George", the fourth is "Dave" again, followed by "Sarah", etc. Yes, I need it to read the array down the first column before it moves on to the next column. For what it's worth, here's what is not working for me: =INDEX($B$15:$B$68,SMALL(IF($C$15:$Z$68"",ROW($C $15:$N$68)-14),ROWS($B$70:H70))). It's an INDEX function using the SMALL function to produce sequential values and the IF function to identify all the places where the array is populated. It's entered as an array function, of course. Any ideas? TIA |
Thread Tools | |
Display Modes | |
|
|