A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Lookup based on matrix values



 
 
Thread Tools Display Modes
  #1  
Old May 28th, 2010, 02:44 PM posted to microsoft.public.excel.misc
andy62
external usenet poster
 
Posts: 140
Default 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  
Old May 28th, 2010, 04:39 PM posted to microsoft.public.excel.misc
BSc Chem Eng Rick
external usenet poster
 
Posts: 98
Default 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  
Old May 28th, 2010, 04:54 PM posted to microsoft.public.excel.misc
BSc Chem Eng Rick
external usenet poster
 
Posts: 98
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 03:41 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.