+ Reply to Thread
Results 1 to 5 of 5

Concatenate Two Rows at a Time; Excel not recognizing pattern

  1. #1
    Registered User
    Join Date
    05-17-2019
    Location
    California
    MS-Off Ver
    2016
    Posts
    19

    Concatenate Two Rows at a Time; Excel not recognizing pattern

    I have a list of students that I want to put into groups of two. I am trying to use the concatenate formula to combine the first two cells in the column (A2 & A3). The second concatenate should go to A4 & A5, and so on. However, when I click and drag or copy and paste excel goes to the next line (A5 & A6) instead of A6 & A7. I am unsure if I should use another formula like transpose with an array, or find another way to make excel recognize the pattern.

    I would also like to know how to do it for groups of 3 & 4 as well. Any help is appreciated.

    Please see the attachment for the list of students and what the output should be. I entered the output by hand (manually typing the formula) but I would love to see if there was a faster way.
    Attached Files Attached Files
    Last edited by profess; 10-09-2019 at 12:02 PM.

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Concatenate Two Rows at a Time; Excel not recognizing pattern

    You can use this formula in cell C2:

    =INDEX(A:A,(ROWS($1:1)-1)*2+2)&" and "&INDEX(A:A,(ROWS($1:1)-1)*2+3)

    then copy down as required (until you start to get just " and " in the cells).

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    05-17-2019
    Location
    California
    MS-Off Ver
    2016
    Posts
    19

    Re: Concatenate Two Rows at a Time; Excel not recognizing pattern

    This is working great! Thank you so much!

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Concatenate Two Rows at a Time; Excel not recognizing pattern

    You're welcome - glad to hear it.

    If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

    Also, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

    Pete

  5. #5
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Concatenate Two Rows at a Time; Excel not recognizing pattern

    Quote Originally Posted by profess View Post
    … I would also like to know how to do it for groups of 3 & 4 as well. Any help is appreciated...
    I've just noticed this from your first post - you must have added this after I started my reply.

    If you wanted to do it for groups of 3, then you would need 3 INDEX expressions, each separated by an " and ". Also, the *2, which occurs twice, will have to become *3 for a group of 3, and the +2 and +3 is there because your data begins on row 2 of column A and you want the data from row 2 and row 3. For a group of 3 students this would have to be +2, +3 and +4. So, the formula would become:

    =INDEX(A:A,(ROWS($1:1)-1)*3+2)&" and "&INDEX(A:A,(ROWS($1:1)-1)*3+3)&" and "&INDEX(A:A,(ROWS($1:1)-1)*3+4)

    to get groups of 3 students. Use the same logic to get groups of 4 students.

    Hope this helps.

    Pete

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Help recognizing a pattern when copying cells. OFFSET function?
    By madboy9 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-06-2017, 08:43 PM
  2. [SOLVED] Recognizing a pattern of 0's
    By gryanb in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-14-2016, 02:21 PM
  3. Excel autofill not recognizing increment pattern.
    By s.hill in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-23-2015, 12:42 PM
  4. [SOLVED] Autofill Issues- recognizing pattern
    By ryanaskren in forum Excel General
    Replies: 3
    Last Post: 08-28-2014, 09:45 AM
  5. [SOLVED] Autofill not recognizing pattern
    By WILLPOU in forum Excel General
    Replies: 4
    Last Post: 11-05-2013, 11:24 PM
  6. pattern recognizing in excel for a large data set
    By maulik1k in forum Excel Programming / VBA / Macros
    Replies: 28
    Last Post: 10-09-2013, 10:27 PM
  7. Replies: 4
    Last Post: 06-09-2010, 11:54 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1