Excel VBA merge/combine columns with same name

Question

My project work has an issue similar to the one described below.

My dataset is like this

What I want is like this (LAST COLUMN)

What I have is many columns of same name like "Is_paid", "Job". What I want is to create a new column "Tot", Which combines all these "Is_Paid" and "Job" in a special manner like,

1. Combine all "Is_Paid" column into "Is_Paid_total"

2. Combine all "Job" column into Job_total

3. And the code format is (Not correct)

   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 for loop

I dont know how to merge/combine to get Is_Paid_Total and Job_Total. Also I know the if statement I have written is wrong. Please help me to tackle this problem.

Answer 1

I imagine that 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 up as a table like so:

The formula for the "Total" column is:

=IF(OR([@[is_paid]]="NonPaid",[@[is_paid2]]="NonPaid",[@[is_paid3]]="NonPaid"),"NonPaid",[@Job]&[@Job2]&[@Job3])

Answer 2

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 meet your columns, in my exampl my last column with data is G so that formula is on column H.

i think you can also use it in vba using the range().formula = "=......" and then using the filldown for all your range