Column to row

[shadymedhat]

Hello fellows,

I have a table of 2 columns “unique code” and “email” ,so the case is that one code could have many emails, so i want show all emails for specific code at one row..


Thank U

 

[sandy666]

using Power Query

Unique code	email		Unique code	email.1	email.2	email.3
10509-10	email@domain.com 1		10509-10	email@domain.com 1
10509-26	email@domain.com 2		10509-26	email@domain.com 2	email@domain.com 3
10509-26	email@domain.com 3		10509-27	email@domain.com 4
10509-27	email@domain.com 4		10537-08	email@domain.com 5	email@domain.com 6	email@domain.com 7
10537-08	email@domain.com 5
10537-08	email@domain.com 6
10537-08	email@domain.com 7

[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"Unique code"}, {{"Count", each _, type table}}),
    List = Table.AddColumn(Group, "email", each List.Distinct(Table.Column([Count],"email"))),
    Extract = Table.TransformColumns(List, {"email", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    SplitCount = Table.AddColumn(Extract, "Split Count", each List.Count(Text.Split([email],","))),
    MaxCount = List.Max(SplitCount[Split Count]),
    Split = Table.SplitColumn(Extract, "email", Splitter.SplitTextByDelimiter(","), MaxCount)
in
    Split[/SIZE]

btw. share excel file not a picture, no one want to re-type data from the picture

 

[shadymedhat]

Noted, i m just new to that forum

 

[shadymedhat]

can I now what are the steps you applied into power query, as I'm new to that platform

 

[sandy666]

all steps are in post#11

also see here: Example Excel file

you should read

• What is PowerQuery
• Getting started with PQ
• PowerQuery Help
• PowerQuery M reference

 

[mohadin]

Hi
Try this come in a normal module

Sub test()
    Dim a As Variant, i, itm, x
    a = Range("a2:a" & Cells(Rows.Count, 1).End(xlUp).Row).Resize(, 2)
    With CreateObject("scripting.dictionary")
        For i = 1 To UBound(a)
            If a(i, 1) <> 0 Then
                If Not .exists(a(i, 1)) Then
                    .Add a(i, 1), a(i, 2)
                Else
                    .Item(a(i, 1)) = .Item(a(i, 1)) & "," & a(i, 2)
                End If
            End If
        Next
        itm = .Items
        Cells(1, 1).Resize(, 2).Copy Cells(1, 1).Offset(, 3).Resize(, 2)
        Cells(1, 1).Offset(1, 3).Resize(.Count) = Application.Transpose(.Keys)
        For i = 0 To .Count - 1
            x = Split(itm(i), ",")
            If UBound(x) < 1 Then
                Cells(i + 1, 1).Offset(1, 4) = itm(i)
            Else
                Cells(i + 1, 1).Offset(1, 4).Resize(, UBound(x) + 1) = x
            End If
        Next
    End With
End Sub

 

[Sam_D_Ben]

Also please see this function. thanks.

Excel 2013/2016

	A	B	C	D	E
14	Unique Code	Email1	Email2	Email3	Email4
15	10509-10	email@domain.com1
16	10509-26	email@domain.com2	email@domain.com3
17	10509-27	email@domain.com4
18	10537-08	email@domain.com5	email@domain.com6	email@domain.com7

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet4​

Array Formulas Cell Formula B15 {=IFERROR(INDEX($B$2:$B$8,SMALL(IF($A15=$A$2:$A$8,ROW($A$2:$A$8)- (ROW($A$2))+1,""),COLUMN()-1)), "")} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>