From 1 row in Excel, I need to create up to 10 rows depending on a Students Score

Hi,

Can someone tell me how to do the following. I have a spreadsheet that has over 1000 rows. I want to convert each row into as many rows as how many students posted a score in each of the rows. (up to 10 scores per row) Please see my attachment for a good example of what I am looking to do. My example only shows 2 rows but my actual table contains over 1000 rows.

Thank you for any help

Hi Rick,

Because you have the latest version of Excel you have Power Query. In Power Query there is an "UnPivot" function that makes your problem a snap.

Here are the steps:
Select cell A1 on your worksheet.
Select the Data Tab and click on "Get Data dropdown
Select "From Other Sources" and then "From Table/Range"
The Power Query window will appear....
Select the first 4 columns using Ctrl Click
Right click on any of the selected columns and then "UnPivot Other Columns"
Click on the Home tab in the PQ editor and Close and Load.

Problem done!!!

You could delete the "Attribute" column either before or after the above to make it look exactly like you show in your example.

Hope this helps.

Or a formula (made messier because some blank cells contain spaces and others do not):

=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($A$2:$A$3)/(TRIM($E$2:$N$3)<>""),ROWS(A$12:A12)),1),"")
copied across & down... and

=IFERROR(INDEX(INDEX($2:$3,MATCH(A12,$A$2:$A$3,0),),AGGREGATE(15,6,COLUMN($E$2:$N$2)/(TRIM(INDEX($E$2:$N$3,MATCH(A12,$A$2:$A$3,0),))<>""),COUNTIF(A$12:A12,A12))),"")
copied down.

see file.

Yet another ... VBA

Please Login or Register  to view this content.

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please click on "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.