Lookup Question

P

pruel

New Member
Joined
Jun 8, 2008
Messages
2
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!
 
Last edited:

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Sort by date Sort by votes
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
 
Upvote 0
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
ABCD
1Data1Data2**
2--------------------------**
3red0*orange,green,blue
4orange1**
5yellow0**
6green7**
7blue3**
8indigo0**
9violet0**
10****
Sheet5 (2)


</body></html>
 
Upvote 0
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
Click to expand...
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Solved
LOOKUP & COMBINE
Replies
2
Views
118
ekhawaja
E
S
  • Solved
Worksheet Change Event
Replies
3
Views
553
sspatriots
S
L
  • Question Question
Multi-dimensional INDEX/MATCH using the table ROW and COLUMN headers to search
Replies
1
Views
828
Legacy 143009
L
T
  • Question Question
Sum-Unique - filter
Replies
8
Views
475
Peter_SSs
Peter_SSs
B
  • Solved
Find value within a cell with multiple text separated by comma
Replies
3
Views
3K
Fluff
Fluff

Forum statistics

Threads
1,223,227
Messages
6,170,853
Members
452,361
Latest member
d3ad3y3

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top