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?

 

[ajetrumpet]

based on your description, you are not trying to concatenate only horizontally, which is what would be easier for you. that is evidenced when you said:

For example: C2: Shoes
and Cell B2: Lacoste

Output : D2: Reebok Shoes

how in the world can you get Reebok Shoes in cell D2 when it does not appear in either B2 or C2??? I really think you need to reorganize your data so you can do a simple concat function like this:

For example: C2: Shoes
and Cell B2: Lacoste

desired result => D2: Locoste Shoes (function literal syntax inside D2 => [=B2 & " " & C2]

 

[vishesh10667]

Brand	Apparel	Combined
Reebok	T-shirt	Reebok T-shirt
Lacoste	Shoes	Reebok Shoes
Puma	Hoody	Reebok Hoody
Nike		Lacoste T-shirt
Addidas		Lacoste Shoes
		Lacoste Hoody
		Puma T-shirt
		Puma Shoes
		Puma Hoody
		Nike T-shirt
		Nike Shoes
		Nike Hoody
		Addidas T-shirt
		Addidas Shoes
		Addidas Hoody

 

[vishesh10667]

Brand	Apparel	Combined
Reebok	T-shirt	Reebok T-shirt
Lacoste	Shoes	Reebok Shoes
Puma	Hoody	Reebok Hoody
Nike		Lacoste T-shirt
Addidas		Lacoste Shoes
		Lacoste Hoody
		Puma T-shirt
		Puma Shoes
		Puma Hoody
		Nike T-shirt
		Nike Shoes
		Nike Hoody
		Addidas T-shirt
		Addidas Shoes
		Addidas Hoody

I need it like this

 

[ajetrumpet]

you really need to restructure your data. but to solve your current problem, try running this behind the sheet where your data is located:

Function unusual_concat()

Dim r As Range
Dim counter As Long
Dim nextValueRef As String
Dim startRowBrand As Long
Dim startColBrand As String
Dim startRowApparel As Long
Dim startColApparel As String
Dim startRowCombined As Long
Dim startColCombined As String

startRowBrand = 2
startColBrand = "A"
startRowApparel = 2
startColApparel = "B"
startRowCombined = 2
startColCombined = "C"

    With ActiveSheet
        For Each r In .Range("a2", Range("a2").End(xlDown))
            For counter = 1 To 3
                .Range(startColCombined & startRowCombined) = .Range(startColBrand & startRowBrand) & " " & .Range(startColApparel & startRowApparel)
                startRowApparel = startRowApparel + 1
                startRowCombined = startRowCombined + 1
            Next counter
                startRowBrand = startRowBrand + 1
                startRowApparel = 2
        Next r
    End With

End Function

you might also find a solution by researching excel's built in functions. I'm a code writer, not a function user, so this is how I would solve it. This assumes that your columns *brand*, *apparel* and *combined* are in columns *a*, *b* and *c* respectively. furthermore, if you would've looked up other threads here regarding the same kind of request, it's possible you would've come across this thread and this solution, from which this code taken from.

 

[Fluff]

Another option

Sub vishesh()
   Dim Ary1 As Variant, Ary2 As Variant, Nary As Variant
   Dim r As Long, rr As Long, nr As Long
   
   Ary1 = Range("A2", Range("A" & Rows.Count).End(xlUp)).Value2
   Ary2 = Range("B2", Range("B" & Rows.Count).End(xlUp)).Value2
   ReDim Nary(1 To UBound(Ary1) * UBound(Ary2), 1 To 1)
   For r = 1 To UBound(Ary1)
      For rr = 1 To UBound(Ary2)
         nr = nr + 1
         Nary(nr, 1) = Ary1(r, 1) & " " & Ary2(rr, 1)
      Next rr
   Next r
   Range("C2").Resize(nr).Value = Nary
End Sub

 

[ajetrumpet]

Fluff,

I assume since you are an Excel MVP, you are very well versed at writing efficient code? That's my immediate impression of you. You obviously know what you're doing, and if you and I show up in any subsequent threads together, more than likely your solution will be better than mine if we both answer the OP.

 

[Fluff]

IMO there is no such thing as "better" code , just options.
If all options work & the OP wants to be able to modify the code if needed, then the "best" code for them would probably be the one they understand the most.

 

[ajetrumpet]

IMO there is no such thing as "better" code , just options.
If all options work & the OP wants to be able to modify the code if needed, then the "best" code for them would probably be the one they understand the most.

agreed. I meant no dig by my comments. we each can do different things, I would assume. =)

 

[Fluff]

I meant no dig by my comments

None taken