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 enter image description here

What I want is like this (LAST COLUMN) enter image description here

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,

Combine all "Is_Paid" column into "Is_Paid_total"
Combine all "Job" column into Job_total

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.

Why not just loop through the rows looking for "nonpaid" and placing that in the column, otherwise looking for the second non-blank and placing that in the column? — Raystafarian
– Raystafarian, Commented Jun 3, 2015 at 13:46
I tried all permutations and combinations. And I miss something. Can you pls write in coded form? — Abdul Shiyas
– Abdul Shiyas, Commented Jun 3, 2015 at 13:49
@AbdulShiyas Well I won't post this as an answer then but I was able to make that last column by just using =INDEX(D3:I3,MATCH("*",D3:I3,-1)) and autofilling down. Maybe we can utilize this within the Application.Worksheetfunction or Evaluate methods... — chancea
– chancea, Commented Jun 3, 2015 at 13:58
If that is a consideration, and assuming that you would still want the "total" to display "NonPaid", then you could go with =IF(OR(D3="NonPaid",F3="NonPaid",H3="NonPaid"),"NonPaid",E3&G3&I3) Otherwise @chancea 's answer works great. — Clif
– Clif, Commented Jun 3, 2015 at 14:09

Clif · Accepted Answer · 2015-06-03 14:41:08Z

2

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: enter image description here

The formula for the "Total" column is:

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

answered Jun 3, 2015 at 14:41

Clif

5144 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Abdul Shiyas Over a year ago

Can I get this using vba ?

Clif Over a year ago

Not sure about that, hopefully someone else will be able to comment on or answer that question.

Federico Sanchez · Accepted Answer · 2015-06-03 15:44:22Z

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

answered Jun 3, 2015 at 15:44

Federico Sanchez

1451 gold badge2 silver badges12 bronze badges

Collectives™ on Stack Overflow

Excel VBA merge/combine columns with same name

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related