concatenate continuing from last argument?

dheeraj27

New member
Joined
Mar 1, 2013
Messages
4
Reaction score
0
Points
0
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​
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
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)
 

dheeraj27

New member
Joined
Mar 1, 2013
Messages
4
Reaction score
0
Points
0
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)


It helped to merge 2 cell and continue with next in other .. extaly wt i am looking fo...

but i need to do 90 field on 1 cell (like A1 to A90 next cell A91 to A180 then A181 to 270) any help on this
 

Palmetto

New member
Joined
Apr 21, 2012
Messages
5
Reaction score
0
Points
0
Cross Posted

Ozgrid: ozgrid.com /forum/showthread.php?t=175747

MrExcel: mrexcel.com /forum/excel-questions/688884-concatenate-continuing-last-argument.html
 

dheeraj27

New member
Joined
Mar 1, 2013
Messages
4
Reaction score
0
Points
0
Cross Posted

Ozgrid: ozgrid.com /forum/showthread.php?t=175747

MrExcel: mrexcel.com /forum/excel-questions/688884-concatenate-continuing-last-argument.html

i dont know about re posting.. i am new... sorryyy for that...

but can help
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
You will need to add a user defined function (ALT+F11, Insert|Module) then paste this code:


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

Then the formula in your workbook would be:


=aconcat(INDEX(A:A,1+((ROW(A1)-1)*90)):INDEX(A:A,90+((ROW(A1)-1)*90)))
 

dheeraj27

New member
Joined
Mar 1, 2013
Messages
4
Reaction score
0
Points
0
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.????
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
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.????

Since Palmetto has showed the links.... I don't want to repeat everything in multiple forums... let's continue at the other link.
 
Top