Excel VBA concatenate / concatenate columns with the same name
My work on a project has a problem similar to the one below.
My dataset is like this
What I want is this (LAST COLUMN)
I have many columns with the same name as "Is_paid", "Job". I want to create a new column "Tot" that combines all these "Is_Paid" and "Job" in a special way, for example
- Combine all columns "Is_Paid" into "Is_Paid_total"
-
Combine all "Job" column in Job_total
-
And the format of the code (Wrong)
Private Sub CommandButton1_Click() Dim MyWorksheetLastColumn As Byte MyWorksheetLastColumn = Worksheets(1).Cells(1, Columns.Count).End(xlToLeft).Column Worksheets(1).Cells(1, MyWorksheetLastColumn + 1).Value = "Tot_Employment" Dim rngTemp As Range Set rngTemp=Cells.Find("*",SearchOrder:=xlByRows,SearchDirection:=xlPrevious) With rngTemp For Each cel In Range(Cells(1, 1), rngTemp) '****************MERGING STEPS********************** If cel.column(Is_Paid_total)="NonPaid" then Tot=Is_Paid else Tot=Job_total
End If Next Cel End With End Sub
Step 3 will be in a loop
I don't know how to combine / merge to get Is_Paid_Total and Job_Total. Also I know that the if statement I wrote is not correct. Please help me to solve this problem.
source to share
I guess someone can give you a better answer, however the best way I can think of to meet the criteria in your last comment is to set the data as a table like this: 
The formula for the "Total" column is:
=IF(OR([@[is_paid]]="NonPaid",[@[is_paid2]]="NonPaid",[@[is_paid3]]="NonPaid"),"NonPaid",[@Job]&[@Job2]&[@Job3])
source to share
I have a different formula, but the idea is almost the same:
your last column should have this formula
IF(ISERROR(MATCH("NonPaid",$A2:$G2,0)),OFFSET(INDIRECT(ADDRESS(ROW($D2),MATCH("Paid",$A2:$G2,0))),0,1),"NonPaid")
you need to adapt it to fit your columns, in my example my last data column is G so the formula is in column H.
I think you can also use it in vba using range (). formula = "= ......" and then using padding for the whole range
source to share