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

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Unique code[/td][td=bgcolor:#5B9BD5]email[/td][td][/td][td=bgcolor:#70AD47]Unique code[/td][td=bgcolor:#70AD47]email.1[/td][td=bgcolor:#70AD47]email.2[/td][td=bgcolor:#70AD47]email.3[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]10509-10[/td][td=bgcolor:#DDEBF7]email@domain.com 1[/td][td][/td][td=bgcolor:#E2EFDA]10509-10[/td][td=bgcolor:#E2EFDA]email@domain.com 1[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]10509-26[/td][td]email@domain.com 2[/td][td][/td][td]10509-26[/td][td]email@domain.com 2[/td][td]email@domain.com 3[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]10509-26[/td][td=bgcolor:#DDEBF7]email@domain.com 3[/td][td][/td][td=bgcolor:#E2EFDA]10509-27[/td][td=bgcolor:#E2EFDA]email@domain.com 4[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]10509-27[/td][td]email@domain.com 4[/td][td][/td][td]10537-08[/td][td]email@domain.com 5[/td][td]email@domain.com 6[/td][td]email@domain.com 7[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]10537-08[/td][td=bgcolor:#DDEBF7]email@domain.com 5[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]10537-08[/td][td]email@domain.com 6[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]10537-08[/td][td=bgcolor:#DDEBF7]email@domain.com 7[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

[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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]14[/TD]
[TD="align: center"]Unique Code[/TD]
[TD="align: center"]Email1[/TD]
[TD="align: center"]Email2[/TD]
[TD="align: center"]Email3[/TD]
[TD="align: center"]Email4[/TD]

[TD="align: center"]15[/TD]
[TD="align: center"]10509-10[/TD]
[TD="align: center"]email@domain.com1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]16[/TD]
[TD="align: center"]10509-26[/TD]
[TD="align: center"]email@domain.com2[/TD]
[TD="align: center"]email@domain.com3[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]17[/TD]
[TD="align: center"]10509-27[/TD]
[TD="align: center"]email@domain.com4[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]18[/TD]
[TD="align: center"]10537-08[/TD]
[TD="align: center"]email@domain.com5[/TD]
[TD="align: center"]email@domain.com6[/TD]
[TD="align: center"]email@domain.com7[/TD]
[TD="align: center"][/TD]

</tbody>

Sheet4​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]B15[/TH]
[TD="align: left"]{=IFERROR(INDEX($B$2:$B$8,SMALL(IF($A15=$A$2:$A$8,ROW($A$2:$A$8)- (ROW($A$2))+1,""),COLUMN()-1)), "")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]