Lookup Question

pruel · Jun 8, 2008

Over the last few years, I've tried to do something in Excel. I optimistically survey the documentation for the function(s) I need, but I always walk away defeated. Can someone tell me how to do the following?

I have a table with two columns:

Data1.........Data2
------------- --------------
red...........0
orange .......1
yellow........0
green.........7
blue..........3
indigo........0
violet........0

I'd like a simple way to derive a single cell, with value:

orange, green, blue

That is, wherever the data in Data2 <> 0, take the corresponding Data1 value and concatenate to the cell's value. The commas should be able to be an arbitrary separator character so one would not be confused by "blue green" versus "blue, green"

Thanks in advance for a solution!

Fazza · Jun 8, 2008

Hi pruel.

Try these in a code module of the workbook. And then call them from a worksheet cell like a worksheet function.

So, =TryThis(A2:B8,",")

HTH, Fazza

Code:

Function TryThis(rngData As Range, strSeparator As String) As String
   Dim i As Long, j As Long
   Dim arDataIn As Variant
   Dim arOutput() As String
   arDataIn = rngData.Value2
   ReDim arOutput(1 To UBound(arDataIn, 1))
   For i = LBound(arDataIn) To UBound(arDataIn)
      If arDataIn(i, 2) > 0 Then
         j = j + 1
         arOutput(j) = arDataIn(i, 1)
      End If
   Next i
   ReDim Preserve arOutput(1 To j)
   TryThis = Join$(arOutput, strSeparator)
 
End Function

Code:

Function OrThis(rngData As Range, strSeparator As String) As String
   Dim i As Long
   Dim arDataIn As Variant
   Dim dicOutput As Object
   Set dicOutput = CreateObject("Scripting.Dictionary")
   arDataIn = rngData.Value2
   For i = LBound(arDataIn) To UBound(arDataIn)
      If arDataIn(i, 2) > 0 Then
         dicOutput.Add arDataIn(i, 1), Nothing
      End If
   Next i
   OrThis = Join$(dicOutput.keys, strSeparator)
   Set dicOutput = Nothing
End Function

Yogi Anand · Jun 8, 2008

Hi pruel:

Welcome to MrExcel Board!

Following is one way using MCONCAT function from MoreFunc Add-in (available for free) ...

<html><head><title>Excel Jeanie HTML</title></head><body>

Excel Workbook

A

B

C

D

1

Data1

Data2

*

2

-------------

*

3

red

0

*

orange,green,blue

4

orange

1

*

5

yellow

0

*

6

green

7

*

7

blue

3

*

8

indigo

0

*

9

violet

0

*

10

*

Sheet5 (2)

</body></html>

pruel · Jun 9, 2008

Fazza, thanks a ton for this. It got me started out in the right direction. I made a few changes and came up with this:

Rich (BB code):

Function MyLookup(rngData1 As Range, rngData2 As Range, strSeparator As String) As String
   Dim i As Long, j As Long
   Dim res As String
   For i = LBound(rngData1.Value) To UBound(rngData2.Value)
      If rngData2(i, 1) > 0 Then
         j = j + 1
         If j > 1 Then
            res = res & strSeparator
        End If
        res = res & rngData1(i, 1)
      End If
   Next i
   MyLookup = res
 
End Function

For one thing, I wanted the zero/non-zero vector to be able to be separated from the data vector.

Question: how can I change the function so that it returns a bunch of cells that will fill columns to the right? For example, I'd like to be able to put the function call in a cell, say A1, like this:

Rich (BB code):

=MyLookup(D2:D8, G2:G8, ", ")

where the data in D2:D8 and G2:G8 are as in my original example (Data1 and Data2). The result, as a String, would be:

Rich (BB code):

orange, green, blue

but is it possible to have the function call populate cell A1 with "orange," cell A2 with "green," and cell A3 with "blue?"

Is this possible?

Thanks!!

Fazza said:

Hi pruel.

Try these in a code module of the workbook. And then call them from a worksheet cell like a worksheet function.

So, =TryThis(A2:B8,",")

HTH, Fazza

Rich (BB code):

Function TryThis(rngData As Range, strSeparator As String) As String
   Dim i As Long, j As Long
   Dim arDataIn As Variant
   Dim arOutput() As String
   arDataIn = rngData.Value2
   ReDim arOutput(1 To UBound(arDataIn, 1))
   For i = LBound(arDataIn) To UBound(arDataIn)
      If arDataIn(i, 2) > 0 Then
         j = j + 1
         arOutput(j) = arDataIn(i, 1)
      End If
   Next i
   ReDim Preserve arOutput(1 To j)
   TryThis = Join$(arOutput, strSeparator)
 
End Function

Rich (BB code):

Function OrThis(rngData As Range, strSeparator As String) As String
   Dim i As Long
   Dim arDataIn As Variant
   Dim dicOutput As Object
   Set dicOutput = CreateObject("Scripting.Dictionary")
   arDataIn = rngData.Value2
   For i = LBound(arDataIn) To UBound(arDataIn)
      If arDataIn(i, 2) > 0 Then
         dicOutput.Add arDataIn(i, 1), Nothing
      End If
   Next i
   OrThis = Join$(dicOutput.keys, strSeparator)
   Set dicOutput = Nothing
End Function

Fazza · Jun 9, 2008

Hi pruel.

As a function, one result is returned. So, populating a cell with a text string like initially.

It is quite different to have a function populate a variable number of cells. For example, think of a normal Excel function. It could be done I think if the result was an array but the function would need to be entered into the entire results cell at the start. Not what you want, I think?

Via VBA just about anything can be done, however. Just need to be crystal clear on what is wanted and work out a suitable approach.

If a multiple cell solution is wanted, best to discuss in more detail what is required so that the best approach is taken. There are many ways to do things and better IMO to discuss & understand the requirement before working out a solution.

Regards, Fazza

Lookup Question

pruel

New Member

Excel Facts

Fazza

MrExcel MVP

Yogi Anand

MrExcel MVP

pruel

New Member

Fazza

MrExcel MVP

Similar threads

Forum statistics

Share this page

Lookup Question

pruel

New Member

Excel Facts

Fazza

MrExcel MVP

Yogi Anand

MrExcel MVP

pruel

New Member

Fazza

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock