Splitting data

Tigerexcel

Active Member
Joined
Mar 6, 2020
Messages
493
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
I have a spreadsheet with student results that contains data such as Student A (in the first column), then the individual results Chemistry 60, Biology 55, Maths 70, English 80 all in one cell (in the next column), the next row down will contain Student B, Maths 60, Languages 50, Physics 90, next one down might contain Biology 75, English 45, Applied Maths 55, General Science 80 etc. The results for each student are in one cell only making it difficult to do any sort of analysis.
I would like to arrange the data so that instead of meaningless column headings up the top like Column A we have along the top Chemistry Maths English etc, then the scores in the relevant columns for each student.
I can use text to columns to split the data across the columns but the names of the subjects won't be indicated on the spreadsheet. There would be approx. 30 or so units that students could take. Typically no one does more than 4 units at a time. There would be approx 1000 students. There may well be a way to do this without VBA and am happy to do it in anyway that gets the job done.
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Does a comma always follow the score for each subject (except for the last)? And if so, are commas used in any of the subject names?
 
Upvote 0
One way would be to employ a user-defined function.
To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below (can use the icon at the top right of the code pane below) into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy across and down.

VBA Code:
Function SubjectScore(sScores As String, sSubject As String) As Variant
  Dim RX As Object
  
  Set RX = CreateObject("VBScript.RegExp")
  RX.IgnoreCase = True
  RX.Pattern = "(\, " & sSubject & " )(\d+)"
  SubjectScore = vbNullString
  If RX.Test(", " & sScores) Then SubjectScore = Val(RX.Execute(", " & sScores)(0).subMatches(1))
End Function

Tigerexcel 2020-03-23 1.xlsm
ABCDEFGHIJ
1StudentScoresApplied MathsBiologyChemistryEnglishGeneral ScienceLanguagesMathsPhysics
2Student AChemistry 60, Biology 55, Maths 70, English 80 556080  70 
3Student BMaths 60, Languages 50, Physics 90     506090
4Student CBiology 75, English 45, Applied Maths 55, General Science 805575 4580   
Sheet1
Cell Formulas
RangeFormula
C2:J4C2=SubjectScore($B2,C$1)
 
Upvote 0
Does a comma always follow the score for each subject (except for the last)? And if so, are commas used in any of the subject names?
Hi Kirk,

Yes commas are the delimiters. No commas are used in the subject names.
 
Upvote 0
Thanks for the response. I like Peter's solution...looks like it should work well.
 
Upvote 0
Thanks Peter, I'd be lying if I said that I understood that coding. Some of that coding doesn't even look like VBA :). I've certainly got a new appreciation for Functions.
Could we go one step further and amend the code so that Excel automatically generates the column headings ie the subject names? The subjects do tend to change from term to term, it would be a little painful to have to type them all in.
 
Upvote 0
This should do the whole job then, just given the scores in column B

VBA Code:
Sub Subjects_And_Scores()
  Dim d As Object
  Dim a As Variant, b As Variant, SubjScore As Variant
  Dim i As Long, pos As Long
  Dim Subj As String
  
  Set d = CreateObject("Scripting.Dictionary")
  d.CompareMode = 1
  a = Range("B2", Range("B" & Rows.Count).End(xlUp)).Value
  ReDim b(1 To UBound(a), 0 To 0)
  For i = 1 To UBound(a)
    For Each SubjScore In Split(a(i, 1), ", ")
      pos = InStrRev(SubjScore, " ")
      Subj = Left(SubjScore, pos - 1)
      If Not d.exists(Subj) Then d(Subj) = d.Count + 1
      If d.Count > UBound(b, 2) Then ReDim Preserve b(1 To UBound(b), 1 To d.Count)
      b(i, d(Subj)) = Val(Mid(SubjScore, pos + 1))
    Next SubjScore
  Next i
  Range("C1").Resize(, d.Count).Value = d.Keys
  With Range("C2").Resize(UBound(b, 1), UBound(b, 2))
    .Value = b
    .EntireColumn.AutoFit
  End With
End Sub
 
Upvote 0
Remarkable! And you got it done so quickly! It works perfectly. Thanks so much Peter.
 
Upvote 0
Peter and or others, would you be able to give an overview of what the coding does? Not expecting line by line comments but I was after a general understanding of how the coding was put together.
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,972
Members
448,537
Latest member
Et_Cetera

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