Function/Formula to return more than one result to a single cell

[Reneek]

I have a spreadsheet which calculates a grid reference

Based on the resultant grid reference i then want the name of that task to be identified in the grid

Using the formaula below - i have managed to get this to happen, however some grid references will have more than one task to go to it

I have managed to get it to return multiple task names with spaces but cannot get it to move to the next record?

=IFERROR(INDEX($O$2:$O$11,MATCH(C3,$N$2:$N$11,0)),0)&" "&IFERROR(INDEX($O$2:$O$11,MATCH(C3,$N$2:$N$11,0)),0)&" "&IFERROR(INDEX($O$2:$O$11,MATCH(C3,$N$2:$N$11,0)),0)

So in the grid where i want the results i get the same task repeated, as below (in a single cell)
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Risk1<o></o>
Risk1<o></o>
Risk1


But it should read (in a single cell)
Risk 1
Risk 2
Risk 8


The grid reference is calculated to N2:N10 and the task name is shown in O2:010 (it is this task name/s which i want to return into the grid)

 

[Aladin Akyurek]

For having the results in a single cell, you could add
the following function code in VBA to your workbook as a module.

In order to add the function to your workbook, run...

ALt+F11
Insert | Module
File | Close and Return to Microsoft Excel.
_________________________________________________________

Function aconcat(a As Variant, Optional sep As String = "") As String
' Harlan Grove, Mar 2002
Dim y As Variant
If TypeOf a Is Range Then
For Each y In a.Cells
aconcat = aconcat & y.Value & sep
Next y
ElseIf IsArray(a) Then
For Each y In a
aconcat = aconcat & y & sep
Next y
Else
aconcat = aconcat & a & sep
End If
aconcat = Left(aconcat, Len(aconcat) - Len(sep))
End Function
________________________________________________________

Now equipped with ACONCAT...

Control+shift+enter, not just enter:

=SUBSTITUTE(aconcat(IF($N$2:$N$11=C3,", "&$O$2:$O$11,"")),", ","",1) <!-- / message --><!-- sig -->
__________________
Microsoft MVP - Excel

 

[Reneek]

That is awesome - thank you so much.

The data returned is

Risk1,
Risk2,
Risk8

Is there any way i can remove the comma after the first two?

 

[T. Valko]

That is awesome - thank you so much.

The data returned is

Risk1,
Risk2,
Risk8

Is there any way i can remove the comma after the first two?

Try this...

Still array entered**:

=SUBSTITUTE(aconcat(IF($N$2:$N$11=C3," "&$O$2:$O$11,""))," ","",1)

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[Aladin Akyurek]

That is awesome - thank you so much.

You are welcome.

The data returned is

Risk1,
Risk2,
Risk8

Is there any way i can remove the comma after the first two?

Yes, you can. The formula uses a comma followed by a space as separator. This can be reduced to just space or even replaced by alt+enter...

=SUBSTITUTE(ACONCAT(IF($N$2:$N$11=C3," "&$O$2:$O$11,""))," ","",1)

If you'd opt for alt+enter...

=SUBSTITUTE(ACONCAT(IF($N$2:$N$11=C3,CHAR(10)&$O$2:$O$11,"")),CHAR(10),"",1)

Recall that you need to confirm such formulas with control+shift+enter.

You would need to activate Wrap text for the formula cells if you opt for for the latter.