Concatenate multiple cells with entire column-formula help

I am trying to concatenate different cells down a column with a list in another column. IE. I want word A1 to concatenate with B1-B3, then A2 to concatenate with B1-B3, etc.

I need the results to display in a 3rd column. I also need the formula to return a null value when it hits B4 and the value is blank. I don't want A1, 2, 3 just repeating themselves down the list.

I hope this makes sense. I have included an example spreadsheet. Any help would be greatly appreciated. This one is getting the best of me.

Originally Posted by Andy Pope

If you name the range A2:A6 FIRST_WORDS
and the range B2:B5 WITH_WORDS

you can use the following in C2 and down

C2: =IF(MOD(ROW()-1,ROWS(WITH_WORDS))=0,"",INDEX(FIRST_WORDS,INT((ROW()-1)/(ROWS(WITH_WORDS)))+1,1) & " "&INDEX(WITH_WORDS,(MOD(ROW()-1,ROWS(WITH_WORDS))),1))

Wow, that is great!!! Only one minor issue though. In rang B2:B5 its working for every value except for the last one B5 (example below should have Georgia-Bulldogs house flag after Georgia-Bulldogs banner) , it's return a blank field. So my data is looking like this with a blank cell between each execution of range A1-A5. I do not need a blank cell between each execution of rows.

Georgia-Bulldogs 1 sided flag
Georgia-Bulldogs applique flag
Georgia-Bulldogs banner

University of Georgia 1 sided flag
University of Georgia applique flag
University of Georgia banner

Originally Posted by Andy Pope

If you name the range A2:A6 FIRST_WORDS
and the range B2:B5 WITH_WORDS

you can use the following in C2 and down

C2: =IF(MOD(ROW()-1,ROWS(WITH_WORDS))=0,"",INDEX(FIRST_WORDS,INT((ROW()-1)/(ROWS(WITH_WORDS)))+1,1) & " "&INDEX(WITH_WORDS,(MOD(ROW()-1,ROWS(WITH_WORDS))),1))

Another strange thing is if I split the Column A & B up into separate sheets (Sheet A & B) and I put the formula in Sheet C the formula works fine for repeating values through the B values but it will not go to the next value in A. It just keeps repeating the A value but doesn't concatenate the values on the end.

University of GA 1 sided flag
University of GA 2 sided flags
University of GA applique flag
University of GA banner
University of GA banners
University of GA collage flag
University of GA
University of GA
University of GA

Any help would be great. Thanks

Originally Posted by Andy Pope

Ah, so your column 'What it should look like' was not really what you wanted it to look like.

Revise the named ranges

FIRST_WORDS: =Sheet1!$A$2:$A$4
WITH_WORDS: =Sheet1!$B$2:$B$4


C2: =INDEX(FIRST_WORDS,INT((ROW()-2)/ROWS(WITH_WORDS))+1,1) & " "&INDEX(WITH_WORDS,MOD(ROW()-2,ROWS(WITH_WORDS))+1,1)

Without seeing your workbook it's hard to say what you have done when moving data and formula around.

Andy thanks for all your help.
I don't really understand the formula so maybe the problem is that I selected the column as the range. I attached the file for you to have a look at. Thanks a lot.