Tuesday, 6 December 2016

Excel Conundrums- Formula to produce a comma separated list

In this one we need a formula to generate a comma separated list but being a formula it has to upf=date to match the data

Generate a comma separated list of header values

Using there macro is harder than the function but here's the reverse the macro is easier than the formula.



The absolute references are required when the formula is copied down.

StringMeUp User defined function


Here's the code in plain text if you wish to copy and paste:

Function StringMeUp(HeaderRow As Range, DataRow As Range) As String
 
    On Error Resume Next
 
    'Loop through data cells.
    For i = 1 To DataRow.Cells.Count
        'Cell contains entry?
        If Not IsEmpty(DataRow.Cells(i)) Then
            'Build string.
            StringMeUp = StringMeUp & HeaderRow.Cells(i) & ","
        End If
    Next
    'Snip off the trailing comma.
    StringMeUp = Left(StringMeUp, Len(StringMeUp) - 1)

End Function

Excel Conundrums


These are tricky problems. The names have been changed to protect the innocent. No propriety value.










No comments:

Post a Comment