How to populate names conditionally?

[xlmaniac]

Dear All,
I do have the following data set.
I would like to populate the names in the cell D1 against whom the cells are blank in column B
I am looking for a formula in D1 which can return the result as follows.
Thanks
Sheet1

*	A	B	C	D
1	Name	Present Status	*	Peter/Mike/Andrew
2	John	Completed	*	*
3	Peter	*	*	*
4	Amy	Completed	*	*
5	Mike	*	*	*
6	Andrew	*	*	*

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 64px;"><col style="width: 109px;"><col style="width: 64px;"><col style="width: 135px;"></colgroup><tbody>
</tbody>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

 

[Domski]

I've modified a concatanation udf I use to do what you want:

Public Function ConcatAll(rngConcat As Range, Optional strSpacer As String, Optional InclBlanks As Variant = True) As String

    Dim rngLoopRange As Range


    For Each rngLoopRange In rngConcat
        If rngLoopRange.Offset(0, 1) = "" Then
            If InclBlanks Then
                If ConcatAll = "" Then
                    ConcatAll = rngLoopRange
                Else
                    ConcatAll = ConcatAll & strSpacer & rngLoopRange
                End If
            Else
                If rngLoopRange <> "" Then
                    If ConcatAll = "" Then
                        ConcatAll = rngLoopRange
                    Else
                        ConcatAll = ConcatAll & strSpacer & rngLoopRange
                    End If
                End If
            End If
        End If
    Next rngLoopRange


End Function

Pop the code in a standard module in your workbook and then use it like: =concatall(A2:A6,"/")

Dom

 

[xlmaniac]

I've modified a concatanation udf I use to do what you want:

Public Function ConcatAll(rngConcat As Range, Optional strSpacer As String, Optional InclBlanks As Variant = True) As String

    Dim rngLoopRange As Range


    For Each rngLoopRange In rngConcat
        If rngLoopRange.Offset(0, 1) = "" Then
            If InclBlanks Then
                If ConcatAll = "" Then
                    ConcatAll = rngLoopRange
                Else
                    ConcatAll = ConcatAll & strSpacer & rngLoopRange
                End If
            Else
                If rngLoopRange <> "" Then
                    If ConcatAll = "" Then
                        ConcatAll = rngLoopRange
                    Else
                        ConcatAll = ConcatAll & strSpacer & rngLoopRange
                    End If
                End If
            End If
        End If
    Next rngLoopRange


End Function

Pop the code in a standard module in your workbook and then use it like: =concatall(A2:A6,"/")

Dom

Hi Domski,
Thank you so much for all your help.Really appreciate the same.
I am not at all conversant with Macro.
Could there be any formula driven solutions for this?
I am using excel 2010 version.
Thanks again.

 

[Domski]

Not very easily. The code is quite easy to use. Follow the steps here on adding code to a regular module (steps 1 to 5) and then just use the function like you would any other formula: =CONCATALL(A2:A6,"/")

Dom

 

[xlmaniac]

Not very easily. The code is quite easy to use. Follow the steps here on adding code to a regular module (steps 1 to 5) and then just use the function like you would any other formula: =CONCATALL(A2:A6,"/")

Dom

Hi Domski,
OK.
Thank you once again for your help.
Regards