Pivot/Transpose from Rows of multiple duplicates with columns to single rows

Bushranger

New Member
Joined
Mar 12, 2019
Messages
5
I have two issues I'm trying to solve

1: I have this:
DougBefore7
DougDuring4
DougAfter9
JimBefore3
JimDuring7
JimAfter4
KarenBefore3
KarenAfter6

<tbody>
</tbody>

And need it to look like this
NameBeforeDuringAfter
Doug749
Jim374
Karen36

<tbody>
</tbody>

2. I have this
SallyYellow
SallyBlue
SallyGreen
JennyRed
JennyGreen
DoryGreen
DoryBlue

<tbody>
</tbody>
and need it to look like this
SallyYellowBlueGreen
JennyRedGreen
DoryGreenBlue

<tbody>
</tbody>

Any help appreciated. I don't know much about VBA, Pivot etc but can follow instructions very well.
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
6,750
using PowerQuery (Get&Transform)

Namesth1Sth2NameBeforeDuringAfter
DougBefore
7​
Doug
7​
4​
9​
DougDuring
4​
Jim
3​
7​
4​
DougAfter
9​
Karen
3​
6​
JimBefore
3​
JimDuring
7​
JimAfter
4​
KarenBefore
3​
KarenAfter
6​
NameColorNameColor.1Color.2Color.3
SallyYellowSallyYellowBlueGreen
SallyBlueJennyRedGreen
SallyGreenDoryGreenBlue
JennyRed
JennyGreen
DoryGreen
DoryBlue

first case:
Code:
[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table12"]}[Content],
    #"Pivoted Column" = Table.Pivot(Source, List.Distinct(Source[sth1]), "sth1", "Sth2", List.Sum)
in
    #"Pivoted Column"[/SIZE]
second case:
Code:
[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table14"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Name"}, {{"Count", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Color", each Table.Column([Count],"Color")),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Color", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Color", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Color.1", "Color.2", "Color.3"})
in
    #"Split Column by Delimiter"[/SIZE]
 

Bushranger

New Member
Joined
Mar 12, 2019
Messages
5
using PowerQuery (Get&Transform)

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Name[/COLOR][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]sth1[/COLOR][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Sth2[/COLOR][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Name[/COLOR][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Before[/COLOR][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]During[/COLOR][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]After[/COLOR]
DougBefore
7​
Doug
7​
4​
9​
DougDuring
4​
Jim
3​
7​
4​
DougAfter
9​
Karen
3​
6​
JimBefore
3​
JimDuring
7​
JimAfter
4​
KarenBefore
3​
KarenAfter
6​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Name[/COLOR][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Color[/COLOR][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Name[/COLOR][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Color.1[/COLOR][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Color.2[/COLOR][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Color.3[/COLOR]
SallyYellowSallyYellowBlueGreen
SallyBlueJennyRedGreen
SallyGreenDoryGreenBlue
JennyRed
JennyGreen
DoryGreen
DoryBlue

<tbody>
</tbody>


first case:
Code:
[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table12"]}[Content],
    #"Pivoted Column" = Table.Pivot(Source, List.Distinct(Source[sth1]), "sth1", "Sth2", List.Sum)
in
    #"Pivoted Column"[/SIZE]
second case:
Code:
[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table14"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Name"}, {{"Count", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Color", each Table.Column([Count],"Color")),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Color", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Color", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Color.1", "Color.2", "Color.3"})
in
    #"Split Column by Delimiter"[/SIZE]

That is exactly what Iwant. This code you have listed, do I need to enter that somewhere? Like into a VBA table or something?
 

Bushranger

New Member
Joined
Mar 12, 2019
Messages
5
I've tried using this. The first error I get is can't find "Table14". . . . what is Table14 in your example, do I have to define somewhere?
Is there a guide somewhere that shows what all the names inside brackets mean?
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
6,750

ADVERTISEMENT

select your range then use Alt+A+PT, copy code into PowerQuery Advanced Editor (replace with all what you see) then change the name of table in code suitably

for excel 2010/2013 you'll need PowerQuery add-in (free from MS site), for excel 2016 and higher - PowerQuery is built-in aka Get&Transform
 
Last edited:

Bushranger

New Member
Joined
Mar 12, 2019
Messages
5
select your range then use Alt+A+PT, copy code into PowerQuery Advanced Editor (replace with all what you see) then change the name of table in code suitably

for excel 2010/2013 you'll need PowerQuery add-in (free from MS site), for excel 2016 and higher - PowerQuery is built-in aka Get&Transform

I've got Excel 2016, but I'm obviously a couple of years behind your knowledge! :)

So I've got my data sitting in two columns.
I highlighted all the data, went to Data > Get Data
it appears it wants to get it from another workbook?

So I closed the workbook, and opened a new one, went to Data > Get Data and linked it to the closed workbook with my data. This gets the data into the new workbook with 2 columns and I can then open power query editor, but I can't work out the coding to put into the advanced coding. I don't know what Alt+A+PT even is. I assume Alt+A is holding the Alt key and A key together, but what is PT?

Do you know of an online guide as to how to get started with this? Like a step by step. You instructions obviously work, but I think they start at a point well beyond what I can get to. I appreciate your efforts, don't get me wrong, I just don't know where to start.
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
6,750

ADVERTISEMENT

select your "table" then : Data - From Table

edit:
press Alt then hit A and then hit P and T (this is just "From Table")
 
Last edited:

Bushranger

New Member
Joined
Mar 12, 2019
Messages
5

Absolutely Brilliant.

I haven't quite figured it out how to do it myself, however, I copied my actual data into your sample and even though I had 2900 rows of data, it picked it up and put it into the 650 unique rows I required with the 10 columns I required next to it. I edited to add in the additional beyond Colour.3 etc....

Amazing. I am so grateful. You just saved my 6 hours work!
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
6,750
6 hours? so little?


you are welcome
have a nice day :)
 

Watch MrExcel Video

Forum statistics

Threads
1,108,969
Messages
5,525,963
Members
409,673
Latest member
Riseee

This Week's Hot Topics

Top