Arrange data horizontally pivot

[Caly]

I have two columns of data where column A has items but then column B has multiple ways the item in column A is referenced. How can I arrange the data that is now in different rows to show horizontally so I can have it in a table?

Column A. Column B
12345. Apple
12345. Pear
5678. Red
5678. Blue


So I want to transform this data to show like this:
Column A. column B. column C.
12345. Apple. Pear
5678. Red. Blue

 

[alansidman]

If you are willing to try Power Query, then here is a solution for you.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Column1"}, {{"Data", each _, type table [Column1=nullable number, Column2=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Data], "Index", 1, 1)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Column2", "Index"}, {"Custom.Column2", "Custom.Index"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Data"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Removed Columns", {{"Custom.Index", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Removed Columns", {{"Custom.Index", type text}}, "en-US")[Custom.Index]), "Custom.Index", "Custom.Column2")
in
    #"Pivoted Column"

Book2
	A	B	C	D	E	F
1	Column1	Column2		Column1	1	2
2	12345	Apple		5678	Red	Blue
3	12345	Pear		12345	Apple	Pear
4	5678	Red
5	5678	Blue
Sheet2

 

[Caly]

Thank you. I’m not yet familiar with power query sadly. Is there a way maybe in a pivot table to do this? Or another approach

 

[alansidman]

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

 

[Caly]

Hi thank you so much. I wanted to attach a file which may be easier to see what I’m trying to have as the end resulting view.

 

[Caly]

Hi thank you so much. I wanted to attach a file which may be easier to see what I’m trying to have as the end resulting view.

View attachment 38377

 

[Caly]

This is the starting point

so In column A there is information for an item that will have the content in columns B through G but the data is unique for the lines so where the data repeats, I am trying to have it all featured on one single line going across

is Power Query the way to go on this
Thank you

 

[alansidman]

Please use XL2BB to repost your data so that we don't have to try and recreate your spreadsheet. Also, show your expected results mocked up so that we clearly understand what you want.

 

[Caly]

Thank you so much. Which button do I select from here to upload the file?

 

[alansidman]

start here-->XL2BB - Excel Range to BBCode