Results 1 to 8 of 8

Thread: concatenate continuing from last argument?

  1. #1

    concatenate continuing from last argument?



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #2
    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)

  3. #3
    Quote Originally Posted by Bob Phillips View Post
    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

  4. #4
    Cross Posted

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

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

  5. #5
    Quote Originally Posted by Palmetto View Post
    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

  6. #6
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    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)))


  7. #7
    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.????

  8. #8
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    Quote Originally Posted by dheeraj27 View Post
    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.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •