Concatenate

[vishesh10667]

Hi, I have a list of items in Column B and each items in B should concatenate with the list in column C.

For example: Cell B1: Reebok, and Cell C1: T-Shirt, C2: Shoes, Etc.,
and Cell B2: Lacoste, and Cell C1: T-shirt, C2: Shoes, Etc.

Output : D1: Reebok T-shirt, D2: Reebok Shoes, D3: Lacoste T-shirt, D4: Lacoste Shoes etc.

Is there any formula to concatenate like this?

 

[FormR]

I need it like this

Hi, here is a formula option you could also try.

Book1
	A	B	C
1	Brand	Apparel	Combined
2	Reebok	T-shirt	Reebok T-shirt
3	Lacoste	Shoes	Reebok Shoes
4	Puma	Hoody	Reebok Hoody
5	Nike		Lacoste T-shirt
6	Addidas		Lacoste Shoes
7			Lacoste Hoody
8			Puma T-shirt
9			Puma Shoes
10			Puma Hoody
11			Nike T-shirt
12			Nike Shoes
13			Nike Hoody
14			Addidas T-shirt
15			Addidas Shoes
16			Addidas Hoody
Sheet1

Cell Formulas
Range	Formula
C2:C16	C2	=INDEX($A$2:$A$6,ROUNDUP(ROWS(C$2:C2)/COUNTA($B$2:$B$4),0))&" "&INDEX($B$2:$B$4,MOD(ROWS(C$2:C2)-1,3)+1)

 

[footoo]

Here's a formula (a bit long - maybe someone can suggest a shorter one).
Put in D1 and drag down:

=INDIRECT("B" & (INT((ROW()-1)/COUNTA(C:C))+1)) & INDIRECT("C" & MOD(ROW(),COUNTA(C:C))+(MOD(ROW(),COUNTA(C:C))=0)*COUNTA(C:C))