Concatenate with several criteria and looping

[martinez77]

Hey all,

So I was given a somewhat small data set of 3500 rows by 10 columns.
The data set is very dirty. I need to clean it up as it is a mailing list.

So here is what I want to do

if columns B:G have values then concatenate columns (b," ","and",e," ", G) in cell __

if columns B,C,D have valuees, but columns e,f,g are blank then concatenate b,c,d in cell ___

if I can understand syntax for the above I can create the rest of the conditions on my own

Then I need to loop this code down each row one at a time.

I really want to understand the syntax so that I don't have to bother anyone in the future for conditional syntax.

Thank you in advance for your help,
Martinez

[Solus Rankin]

if columns B:G have values then concatenate columns (b," ","and",e," ", G) in cell __

Columns B & G or B thru G? What are you trying concatenate? and which cell is it supposed to go into?

[martinez77]

Correct B through G and I want the output to go to column A

I am trying to concatenate names. There is a column for husband and wife prefix, first name, and last name each.
Depending on what is present I need to concatenate those columns in a specific order as shown in my example.

I hope that clarifies
Thanks

Columns B & G or B thru G? What are you trying concatenate? and which cell is it supposed to go into?

[protonLeah]

To attach a Workbook
(please do not post pictures of worksheets)

• Click Advanced (next to quick post),
• Scroll down until you see "Manage Attachments",
• Click that then select "add files" (top right corner).
• Click "Select Files" find your file, click "open" click "upload"
• Once the upload is completed the file name will appear below the input boxes in this window.
• Click "Done" at bottom right to close the Attachment Manager.
• Click "Submit Reply"

[Solus Rankin]

Dim l As Long
Dim lRow As Long
Dim cel As Range
Dim x As Integer

lRow = Range("B" & Rows.Count).End(xlUp).Row

For l = 1 To lRow
    x = 0
    For Each cel In Range("B" & l & ":G" & l)
        If cel.Value <> "" Then
            x = x + 1
        End If
    Next cel
    If x = 6 Then
        Range("A" & l).Value = Range("B" & l).Value & " " & Range("E" & l).Value & " " & Range("G" & l).Value
    Else:
        Range("A" & l).Value = Range("B" & l).Value & " " & Range("C" & l).Value & " " & Range("D" & l).Value
    End If
Next l
    

End Sub

[martinez77]

OK so that code does exactly what I described which is stellar. I am having trouble following the logic.
If I wanted to add statements how would I go about it. My goal is to understand the syntax also.

if x = 6 then statement confuses me. Could you explain?

I will need for example to add a statement that states

if columns bcd are empty, but columns efg are not then concatenate efg in column a.

what would that statement look like?

Thanks again

I need to add a few more statements

[Solus Rankin]

lRow = Range("B" & Rows.Count).End(xlUp).Row

For l = 1 To lRow

Begins to loop through all rows

    For Each cel In Range("B" & l & ":G" & l)

loops through B:G in a row

If cel.Value <> "" Then
            x = x + 1
        End If

Counts each cell in B:G that is not empty

If x = 6 Then
        Range("A" & l).Value = Range("B" & l).Value & " " & Range("E" & l).Value & " " & Range("G" & l).Value

If none are empty in concatenates these values.

 Else:
        Range("A" & l).Value = Range("B" & l).Value & " " & Range("C" & l).Value & " " & Range("D" & l).Value

Else if some are empty it concatenates these cells.

[Solus Rankin]

However, if you're going to add criteria you should probably use Select Case