# i need a macro to (merge) concatenate a number(example-5cells of a column[example at the 1st column]) and do it that every 4 columns?

i need a macro to (merge) concatenate a number Variable X (example-4cells of a column) ,
Variable Y  [example at the 1st column] and do it that every Variable Z=X+N(rows) {example every 4-rows}
i am sending  an excel
test-macro.xlsm
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Finance AnalystCommented:

In column B, copied down from cell B2:
=IF(MOD(ROW()-1,\$E\$8)=1,\$H\$3&A2&\$H\$4,B1&\$H\$3&A2&\$H\$4)

In column C, copied down from cell C2:
=IF(MOD(ROW(),\$E\$8)-1<>0,"",B2)

Cells referred to in formula:

H3 = ""
H4 = ;;
E8 = 4

Column B concatenates the values of each row in column A, restarting every 4th row.
Column C pulls the result from column B every 4th row, leaving the other 3 blank.

You can then filter and copy and paste the result from col c to where required.

Thanks
Rob H
Commented:
Hi,

as a macro
``````Sub macro()

myCnt = Range("A" & Cells.Rows.Count).End(xlUp).Row - 1
For Idx = 1 To myCnt / 4 + 1 Step 1
Range("E" & Idx + 1) = _
[B2] & Range("A" & 1 + ((Idx - 1) * 4) + 1) & [B3] & _
[B2] & Range("A" & 1 + ((Idx - 1) * 4) + 2) & [B3] & _
[B2] & Range("A" & 1 + ((Idx - 1) * 4) + 3) & [B3] & _
[B2] & Range("A" & 1 + ((Idx - 1) * 4) + 4) & [B3]
Range("E" & Idx + 1).Value = Replace(Range("E" & Idx + 1).Value, Chr(34) & Chr(34) & ";;", "")
Next
End Sub
``````
Regards

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author Commented:
there is an error that prevents the formula to be executed ,maybe i am do it wrong
if you can do it as macro i could understand better ,thanks anyway
test-macro.xlsm
Finance AnalystCommented:
Looks like you entered it as text, delete the apostrophe at the start of the formula in B2 and C2 and then copy down.

Corrected file attached.
test-macro-1.xlsm
Commented:
Corrected code

``````Sub macro()

myCnt = Range("A" & Cells.Rows.Count).End(xlUp).Row - 1
For Idx = 1 To myCnt / 4 + 1 Step 1
Range("E" & Idx + 1) = _
[B2] & Range("A" & 1 + ((Idx - 1) * 4) + 1) & [B3] & _
[B2] & Range("A" & 1 + ((Idx - 1) * 4) + 2) & [B3] & _
[B2] & Range("A" & 1 + ((Idx - 1) * 4) + 3) & [B3] & _
[B2] & Range("A" & 1 + ((Idx - 1) * 4) + 4) & [B3]
Range("E" & Idx + 1).Value = Replace(Range("E" & Idx + 1).Value, [B3]& [B2], "")
Next
End Sub
``````
Author Commented:
Thanks for the right answers both, tested  ok