LOOKUP Challenge

[Jan Kronsell]

I have a workbook, consisting of several sheets. In this case only two of
the sheets are interesting.

In sheet "Bulk", column BC I have a field containing project ID and in
column BD I have the name of the executive rerponsible for the project.

In sheet "Data" I have the same projectID's in Column I. In column A I want
the name of the Executive in charge returned.

This is ordinarily no problem using VLOOKUP. In this case however, the
projectID is not unique. In Bulk colunm BC the same number can appear 2, 3,
4 or 5 times with a different exceutive name, as each project can have more
than one responsible exec.

In Data, column I the ID also appears several times. The challenge now is to
return the correct name in Data column A, so that first time the projectID
appears in columns A, the first responsible Excec name appears, next time
the ID appears it shoud be the name of the second responsible exec and so
on.

I have a UDF, that returns second, third, fourth and so onn, excec name, but
this UDF needs to know wich number I want returned, and that must be
hardcoded in the formula, which is not an option as I have more 30.000 rows
in the sheet.

Is there anyway to return the second, third, and so on?

Jan

 

[Bernie Deitrick]

Jan,

In your UDF (which you can post if you cannot figure out how to implement this solution) add an
argument that increments as needed.

Function UDF(myVal As String, myR As Range, myCol As Integer, myNum As Integer)

and when you use it, pass COUNTIF($I$2:I2,I2) as the instance number control:

=UDF(I2,Bulk!BD:BD,2,COUNTIF($I$2:I2,I2))


HTH,
Bernie
MS Excel MVP

 

[Harlan Grove]

In sheet "Bulk", column BC I have a field containing project ID and in
column BD I have the name of the executive rerponsible for the project.

....

Is this table sorted on the project ID column? If so, it'd be easiest
to add a column to the table containing formulas like (for example in
cell DZ3 referring to the project ID in cell BC3)

DZ3:
=BC3&TEXT(IF(BC3=BC2,("0"&RIGHT(DZ2,4))+1,0),"\.0000")

Fill DZ3 down as far as needed.

In Data, column I the ID also appears several times. The challenge now is to
return the correct name in Data column A, so that first time the projectID
appears in columns A, the first responsible Excec name appears, next time
the ID appears it shoud be the name of the second responsible exec and so
on.

....

This you could handle with formulas like (assuming topmost result
record in row 3, showing formula for 3rd result record in row 5)

A5:
=INDEX(Bulk!BD$3:BD$30002,MATCH(I5&TEXT(COUNTIF(I$3:I$5,I4),"\.
0000"),Bulk!DZ$3Z$30002))

IOW, you don't need a udf for this if your table in the Bulk worksheet
were sorted on project ID.

 

[Harlan Grove]

In your UDF (which you can post if you cannot figure out how to implement this solution) add an
argument that increments as needed.

....

Unnecessary. The udf could use the Application.Caller property to
determine from which cell's formula it was called and calculate the
instance number using it.

 

[Jan Kronsell]

The table is not sorted on projectid. It sorted on another fields, and has
to be.

Jan

 

[Jan Kronsell]

...

Unnecessary. The udf could use the Application.Caller property to
determine from which cell's formula it was called and calculate the
instance number using it.

I Look into the VBA siolutions tomorrow. The UDF looks like this

Function FLOPSLAG(ops As Variant, num As Single, rn As Range, ofs As Byte)
Dim Taeller As Long
Dim i As Long
i = 0
For Each c In rn.Columns(1).Cells
If c.Value = ops Then
i = i + 1
End If
Next c
If num - CInt(num) <> 0 Or num < 1 Then
FLOPSLAG = CVErr(xlErrNum)
Exit Function
End If
If i < num Then
FLOPSLAG = CVErr(xlErrNA)
Exit Function
End If
Taeller = 0
For Each c In rn.Columns(1).Cells
If c.Value = ops.Value Then
Taeller = Taeller + 1
If Taeller = num Then
FLOPSLAG = c.Offset(0, ofs - 1).Value
Exit Function
End If
End If
Next c
End Function

and is used like: =FLOPSLAG(lookupvalue,number of appearance to show,
lookuparea, rownumber to return value from) ie

=FLOPSLAG(A2,3,C1100,2) to return the value from column 2 in the aarray,
that is representing the third occurrence of the value in A2.

Jan

 

[Jan Kronsell]

Great. It did the trick.

Jan

Jan,

In your UDF (which you can post if you cannot figure out how to
implement this solution) add an argument that increments as needed.

Function UDF(myVal As String, myR As Range, myCol As Integer, myNum
As Integer)
and when you use it, pass COUNTIF($I$2:I2,I2) as the instance number
control:
=UDF(I2,Bulk!BD:BD,2,COUNTIF($I$2:I2,I2))


HTH,
Bernie
MS Excel MVP