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.
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,497
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

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,497

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

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,497

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

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,497
6 hours? so little?
rotfl2.gif


you are welcome
have a nice day :)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,017
Messages
5,834,960
Members
430,330
Latest member
drAli77

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top