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-C01
--------------------


On another worksheet, I have the following:


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


I'd like the Initiating Nodes column to be filled with an array of
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, 5
--------------------


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

Thanks :)
Drew


--
AthleteTO
------------------------------------------------------------------------
AthleteTO's Profile: http://www.excelforum.com/member.php...o&userid=15943
View this thread: http://www.excelforum.com/showthread...hreadid=274218

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



--
Regards
Frank Kabel
Frankfurt, Germany


AthleteTO wrote:
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-C01
--------------------


On another worksheet, I have the following:


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


I'd like the Initiating Nodes column to be filled with an array of
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, 5
--------------------


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

Thanks :)
Drew

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


"AthleteTO" wrote in message
...

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-C01
--------------------


On another worksheet, I have the following:


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


I'd like the Initiating Nodes column to be filled with an array of
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, 5
--------------------


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

Thanks :)
Drew


--
AthleteTO
------------------------------------------------------------------------
AthleteTO's Profile:
http://www.excelforum.com/member.php...o&userid=15943
View this thread: http://www.excelforum.com/showthread...hreadid=274218

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)))),"",IN DEX
(Sheet1!$A$2:$A$7,SMALL(IF(Sheet1!$B$2:$B$7=$A1,RO W
($A$1:$A$6)),COLUMN(A:A)))

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

Biff

-----Original Message-----

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-C01
--------------------


On another worksheet, I have the following:


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


I'd like the Initiating Nodes column to be filled with an
array of
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, 5
--------------------


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

Thanks :)
Drew


--
AthleteTO
----------------------------------------------------------
--------------
AthleteTO's Profile: http://www.excelforum.com/member.php?
action=getinfo&userid=15943
View this thread:
http://www.excelforum.com/showthread...hreadid=274218

.

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

Let Sheet1!A1:B7 house the source sample and Sheet2!A1:C4 the
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 just
with enter) and copy down.


AthleteTO Wrote:
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-C01
--------------------


On another worksheet, I have the following:


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


I'd like the Initiating Nodes column to be filled with an array of
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, 5
--------------------


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

Thanks :)
Drew


--
Aladin Akyurek
------------------------------------------------------------------------
Aladin Akyurek's Profile: http://www.excelforum.com/member.php...fo&userid=4165
View this thread: http://www.excelforum.com/showthread...hreadid=274218

AthleteTO wrote in
:

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.