Stumped! Collecting values into one cell.

[AthleteTO]

OK, this one has me stumped. Anyone with creative ideas?

I have one worksheet with values such as this:


Code
-------------------
Node ReuseCode
2 C-C01
3 C-C03
4 C-C01
5 C-C03
6 C-C02
7 C-C0
-------------------


On another worksheet, I have the following:


Code
-------------------
ReuseCode Name Initiating Nodes
C-C01 Policy A
C-C02 Policy B
C-C03 General Informatio
-------------------


I'd like the Initiating Nodes column to be filled with an array o
values from the first worksheet. The result would look like this:


Code
-------------------
ReuseCode Name Initiating Nodes
C-C01 Policy A 2, 4, 7
C-C02 Policy B 6
C-C03 General Information 3,
-------------------


I've fiddled with formulas, but I am positively stumped. Three cheer
for any bright ideas!

Thanks
Dre

 

[Frank Kabel]

Hi
not possible with formulas alone. You'll need VBA. Maybe try Alan
Beban's VLOOKUPS UDF to return multiple results (
§ http://home.pacbell.net/beban

 

[Bernie Deitrick]

Drew,

You could use a User-Defined-Function, definition below. Copy the code and
paste into a standard codemodule of your workbook. Used like:

=Nodes(A2,Sheet1!$B$2:$B$7,Sheet1!$A$2:$A$7)

Where A2 has the reuse code you are interested in, B2:B7 of Sheet1 has the
reuse code table, and A2:A7 of Sheet1 has the Node values.

HTH,
Bernie
MS Excel MVP

Function Nodes(inVal As Range, _
InCode As Range, _
InNode As Range) As Variant

Dim i As Integer

Nodes = "None"

If InCode.Cells.Count <> InNode.Cells.Count Then
Nodes = "Unbalanced entry ranges"
Exit Function
End If

If inVal.Cells.Count <> 1 Then
Nodes = "Too many key values"
Exit Function
End If

For i = 1 To InCode.Cells.Count
If InCode.Cells(i).Value = inVal.Value Then
If Nodes = "None" Then
Nodes = InNode.Cells(i).Value
Else
Nodes = Nodes & ", " & InNode.Cells(i).Value
End If
End If
Next i

End Function

 

[Biff]

Hi!

I can get the data extracted into individual cells but
cannot get an array of values in a single cell:

Assume the raw data is on Sheet1 A2:B7.

On the summary sheet, the reuse codes are listed in column
A1:Ax

In C1 enter this array formula with the key combo of
CTRL,SHIFT,ENTER. Copy down then across:

=INDEX(Sheet1!$A$2:$A$7,SMALL(IF(Sheet1!$B$2:$B$7=$A1,ROW
($A$1:$A$6)),COLUMN(A:A)))

When data is exhausted that meets the certain criteria a
#NUM! error will be returned. You can suppress the errors
by using this formula:

=IF(ISERROR(INDEX(Sheet1!$A$2:$A$7,SMALL(IF(Sheet1!
$B$2:$B$7=$A1,ROW($A$1:$A$6)),COLUMN(A:A)))),"",INDEX
(Sheet1!$A$2:$A$7,SMALL(IF(Sheet1!$B$2:$B$7=$A1,ROW
($A$1:$A$6)),COLUMN(A:A)))

Or, you could use a conditional format to hide the #NUM!
return.

Biff

 

[Aladin Akyurek]

If you have the morefunc.xll add-in...

Let Sheet1!A1:B7 house the source sample and Sheet2!A1:C4 th
destination area.

C2 on Sheet2:

=SUBSTITUTE(MCONCAT(IF(Sheet1!$B$2:$B$7=A2,", "&Sheet1!A2:A7,"")),"
","",1)

which you need to confirm with control+shift+enter (instead of jus
with enter) and copy down.

 

[Jonathan Rynd]

Node ReuseCode
2 C-C01
3 C-C03
4 C-C01
5 C-C03
6 C-C02
7 C-C01
--------------------
ReuseCode Name Initiating Nodes
C-C01 Policy A
C-C02 Policy B
C-C03 General Information
--------------------

Let Sheet1!A1:B7 house the source sample and Sheet2!A1:C4 the
destination area.

Put the flollowing into C2 on sheet2, and fill down:

=mid(if(c1=sheet1!$B$2,", "&sheet1!$A$2,"")&
if(c1=sheet1!$B$3,", "&sheet1!$A$3,"")&
if(c1=sheet1!$B$4,", "&sheet1!$A$4,"")&
if(c1=sheet1!$B$5,", "&sheet1!$A$5,"")&
if(c1=sheet1!$B$6,", "&sheet1!$A$6,"")&
if(c1=sheet1!$B$7,", "&sheet1!$A$7,""),3,1000)

In principle this is extendible to any number of nodes, however the
problem is that adding and deleting is very difficult. You should
probably use one of the other suggested methods. But it can be done with
Excel formulas.