Try
=INDEX(A:A,(ROW(A1)-1)*3+1)&INDEX(A:A,(ROW(A1)-1)*3+2)&INDEX(A:A,(ROW(A1)-1)*3+3)
i want to start the autofill the concatenate from last argument.
like in B1 =Concatenate(A1,A2,A3) then i want
B2 =Concatenate(A4,A5,A6)
so in this example if 1st formula end with A3 then next formula in otherline start with next A4 not with A2.
if somebody have can help tell how i can do that .. it will be awesome.. thanks in advance
Try
=INDEX(A:A,(ROW(A1)-1)*3+1)&INDEX(A:A,(ROW(A1)-1)*3+2)&INDEX(A:A,(ROW(A1)-1)*3+3)
Cross Posted
Ozgrid: ozgrid.com /forum/showthread.php?t=175747
MrExcel: mrexcel.com /forum/excel-questions/688884-concatenate-continuing-last-argument.html
You will need to add a user defined function (ALT+F11, Insert|Module) then paste this code:
Then the formula in your workbook would be:Code:Public Function aconcat(a As Variant, Optional sep As String = "") As String ' Harlan Grove, Mar 2002 Dim y As Variant If TypeOf a Is Range Then For Each y In a.Cells aconcat = aconcat & y.Value & sep Next y ElseIf IsArray(a) Then For Each y In a aconcat = aconcat & y & sep Next y Else aconcat = aconcat & a & sep End If aconcat = Left(aconcat, Len(aconcat) - Len(sep)) End Function
=aconcat(INDEX(A:A,1+((ROW(A1)-1)*90)):INDEX(A:A,90+((ROW(A1)-1)*90)))
This is awesome it made my work when i applying in sheet... problem came when i was making a macro and put formula for a cell and paste VB in worksheet vbaproject not in macro module and it doesnt working in macro.. can u help.????
Bookmarks