Horizontal to Vertical - Transpose

[soumen21]

Hi Experts,

I have few data like this

Catalogue

Product Line

Description

Marking1

Marking2

Marking3

Marking4

Marking5

Marking6

Marking7

Marking8

Marking9

Marking10

Marking11

Marking12

Marking13

Marking14

Marking15

Catalogue1

P1

Description 1

2014CE

CE Mark

China RoHS2

CUTR-EMC

EAC

EU-ATEX

EU-EMC

FCC-A(verified)

UL Listed

Catalogue2

P2

Description 2

2014CE

ABS

CE Mark

China RoHS2

CUTR-EMC

DNV

EAC

EU-ATEX

EU-EMC

FCC-A(verified)

UL Listed

I want to sort it like below.

Catalogue	Product Line	Description	Marking
Catalogue1	P1	Description 1	2014CE
Catalogue1	P1	Description 1	CE Mark
Catalogue1	P1	Description 1	China RoHS2
Catalogue1	P1	Description 1	CUTR-EMC
Catalogue1	P1	Description 1	EAC
Catalogue1	P1	Description 1	EU-ATEX
Catalogue1	P1	Description 1	EU-EMC
Catalogue1	P1	Description 1	FCC-A(verified)
Catalogue1	P1	Description 1	UL Listed
Catalogue2	P2	Description 2	2014CE
Catalogue2	P2	Description 2	ABS
Catalogue2	P2	Description 2	CE Mark
Catalogue2	P2	Description 2	China RoHS2
Catalogue2	P2	Description 2	CUTR-EMC
Catalogue2	P2	Description 2	DNV
Catalogue2	P2	Description 2	EAC
Catalogue2	P2	Description 2	EU-ATEX
Catalogue2	P2	Description 2	EU-EMC
Catalogue2	P2	Description 2	FCC-A(verified)
Catalogue2	P2	Description 2	UL Listed

Could you please guide how to do?

Best Regards

 

[cwstudio2020]

If there isn’t much data, using the transpose method to process it row by row would be more efficient. If there are many rows, you might consider using VBA to process the data. Alternatively, you can use SQL. If you'd like to use SQL, I’d be happy to help you write one (for free).

 

[soumen21]

My data is huge. Doing it manually will not be good idea

 

[alansidman]

Unpivot the data using Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Catalogue", "Product Line", "Description"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Value", "Marking"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Attribute"})
in
    #"Removed Columns"

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.

The Complete Guide to Installing Power Query - Excel Campus

Learn what version you need to get Power Query and instructions on how to install this free add-in from Microsoft.

www.excelcampus.com

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

Power Query Overview: An Introduction to Excel's Most Powerful Data Tool - Excel Campus

Learn how this FREE Excel add-in from Microsoft called Power Query will help transform your data into a format that is ready for pivot tables, reports and more.

www.excelcampus.com

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

Power Query - How To Paste Code (video) - Excel Solutions

Power Query is great at solving data transformation and consolidation issues. But there are still loads of Excel users who have never used Power Query yet – and sometimes when they’re given some Power Query code to answer their question, they don’t know what to do with it. Here’s a short video...

excel.solutions

 

[cwstudio2020]

I used a tool to create a small program to accomplish this task.
The steps are as follows:

1. Create a table (t_pp) in the database with column names that match those in your Excel sheet.
2. Then, use the tool to import the Excel data into the t_pp table.
3. Run an SQL query to insert each record's Catalogue, Product Line, Description, and Marking1 through Marking15 into another table, t_pp_2.
4. Finally, retrieve the processed results from the t_pp_2 table.

If needed, I can send you the program I created. You’ll just need to click a button in the interface to import the table into the database and then export the resulting table.

The final output looks like this:

 

[soumen21]

Guys, I used power query and got the solution.