Formula to transpose information

[stephenclarke111111111]

Hi,

I'm new to this and might not explain this the best,(see example below)

Column a has duplicated information multiple times, i need to display column b horizontally see column c , this is the result i'm after.

Please ask for anymore information, I look forward to seeing if this can be done.

Thanks

Stephen.



a b c

[TABLE="width: 554"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Part Number[/TD]
[TD]Model[/TD]
[TD]Result[/TD]
[/TR]
[TR]
[TD]1000[/TD]
[TD]3 Series[/TD]
[TD]3 Series","5 Series","Z1",6 Series[/TD]
[/TR]
[TR]
[TD]1000[/TD]
[TD]5 Series[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]1000[/TD]
[TD]Z1[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]1000[/TD]
[TD]6 Series[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Courier[/TD]
[TD]"Courier","Fiesta"[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Fiesta[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

 

[Joe4]

Welcome to the Board!

Are you open to a VBA solution? There may be a formula solution, but that is beyond me. I would probably use VBA.
Do you really want each result enclosed in double-quotes like shown in the Result column?
Is the data always sorted by column A, so that the values that need to be combined are always listed together?

 

[stephenclarke111111111]

Welcome to the Board!

Are you open to a VBA solution? There may be a formula solution, but that is beyond me. I would probably use VBA.
Do you really want each result enclosed in double-quotes like shown in the Result column?
Is the data always sorted by column A, so that the values that need to be combined are always listed together?

Hi,

Thank you.

The quotes don't matter. I just need all the information from column B that applied to column A on one line.

I'm open to VBA

Regards

 

[PichaiTC]

For Excel 2016 by TEXTJOIN function

https://www.facebook.com/ExcelNaNa/posts/1684473291584616

 

[PichaiTC]

For older Excel versions, without TEXTJOIN function.
You can add helper column and use VLOOKUP function.

https://www.facebook.com/ExcelNaNa/posts/1684473291584616

 

[stephenclarke111111111]

Hi,


Thank you for the posts, I have 1 million rows to apply this to, is there a way to do this with one formula ?

Thanks

 

[Fluff]

One VBA solution would be

Sub ConcatTranspose()

   Dim Cl As Range
   
Application.ScreenUpdating = False

   With CreateObject("scripting.dictionary")
      For Each Cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
         If Not .exists(Cl.Value) Then
            Cl.Offset(, 2).Value = Cl.Offset(, 1).Value
            .Add Cl.Value, Cl.Offset(, 2)
         Else
            .Item(Cl.Value).Value = .Item(Cl.Value).Value & ", " & Cl.Offset(, 1).Value
         End If
      Next Cl
   End With

End Sub

 

[Mike Szczesny]

This non-VBA option seems to work. This is a two step process for one formula. I will use the data sample in post #4 as an example. Assume A1:A9 is Item range (including title). Assume B1:B9 is the Tag range (including Tag). Assume Book and Pen criteria are in D2:D3. The answer for book will be in E2. First, create this formula =concatenate(transpose(if(a2:a9=d2,b2:b9&" , "))). Hit F9. Highlight the transpose(.....)), leaving the final ) alone. Remove both { and }. You will see "FALSE" answer in your result. Add on on a =substitute formula. The final formula should be =substitute(your formula,"FALSE",""). This should remove the False answers.

[TABLE="width: 357"]
<colgroup><col span="2"><col span="2"><col></colgroup><tbody>[TR]
[TD]Item[/TD]
[TD]Tag[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Book[/TD]
[TD]a[/TD]
[TD][/TD]
[TD]Book[/TD]
[TD]a ,b ,c ,d ,e ,[/TD]
[/TR]
[TR]
[TD]Book[/TD]
[TD]b[/TD]
[TD][/TD]
[TD]Pen[/TD]
[TD]f , g , h , [/TD]
[/TR]
[TR]
[TD]Book[/TD]
[TD]c[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Book[/TD]
[TD]d[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Book[/TD]
[TD]e[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Pen[/TD]
[TD]f[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Pen[/TD]
[TD]g[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Pen[/TD]
[TD]h[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]