finding nonblank cells and reporting their addresses

[kevinwc1969]

Hi! New to the forum; hoping someone can help me out.

I have a pretty big worksheet (230 columns x over a thousand rows). Any given column might have 3-4 nonblank cells; the rest are blank.

What I want to do for each column is obtain the address of each nonblank cell, then grab the contents of the corresponding cell in the B column and concatenate the results.

In the attached example, I want to put the function in d2, and the expected result would be ar 001, fc 001, hw 003.

Any help is very much appreciated! :-)

[shg]

Welcome to the forum.

If you're amenable to VBA, you can add this function to your workbook:

Function Cat(vInp As Variant, _
             Optional sSep As String = "", _
             Optional bCatEmpty As Boolean = False) As String
    ' Catenates the elements of vInp separated by sSep
    ' Empty values and null strings are ignored unless bCatEmpty is True

    Dim vItem       As Variant
    Dim sItem       As String

    If bCatEmpty Then
        For Each vItem In vInp
            Cat = Cat & CStr(vItem) & sSep
        Next vItem

    Else
        For Each vItem In vInp
            sItem = CStr(vItem)
            If Len(sItem) Then Cat = Cat & sItem & sSep
        Next vItem
    End If

    If Len(Cat) Then Cat = Left(Cat, Len(Cat) - Len(sSep))
End Function

Then in D2, =Cat(IF(D4:D18<>"",$B4:$B18,""), ", ")

The formula MUST be confirmed with Ctrl+Shift+Enter.

Then you can copy across.

[kevinwc1969]

shg,

Thanks for the welcome, and for the quick reply!

I've done some VBA in Word, but not much in Excel, so I'm not sure whether I'm doing it right. I'm getting a #NAME? error in the spreadsheet. Apologies if I'm doing something silly or n00bish...

I opened the Visual Basic editor, went to "This Workbook" under the file (VU demo.xls), and pasted in the function code. I then hit Save, went to the spreadsheet, pasted in the function call you provided, and did F2 | Ctrl-Shift-Enter. The cell then reads #NAME?.

When I click on the error tooltip and select Show Calculation Steps, it looks like it's doing everything right (reading the column, finding the nonblank cells, pulling the right stuff from column B), but that it doesn't recognize Cat.

I suspect that what I'm doing wrong is something obvious... did I put the function code in the wrong place? Or maybe it's a security setting, or something? I have Macro Security set to Medium (in case that might be relevant).

Thanks again for your help--I really appreciate it.

[kevinwc1969]

Never mind previous post--got it. I had, in fact, put the function code in the wrong place, but this page steered me right.

Looks like I'm in business. Thanks!

[shg]

Great. Would you please take a minute to read the forum rules, and then mark the thread as SOLVED?

Thanks.