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

P

pratheesh

New Member
Joined
Dec 16, 2020
Messages
12
Office Version
  1. 365
  2. 2011
  3. 2010
Platform
  1. Windows
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 advance
 

Attachments

  • excel.PNG
    excel.PNG
    19.1 KB · Views: 11

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Sort by date Sort by votes
For the first column you can use the function UNIQUE
For the orther functions you can use VLOOKUP or INDEX and MATCH.
 
Upvote 0
pratheesh said:
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 advance
Click to expand...
Please find the below:

Book1
ABCDEFGHIJKL
1InputOutput
2
3
4
5IDCityCountryRateIDCityCountryRate1Rate2Rate3Rate4
61RomeItaly12551RomeItaly125521525486
71RomeItaly2152ParisFrance254866558
81RomeItaly254863FrankfurtGermany65588142985131546
92ParisFrance65584MilanItaly3154651568
102ParisFrance8142
113FrankfurtGermany9851
123FrankfurtGermany31546
133FrankfurtGermany51568
143FrankfurtGermany1548
154MilanItaly5994
164MilanItaly9666
Sheet1
Cell Formulas
RangeFormula
F6:G9F6=UNIQUE(A6:A16)
H6:H9H6=VLOOKUP(G6,$B$6:$C$16,2,0)
I6:K6,I9:J9,I8:L8,I7:J7I6=TRANSPOSE(FILTER($D$6:$D$16,(B6:B16=G6)))
Dynamic array formulas.


Another way of doing such easily, it to insert a pivot table

Regards
 
Upvote 0
Another option
+Fluff 1.xlsm
ABCDEFGHIJKL
1IDCityCountryRateIDCityCountryRate1Rate2Rate3Rate4
21RomeItaly12551RomeItaly125521525486
31RomeItaly2152ParisFrance254866558
41RomeItaly254863FrankfurtGermany65588142985131546
52ParisFrance65584MilanItaly3154651568
62ParisFrance8142
73FrankfurtGermany9851
83FrankfurtGermany31546
93FrankfurtGermany51568
103FrankfurtGermany1548
114MilanItaly5994
124MilanItaly9666
13
Data
Cell Formulas
RangeFormula
F2:H5F2=UNIQUE(A2:C12)
I2:K2,I5:J5,I4:L4,I3:J3I2=TRANSPOSE(FILTER($D$2:$D$12,(B2:B12=G2)))
Dynamic array formulas.
 
Upvote 0
mamady said:
Please find the below:

Book1
ABCDEFGHIJKL
1InputOutput
2
3
4
5IDCityCountryRateIDCityCountryRate1Rate2Rate3Rate4
61RomeItaly12551RomeItaly125521525486
71RomeItaly2152ParisFrance254866558
81RomeItaly254863FrankfurtGermany65588142985131546
92ParisFrance65584MilanItaly3154651568
102ParisFrance8142
113FrankfurtGermany9851
123FrankfurtGermany31546
133FrankfurtGermany51568
143FrankfurtGermany1548
154MilanItaly5994
164MilanItaly9666
Sheet1
Cell Formulas
RangeFormula
F6:G9F6=UNIQUE(A6:A16)
H6:H9H6=VLOOKUP(G6,$B$6:$C$16,2,0)
I6:K6,I9:J9,I8:L8,I7:J7I6=TRANSPOSE(FILTER($D$6:$D$16,(B6:B16=G6)))
Dynamic array formulas.


Another way of doing such easily, it to insert a pivot table

Regards
Click to expand...
Thanks a lot :) :)

mamady​

 
Upvote 0
Fluff said:
Another option
+Fluff 1.xlsm
ABCDEFGHIJKL
1IDCityCountryRateIDCityCountryRate1Rate2Rate3Rate4
21RomeItaly12551RomeItaly125521525486
31RomeItaly2152ParisFrance254866558
41RomeItaly254863FrankfurtGermany65588142985131546
52ParisFrance65584MilanItaly3154651568
62ParisFrance8142
73FrankfurtGermany9851
83FrankfurtGermany31546
93FrankfurtGermany51568
103FrankfurtGermany1548
114MilanItaly5994
124MilanItaly9666
13
Data
Cell Formulas
RangeFormula
F2:H5F2=UNIQUE(A2:C12)
I2:K2,I5:J5,I4:L4,I3:J3I2=TRANSPOSE(FILTER($D$2:$D$12,(B2:B12=G2)))
Dynamic array formulas.
Click to expand...
Thank you very much :) :)
 
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0
Thanks, we are glad to be able to assist.

Regards
 
Upvote 0
For future reference.

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Transpose the column values into row base on a certain values in a column
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
 
Upvote 0
mamady said:
Please find the below:

Book1
ABCDEFGHIJKL
1InputOutput
2
3
4
5IDCityCountryRateIDCityCountryRate1Rate2Rate3Rate4
61RomeItaly12551RomeItaly125521525486
71RomeItaly2152ParisFrance254866558
81RomeItaly254863FrankfurtGermany65588142985131546
92ParisFrance65584MilanItaly3154651568
102ParisFrance8142
113FrankfurtGermany9851
123FrankfurtGermany31546
133FrankfurtGermany51568
143FrankfurtGermany1548
154MilanItaly5994
164MilanItaly9666
Sheet1
Cell Formulas
RangeFormula
F6:G9F6=UNIQUE(A6:A16)
H6:H9H6=VLOOKUP(G6,$B$6:$C$16,2,0)
I6:K6,I9:J9,I8:L8,I7:J7I6=TRANSPOSE(FILTER($D$6:$D$16,(B6:B16=G6)))
Dynamic array formulas.


Another way of doing such easily, it to insert a pivot table

Regards
Click to expand...
HI Mamdy,

There is a minor mistake in your solution. if you look the output for the row "Paris" the values in the input are "6558" and "8142" but in the output . the first value for paris is "25486" this value belong to "Rome". and its the same case in all the other rows the first value is the value belong to the previous row. and also the very last value "9666" is missing in the output.

Please take a look and let me know is there any possibliltes to solve this issue.

thank you so much for your effort so far.. :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
Output text vba should run10000 rows batch wise on every run till it ends.
Replies
0
Views
127
Sathish G
S
P
  • Question
Excel VBA, once a cell value is changed, change other cell in same row with specific text
Replies
1
Views
93
Patty3-4
P
L
  • Question
Transposing data accross a fixed number of columns
2
Replies
17
Views
683
Fluff
Fluff
I
  • Question
Move multiple rows from one table to another using conditional trigger
Replies
2
Views
468
ItsNursinTime
I
P
  • Question
Code used for comparison between 2 list.
Replies
3
Views
315
Tupe77
T

Forum statistics

Threads
1,214,990
Messages
6,122,625
Members
449,093
Latest member
catterz66

Share this page

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
Back
Top