excel - Large table to be transformed by moving 200+ columns to rows

Question

Given a relative massive table I have recieved which is (currently) in excel. 25k rows and 225 columns. First column is "event_Id", 2nd "organizer" 3rd-225 "participants".

I would like to transfer it to 3 columns from the 225, where event_Id and organizer can repeat, but the participant columns I would like to pivot into one column, though I have no idea how. Pulling it column by column to the pivot table columns does not feel to effective, especially if I need to repeat the process somehow.

current:

id1 organizer1 partic1 partic2 partic3 partic4
id2 organizer4 partic5 partic2 partic6 

desired:

id1 organizer1 partic1
id1 organizer1 partic2
id1 organizer1 partic3
id1 organizer1 partic4
id2 organizer4 partic5
id2 organizer4 partic2
id2 organizer4 partic6 

any idea is welcome- dont need to stick to excell- can go to sql, tableau...

标签： excelpivot

Answer 1

反透视 Excel 表

您要执行的操作称为UnPivot，大约需要十几次点击。

有关详细步骤和更多信息，请参阅我现有的答案here和here以及here以及更多信息here。