Concatenating cell values

**tray262** · 11-08-2012, 12:38 AM

This_is what_I_have.jpg So this is what I have. I'm using it the same way as before. The only thing I need to add is if Let's say student 1, has 2 zeros that actually have assignmets in column A. So I want this function to grab the assessment name from column A unless the assessment name is Zero. Hope that make sense.

Thanks again.

**tray262** · 11-08-2012, 12:58 AM

Actually I think I got it after a little playing around. I think I'm starting to "get it" Please let me know if you see anything wrong with this. I added the sections in red. I think what I did was I added a 2nd check. So if it checks two things now. Please let me know what ya think. Thanks so much!

Function ConcatText(rng As Range, MatchVal As String) As String
Dim RetVal As String
Dim cCell As Range
Dim ColRef As Long
MatchVal = UCase(MatchVal)

RetVal = ""
For Each cCell In rng
    If UCase(cCell.Text) = MatchVal Then
        ColRef = cCell.Column
    If cCell.Offset(ColumnOffset:=-ColRef + 1).Text <> 0 Then       
RetVal = RetVal & cCell.Offset(ColumnOffset:=-ColRef + 1).Text & " | "
    End If
    End If
Next cCell
If RetVal <> "" Then
    ConcatText = Left(RetVal, Len(RetVal) - 2)
Else
    ConcatText = "None"
End If

End Function

**Ron Coderre** · 11-08-2012, 10:23 AM

1) You made a correction that does what you want.
2) I'd tweak it a bit to make it a little more readable and efficient:

Function ConcatText(rng As Range, MatchVal As String) As String
Dim RetVal As String
Dim cCell As Range
Dim ColRef As Long
Dim NewItem As String
MatchVal = UCase(MatchVal)

RetVal = ""
For Each cCell In rng
    If UCase(cCell.Text) = MatchVal Then
        ColRef = cCell.Column
        NewItem = cCell.Offset(ColumnOffset:=-ColRef + 1).Text
        If NewItem <> "0" Then
            RetVal = RetVal & NewItem & " | "
        End If
    End If
Next cCell

If RetVal <> "" Then
    ConcatText = Left(RetVal, Len(RetVal) - 3)
Else
    ConcatText = "None"
End If

End Function

**tray262** · 11-11-2012, 08:33 AM

Thanks once again! I'm really trying to get the concept. Can you recommend any good book for this type of programming inside of excel. I'm going to try to and attempt to re-code what you've come up with and add a couple things to the function. I'm really having a lot of fun with this. This is what I'm going to try and do.

Function ConcatText(rng As Range, MatchVal As String,UpOrOver As String,WhichPositionToGet as String) As String

Basically the "UpOrOver As String" I'm thinking of making the program either be able to grab the info from the same column or if the data is going the other direction it will grab it from the row. So this is basically going to change if it's going to be a Row offset or a Column offset.

"WhichPositionToGet" I'm going to make a variable, instead of hardcoding that it is column 1 or row 1, I want to be able to set that in the function. I"m thinking that this will make it a lot more fuctionable for me.

I'll post some results. I think I've got the bug. Gotta go do some reading.

Thanks!
Tray262

**tray262** · 11-11-2012, 09:22 AM

Maybe not. lol.
Just getting #/Value. Any suggestions.

Function ConcatTextAll(rng As Range, MatchVal As String, UpOrOver As String, WhichPosition As String) As String
Dim RetVal As String
Dim cCell As Range
Dim RefPosition As Long
Dim NameOfPosition As String
Dim UpOrOver As String
Dim WhichPosition As String
MatchVal = UCase(MatchVal)


RetVal = ""
    If UpOrOver = "O" Or "U" Then
        If UpOrOver = "U" Then
        For Each cCell In rng
            If UCase(cCell.Text) = MatchVal Then
                RefPosition = cCell.Row
                NameOfPosition = cCell.Offset(RowOffset:=-RefPosition + WhichPosition).Text
                If NameOfPosition <> "0" Then
                    RetVal = RetVal & NameOfPosition & " | "
                End If
            End If
        Next cCell
        End If
        
        If UpOrOver = "O" Then
          If UCase(cCell.Text) = MatchVal Then
                RefPosition = cCell.Column
                NameOfPosition = cCell.Offset(ColumnOffset:=-RefPosition + WhichPosition).Text
                If NameOfPosition <> "0" Then
                    RetVal = RetVal & NameOfPosition & " | "
                End If
            End If
        Next cCell
        End If
    
    End If

    If RetVal <> "" Then
        ConcatTextAll = Left(RetVal, Len(RetVal) - 3)
    Else
        ConcatTextAll = "None"
    End If

End Function