How to populate names conditionally?

xlmaniac

Well-known Member
Joined
Jul 2, 2009
Messages
505
Office Version
  1. 2010
Platform
  1. Windows
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

*ABCD
1NamePresent Status*Peter/Mike/Andrew
2JohnCompleted**
3Peter***
4AmyCompleted**
5Mike***
6Andrew***

<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
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Domski

Well-known Member
Joined
Jan 18, 2005
Messages
7,292
I've modified a concatanation udf I use to do what you want:

Code:
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

Well-known Member
Joined
Jul 2, 2009
Messages
505
Office Version
  1. 2010
Platform
  1. Windows
I've modified a concatanation udf I use to do what you want:

Code:
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.:mad:
Could there be any formula driven solutions for this?
I am using excel 2010 version.
Thanks again.
 

Domski

Well-known Member
Joined
Jan 18, 2005
Messages
7,292
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

Well-known Member
Joined
Jul 2, 2009
Messages
505
Office Version
  1. 2010
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,685
Messages
5,637,798
Members
416,983
Latest member
LessThanAverageUser

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
Top