# Concatenate Two Rows at a Time; Excel not recognizing pattern

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

2. ## 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. ## Re: Concatenate Two Rows at a Time; Excel not recognizing pattern

This is working great! Thank you so much!

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

You're welcome - glad to hear it.

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. ## Re: Concatenate Two Rows at a Time; Excel not recognizing pattern

Originally Posted by profess
… 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

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