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

[Rick Vu]

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

[MarvinP]

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.

[Rick Vu]

Much appreciated Marvin, IT WORKS, Thank you very much

[Rick Vu]

Thank you Marvin, IT WORKS, much appreciated

[sandy666]

Power Query

another way

	Result
School	Subject	Tested By	Date	Score
Center Twp.	Math	John Doe	20220620	5
Center Twp.	Math	John Doe	20220620	4
Center Twp.	Math	John Doe	20220620	3
Center Twp.	Math	John Doe	20220620	1
Center Twp.	Math	John Doe	20220620	1
Center Twp.	Math	John Doe	20220620	4
Hopewell	Science	Mary Jane	20220701	5
Hopewell	Science	Mary Jane	20220701	5
Hopewell	Science	Mary Jane	20220701	2
Hopewell	Science	Mary Jane	20220701	4
Hopewell	Science	Mary Jane	20220701	3
Hopewell	Science	Mary Jane	20220701	4
Hopewell	Science	Mary Jane	20220701	1
Hopewell	Science	Mary Jane	20220701	2

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Merge = Table.CombineColumns(Table.TransformColumnTypes(Source, {{"Student 1", type text}, {"Student 2", type text}, {"Student 3", type text}, {"Student 4", type text}, {"Student 5", type text}, {"Student 6", type text}, {"Student 7", type text}, {"Student 8", type text}, {"Student 9", type text}, {"Student 10", type text}}, "en-GB"),{"Student 1", "Student 2", "Student 3", "Student 4", "Student 5", "Student 6", "Student 7", "Student 8", "Student 9", "Student 10"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Score"),
    Rep = Table.ReplaceValue(Merge," ","",Replacer.ReplaceText,{"Score"}),
    Split = Table.ExpandListColumn(Table.TransformColumns(Rep, {{"Score", Splitter.SplitTextByRepeatedLengths(1), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Score"),
    Type = Table.TransformColumnTypes(Split,{{"Score", Int64.Type}})
in
    Type

[Rick Vu]

Thank you Sandy,

[Glenn Kennedy]

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.

[Rick Vu]

Thanks Glen

[Czeslaw]

Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"School", type text}, {"Subject", type text}, {"Tested By", type text}, {"Date", Int64.Type}, {"Student 1", Int64.Type}, {"Student 2", Int64.Type}, {"Student 3", Int64.Type}, {"Student 4", Int64.Type}, {"Student 5", Int64.Type}, {"Student 6", Int64.Type}, {"Student 7", Int64.Type}, {"Student 8", Int64.Type}, {"Student 9", Int64.Type}, {"Student 10", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"School", "Subject", "Tested By", "Date"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Value", "Student Score"}})
in
    #"Renamed Columns"

[JohnTopley]

Yet another ... VBA

Sub X_10()
Dim ar, arr
ar = Sheets("Sheet1").[a1].CurrentRegion
ReDim arr(1 To 5, 1 To 1)
Application.ScreenUpdating = False
n = 0
For r = 2 To UBound(ar, 1)
    For c = 5 To 14
        If IsNumeric(ar(r, c)) Then
            n = n + 1
            ReDim Preserve arr(1 To 5, 1 To n)
            arr(5, n) = ar(r, c)
            For cc = 1 To 4
                arr(cc, n) = ar(r, cc)
            Next cc
        End If
    Next c
Next r

With Sheets("Sheet2")
.[A2].Resize(n, 5) = Application.Transpose(arr)
End With

Application.ScreenUpdating = True
End Sub

[Rick Vu]

Thanks John

[Rick Vu]

Thanks Czeslaw

[HansDouwe]

I've an other solution with formula:

Please try for the student score

=FILTERXML("<z><m>"&TEXTJOIN("</m><m>",,TRIM(E2:N3))&"</m></z>","//m")

Please create for the other information an helper column =COUNT(E$2:N2) and try formula

=IFERROR(INDEX(A$2:D$3,IFNA(MATCH(ROW(A1)-1,O$2:O$3,1)+1,1),0),"")

[Glenn Kennedy]

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.