Transpose the column values into row base on a certain values in a column

Hi all,

I have a table that looks like the below "Input" and i want to transpose it into a structure like the "Output"

I need to take the unique values in the column "ID". And print it as once even how many time it appears the column till "country" should print once and the values in the "Rate" column should be print in rows until in available for the same "ID". when the value in the "ID" column changes, it the respected values for the next "ID" should start a print in the next row.


Please see the image for reference.

Thanks in advanceexcel.PNG

Undeclared cross post added by GK:

https://www.mrexcel.com/board/thread...olumn.1163760/

Welcome (back) to the forum.

Thanks for your question. However, we have just found out that you have posted the same question elsewhere.... and haven't told us. Feel free to cross-post on other sites...

But. Every forum has its rules. Please see Forum Rule #3 about cross-posting. So.

Do not keep it a secret. We all are willing to give you our time, freely. However, some of us get very annoyed if we waste our time developing a solution for you, when you already have a nice solution elsewhere. All we ask is that you show us some respect and tell us:

1) if you have cross-posted, and

2) the URL of the cross posts.

That way, those of us who don't want to waste their time can quickly check to see if you're already happy with another solution.

(Note: this requirement is not optional. No help to be offered until the link is provided.)

Has this been solved on Mr Excel??

Here is an alternative solution to those you received over at MrE. I used Power Query and transformed the Data using a Pivot Table within PQ

Here is the Mcode and the file is attached

Please Login or Register  to view this content.

Excel 2016 (Windows) 32 bit

	A	B	C	D	E	F	G
1	ID	City	Country	1	2	3	4
2	1	Rome	Italy	1255	215	25486
3	2	Paris	France	6558	8142
4	3	Frankfurt	Germany	9851	31546	51568	1548
5	4	Milan	Italy	5994	9666

Sheet: Table1

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.