Create column with concatenated items

I

ilsley_excel

Board Regular
Joined
Mar 5, 2015
Messages
54
Office Version
  1. 2010
Platform
  1. Windows
Hi,

I am trying to create a column (COLUMN F) that concatenates a pupil's subjects (COLUMN C) where they have achieved a Grade A or O (COLUMN B) for Data Type 'PR' (COLUMN D), but I can't get my head around a formula/UDF that will allow me to do this. I've been struggling for a couple of hours trying to write a formula that will work, to no avail.

See below... the desired result is in COLUMN F.


PupilIDGrade SubjectData TypePR_TotalAO Subjects_AO
Pupil01AArtBH11Art, Computing & ICT, D&T, Geography, History, Maths, Music, PE, RS, Science, Spanish
Pupil01AArtEF11Art, Computing & ICT, D&T, Geography, History, Maths, Music, PE, RS, Science, Spanish
Pupil01AArtPR11Art, Computing & ICT, D&T, Geography, History, Maths, Music, PE, RS, Science, Spanish
Pupil01BComputing & ICTBH11Art, Computing & ICT, D&T, Geography, History, Maths, Music, PE, RS, Science, Spanish
Pupil01BComputing & ICTEF11Art, Computing & ICT, D&T, Geography, History, Maths, Music, PE, RS, Science, Spanish
Pupil01OComputing & ICTPR11Art, Computing & ICT, D&T, Geography, History, Maths, Music, PE, RS, Science, Spanish
Pupil01AD&TBH11Art, Computing & ICT, D&T, Geography, History, Maths, Music, PE, RS, Science, Spanish
Pupil01AD&TEF11Art, Computing & ICT, D&T, Geography, History, Maths, Music, PE, RS, Science, Spanish
Pupil01AD&TPR11Art, Computing & ICT, D&T, Geography, History, Maths, Music, PE, RS, Science, Spanish
Pupil01BEnglishBH11Art, Computing & ICT, D&T, Geography, History, Maths, Music, PE, RS, Science, Spanish
Pupil01CEnglishEF11Art, Computing & ICT, D&T, Geography, History, Maths, Music, PE, RS, Science, Spanish
Pupil01BEnglishPR11Art, Computing & ICT, D&T, Geography, History, Maths, Music, PE, RS, Science, Spanish
Pupil01AGeographyBH11Art, Computing & ICT, D&T, Geography, History, Maths, Music, PE, RS, Science, Spanish
Pupil01AGeographyEF11Art, Computing & ICT, D&T, Geography, History, Maths, Music, PE, RS, Science, Spanish
Pupil01AGeographyPR11Art, Computing & ICT, D&T, Geography, History, Maths, Music, PE, RS, Science, Spanish
Pupil01AHistoryBH11Art, Computing & ICT, D&T, Geography, History, Maths, Music, PE, RS, Science, Spanish
Pupil01BHistoryEF11Art, Computing & ICT, D&T, Geography, History, Maths, Music, PE, RS, Science, Spanish
Pupil01OHistoryPR11Art, Computing & ICT, D&T, Geography, History, Maths, Music, PE, RS, Science, Spanish
Pupil01BMathsBH11Art, Computing & ICT, D&T, Geography, History, Maths, Music, PE, RS, Science, Spanish
Pupil01BMathsEF11Art, Computing & ICT, D&T, Geography, History, Maths, Music, PE, RS, Science, Spanish
Pupil01OMathsPR11Art, Computing & ICT, D&T, Geography, History, Maths, Music, PE, RS, Science, Spanish
Pupil01AMusicBH11Art, Computing & ICT, D&T, Geography, History, Maths, Music, PE, RS, Science, Spanish
Pupil01AMusicEF11Art, Computing & ICT, D&T, Geography, History, Maths, Music, PE, RS, Science, Spanish
Pupil01OMusicPR11Art, Computing & ICT, D&T, Geography, History, Maths, Music, PE, RS, Science, Spanish
Pupil01APEBH11Art, Computing & ICT, D&T, Geography, History, Maths, Music, PE, RS, Science, Spanish
Pupil01APEEF11Art, Computing & ICT, D&T, Geography, History, Maths, Music, PE, RS, Science, Spanish
Pupil01APEPR11Art, Computing & ICT, D&T, Geography, History, Maths, Music, PE, RS, Science, Spanish
Pupil01CRSBH11Art, Computing & ICT, D&T, Geography, History, Maths, Music, PE, RS, Science, Spanish
Pupil01ARSEF11Art, Computing & ICT, D&T, Geography, History, Maths, Music, PE, RS, Science, Spanish
Pupil01ORSPR11Art, Computing & ICT, D&T, Geography, History, Maths, Music, PE, RS, Science, Spanish
Pupil01AScienceBH11Art, Computing & ICT, D&T, Geography, History, Maths, Music, PE, RS, Science, Spanish
Pupil01AScienceEF11Art, Computing & ICT, D&T, Geography, History, Maths, Music, PE, RS, Science, Spanish
Pupil01OSciencePR11Art, Computing & ICT, D&T, Geography, History, Maths, Music, PE, RS, Science, Spanish
Pupil01ASpanishBH11Art, Computing & ICT, D&T, Geography, History, Maths, Music, PE, RS, Science, Spanish
Pupil01ASpanishEF11Art, Computing & ICT, D&T, Geography, History, Maths, Music, PE, RS, Science, Spanish
Pupil01OSpanishPR11Art, Computing & ICT, D&T, Geography, History, Maths, Music, PE, RS, Science, Spanish
Pupil02AArtBH10Art, Computing & ICT, D&T, English, French, Geography, History, Maths, RS, Science
Pupil02BArtEF10Art, Computing & ICT, D&T, English, French, Geography, History, Maths, RS, Science
Pupil02OArtPR10Art, Computing & ICT, D&T, English, French, Geography, History, Maths, RS, Science
Pupil02AComputing & ICTBH10Art, Computing & ICT, D&T, English, French, Geography, History, Maths, RS, Science
Pupil02AComputing & ICTEF10Art, Computing & ICT, D&T, English, French, Geography, History, Maths, RS, Science
Pupil02AComputing & ICTPR10Art, Computing & ICT, D&T, English, French, Geography, History, Maths, RS, Science
Pupil02AD&TBH10Art, Computing & ICT, D&T, English, French, Geography, History, Maths, RS, Science
Pupil02AD&TEF10Art, Computing & ICT, D&T, English, French, Geography, History, Maths, RS, Science
Pupil02OD&TPR10Art, Computing & ICT, D&T, English, French, Geography, History, Maths, RS, Science
Pupil02AEnglishBH10Art, Computing & ICT, D&T, English, French, Geography, History, Maths, RS, Science
Pupil02AEnglishEF10Art, Computing & ICT, D&T, English, French, Geography, History, Maths, RS, Science
Pupil02AEnglishPR10Art, Computing & ICT, D&T, English, French, Geography, History, Maths, RS, Science
Pupil02AFrenchBH10Art, Computing & ICT, D&T, English, French, Geography, History, Maths, RS, Science
Pupil02CFrenchEF10Art, Computing & ICT, D&T, English, French, Geography, History, Maths, RS, Science
Pupil02OFrenchPR10Art, Computing & ICT, D&T, English, French, Geography, History, Maths, RS, Science
Pupil02AGeographyBH10Art, Computing & ICT, D&T, English, French, Geography, History, Maths, RS, Science
Pupil02AGeographyEF10Art, Computing & ICT, D&T, English, French, Geography, History, Maths, RS, Science
Pupil02OGeographyPR10Art, Computing & ICT, D&T, English, French, Geography, History, Maths, RS, Science
Pupil02AHistoryBH10Art, Computing & ICT, D&T, English, French, Geography, History, Maths, RS, Science
Pupil02AHistoryEF10Art, Computing & ICT, D&T, English, French, Geography, History, Maths, RS, Science
Pupil02AHistoryPR10Art, Computing & ICT, D&T, English, French, Geography, History, Maths, RS, Science
Pupil02AMathsBH10Art, Computing & ICT, D&T, English, French, Geography, History, Maths, RS, Science
Pupil02AMathsEF10Art, Computing & ICT, D&T, English, French, Geography, History, Maths, RS, Science
Pupil02OMathsPR10Art, Computing & ICT, D&T, English, French, Geography, History, Maths, RS, Science
Pupil02ARSBH10Art, Computing & ICT, D&T, English, French, Geography, History, Maths, RS, Science
Pupil02ARSEF10Art, Computing & ICT, D&T, English, French, Geography, History, Maths, RS, Science
Pupil02ORSPR10Art, Computing & ICT, D&T, English, French, Geography, History, Maths, RS, Science
Pupil02AScienceBH10Art, Computing & ICT, D&T, English, French, Geography, History, Maths, RS, Science
Pupil02AScienceEF10Art, Computing & ICT, D&T, English, French, Geography, History, Maths, RS, Science
Pupil02OSciencePR10Art, Computing & ICT, D&T, English, French, Geography, History, Maths, RS, Science
Pupil03AArtBH12Art, Computing & ICT, D&T, English, French, Geography, History, Maths, Music, PE, RS, Science
Pupil03BArtEF12Art, Computing & ICT, D&T, English, French, Geography, History, Maths, Music, PE, RS, Science
Pupil03OArtPR12Art, Computing & ICT, D&T, English, French, Geography, History, Maths, Music, PE, RS, Science
Pupil03BComputing & ICTBH12Art, Computing & ICT, D&T, English, French, Geography, History, Maths, Music, PE, RS, Science
Pupil03BComputing & ICTEF12Art, Computing & ICT, D&T, English, French, Geography, History, Maths, Music, PE, RS, Science
Pupil03OComputing & ICTPR12Art, Computing & ICT, D&T, English, French, Geography, History, Maths, Music, PE, RS, Science
Pupil03AD&TBH12Art, Computing & ICT, D&T, English, French, Geography, History, Maths, Music, PE, RS, Science
Pupil03AD&TEF12Art, Computing & ICT, D&T, English, French, Geography, History, Maths, Music, PE, RS, Science
Pupil03OD&TPR12Art, Computing & ICT, D&T, English, French, Geography, History, Maths, Music, PE, RS, Science
Pupil03AEnglishBH12Art, Computing & ICT, D&T, English, French, Geography, History, Maths, Music, PE, RS, Science
Pupil03AEnglishEF12Art, Computing & ICT, D&T, English, French, Geography, History, Maths, Music, PE, RS, Science
Pupil03OEnglishPR12Art, Computing & ICT, D&T, English, French, Geography, History, Maths, Music, PE, RS, Science
Pupil03AFrenchBH12Art, Computing & ICT, D&T, English, French, Geography, History, Maths, Music, PE, RS, Science
Pupil03AFrenchEF12Art, Computing & ICT, D&T, English, French, Geography, History, Maths, Music, PE, RS, Science
Pupil03AFrenchPR12Art, Computing & ICT, D&T, English, French, Geography, History, Maths, Music, PE, RS, Science
Pupil03BGeographyBH12Art, Computing & ICT, D&T, English, French, Geography, History, Maths, Music, PE, RS, Science
Pupil03AGeographyEF12Art, Computing & ICT, D&T, English, French, Geography, History, Maths, Music, PE, RS, Science
Pupil03OGeographyPR12Art, Computing & ICT, D&T, English, French, Geography, History, Maths, Music, PE, RS, Science
Pupil03AHistoryBH12Art, Computing & ICT, D&T, English, French, Geography, History, Maths, Music, PE, RS, Science
Pupil03AHistoryEF12Art, Computing & ICT, D&T, English, French, Geography, History, Maths, Music, PE, RS, Science
Pupil03OHistoryPR12Art, Computing & ICT, D&T, English, French, Geography, History, Maths, Music, PE, RS, Science
Pupil03AMathsBH12Art, Computing & ICT, D&T, English, French, Geography, History, Maths, Music, PE, RS, Science
Pupil03AMathsEF12Art, Computing & ICT, D&T, English, French, Geography, History, Maths, Music, PE, RS, Science
Pupil03OMathsPR12Art, Computing & ICT, D&T, English, French, Geography, History, Maths, Music, PE, RS, Science
Pupil03AMusicBH12Art, Computing & ICT, D&T, English, French, Geography, History, Maths, Music, PE, RS, Science
Pupil03AMusicEF12Art, Computing & ICT, D&T, English, French, Geography, History, Maths, Music, PE, RS, Science
Pupil03OMusicPR12Art, Computing & ICT, D&T, English, French, Geography, History, Maths, Music, PE, RS, Science
Pupil03APEBH12Art, Computing & ICT, D&T, English, French, Geography, History, Maths, Music, PE, RS, Science
Pupil03APEEF12Art, Computing & ICT, D&T, English, French, Geography, History, Maths, Music, PE, RS, Science
Pupil03OPEPR12Art, Computing & ICT, D&T, English, French, Geography, History, Maths, Music, PE, RS, Science
Pupil03ARSBH12Art, Computing & ICT, D&T, English, French, Geography, History, Maths, Music, PE, RS, Science
Pupil03ARSEF12Art, Computing & ICT, D&T, English, French, Geography, History, Maths, Music, PE, RS, Science
Pupil03ARSPR12Art, Computing & ICT, D&T, English, French, Geography, History, Maths, Music, PE, RS, Science
Pupil03AScienceBH12Art, Computing & ICT, D&T, English, French, Geography, History, Maths, Music, PE, RS, Science
Pupil03AScienceEF12Art, Computing & ICT, D&T, English, French, Geography, History, Maths, Music, PE, RS, Science
Pupil03OSciencePR12Art, Computing & ICT, D&T, English, French, Geography, History, Maths, Music, PE, RS, Science

<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>



Any ideas?

Thanks in advance for your help!
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Sort by date Sort by votes
Here's my suggestion. Make a pivot table. Use the data type and grade as report filters. Select PR as the only data type and A/O as Grade types (multiple items). Use Subject as column labels and PupilID as row labels. Use Count of Subject in the sum values box. You will then get a table of 1s and blanks that looks like below. You can then set up a concatenate formula that can be copied downwards for each Pupil. something like this:

=IF(I6=1,I$5&", ","")&IF(J6=1,J$5&", ","")&IF(K6=1,K$5&", ","")&IF(L6=1,L$5&", ","")&IF(M6=1,M$5&", ","")&IF(N6=1,N$5&", ","")&IF(O6=1,O$5&", ","")&IF(P6=1,P$5&", ","")&IF(Q6=1,Q$5&", ","")&IF(R6=1,R$5&", ","")&IF(S6=1,S$5&", ","")&IF(T6=1,T$5&", ","")&IF(U6=1,U$5&", ","")



Excel 2010
HIJKLMNOPQRSTUV
1Data TypePR
2Grade(Multiple Items)
3
4Count of SubjectColumn Labels
5Row LabelsArtComputing & ICTD&TEnglishFrenchGeographyHistoryMathsMusicPERSScienceSpanishGrand Total
6Pupil011111111111111
7Pupil02111111111110
8Pupil0311111111111112
9Grand Total333223332233133
10
11
12Pupil01Art, Computing & ICT, D&T, Geography, History, Maths, Music, PE, RS, Science, Spanish,
13Pupil02Art, Computing & ICT, D&T, English, French, Geography, History, Maths, RS, Science,
14Pupil03Art, Computing & ICT, D&T, English, French, Geography, History, Maths, Music, PE, RS, Science,
Sheet1
 
Upvote 0
Hi. That does work, though ideally, I'd have liked to have created a UDF that concatenates all the values straight into column F.

Thank you very much for your help - I can use this as a starting point!
 
Upvote 0
Try this:
Code: 
Public Function GetSubjects(rng As Range, pupil As String)

Dim pupilCol, gradeCol, subjectCol, DataTypeCol As Integer
Dim iRow As Integer
pupilCol = 1 'Column A
gradeCol = 2 'Column B
subjectCol = 3 'Column C
DataTypeCol = 4 'Column D

For iRow = 1 To rng.Rows.Count
    If rng.Cells(iRow, pupilCol) = pupil And rng.Cells(iRow, DataTypeCol) = "PR" And (rng.Cells(iRow, gradeCol) = "A" Or rng.Cells(iRow, gradeCol) = "O") Then
        GetSubjects = GetSubjects & rng.Cells(iRow, subjectCol) & ", "
    End If
Next iRow
If Len(GetSubjects) > 0 Then
    GetSubjects = Mid(GetSubjects, 1, Len(GetSubjects) - 2)
End If
End Function

Then in cell F2 enter:
=GetSubjects($A$2:$D$103,A2) and copy down...
 
Upvote 0
CodeNinja said:
Try this:
Code: 
Public Function GetSubjects(rng As Range, pupil As String)

Dim pupilCol, gradeCol, subjectCol, DataTypeCol As Integer
Dim iRow As Integer
pupilCol = 1 'Column A
gradeCol = 2 'Column B
subjectCol = 3 'Column C
DataTypeCol = 4 'Column D

For iRow = 1 To rng.Rows.Count
    If rng.Cells(iRow, pupilCol) = pupil And rng.Cells(iRow, DataTypeCol) = "PR" And (rng.Cells(iRow, gradeCol) = "A" Or rng.Cells(iRow, gradeCol) = "O") Then
        GetSubjects = GetSubjects & rng.Cells(iRow, subjectCol) & ", "
    End If
Next iRow
If Len(GetSubjects) > 0 Then
    GetSubjects = Mid(GetSubjects, 1, Len(GetSubjects) - 2)
End If
End Function

Then in cell F2 enter:
=GetSubjects($A$2:$D$103,A2) and copy down...
Click to expand...



Thank you so much for this - this actually works perfectly. Amazing!

The only problem is that when I run it against actual data (of which there are sometimes in excess of 10,000 rows), it is really slow to process. Any ideas why this would be the case?
 
Upvote 0
ilsley_excel said:
Thank you so much for this - this actually works perfectly. Amazing!

The only problem is that when I run it against actual data (of which there are sometimes in excess of 10,000 rows), it is really slow to process. Any ideas why this would be the case?
Click to expand...

Hi ilsley_excel,
I'm not at all surprised that running this against 10k rows slows it down a ton. The code is looping through 10k rows for each cell you enter this into. You have a few options... My first choice would be to copy and paste values the results of the formula.

If you want this dynamic, you could get a distinct list of "Students" and put the formula next to them, thus reducing the number of rows it is looping through by a ton... (IE in your example above, you would add another sheet or something like:
pupil01Formula
pupil02Formula
pupil03Formula

Finally, you could try to make the code more efficient with a lookup of some sort... maybe add a helper column that gives you all the data you need and rng.find that data...
For the following code, I added that in column A and replaced with values ... Be sure to replace with values or this will not work.

Code: 
Public Function GetSubjects(rng As Range, pupil As String)

Dim pupilCol, gradeCol, subjectCol, DataTypeCol As Integer
Dim iRow As Integer
Dim currentCell As Range
Dim startCell As Range
Dim startGrade As String
Dim NotStartGrade As String

pupilCol = 1 'Column A
gradeCol = 2 'Column B
subjectCol = 3 'Column C
DataTypeCol = 4 'Column D

If rng.Find(pupil & "APR").Row < rng.Find(pupil & "OPR").Row Then
    Set currentCell = rng.Find(pupil & "APR")
    startGrade = "A"
    NotStartGrade = "O"
Else
    Set currentCell = rng.Find(pupil & "OPR")
    startGrade = "O"
    NotStartGrade = "A"
End If
Set startCell = currentCell

If Not currentCell Is Nothing Then
    GetSubjects = currentCell.Offset(0, 3) & ", "
    While rng.Find(what:=pupil & startGrade & "PR", after:=currentCell).Address <> startCell.Address Or (rng.Find(what:=pupil & NotStartGrade & "PR", after:=currentCell).Row > currentCell.Row And Not rng.Find(what:=pupil & NotStartGrade & "PR", after:=currentCell) Is Nothing)
        If (rng.Find(what:=pupil & startGrade & "PR", after:=currentCell).Row < rng.Find(what:=pupil & NotStartGrade & "PR", after:=currentCell).Row And rng.Find(what:=pupil & startGrade & "PR", after:=currentCell).Address <> startCell.Address) Or rng.Find(what:=pupil & NotStartGrade & "PR", after:=currentCell) Is Nothing Then
            Set currentCell = rng.Find(what:=pupil & startGrade & "PR", after:=currentCell)
        Else
            Set currentCell = rng.Find(what:=pupil & NotStartGrade & "PR", after:=currentCell)
        End If
        GetSubjects = GetSubjects & currentCell.Offset(0, 3) & ", "
    Wend
End If


'For iRow = 1 To rng.Rows.Count
'    If rng.Cells(iRow, pupilCol) = pupil And rng.Cells(iRow, DataTypeCol) = "PR" And (rng.Cells(iRow, gradeCol) = "A" Or rng.Cells(iRow, gradeCol) = "O") Then
'        GetSubjects = GetSubjects & rng.Cells(iRow, subjectCol) & ", "
'    End If
'Next iRow
If Len(GetSubjects) > 0 Then
    GetSubjects = Mid(GetSubjects, 1, Len(GetSubjects) - 2)
End If


End Function

This way you are looping through a lot less... It still may take a lot of time though, so your best bet is to run something and replace with values.

Good luck,

CN.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Dynamic Grade Look Up using 2 criteria
Replies
5
Views
186
Fluff
Fluff
D
  • Solved
Find Partial Text and Return First Part of the Word
Replies
4
Views
461
Peter_SSs
Peter_SSs
MrDB4Excel
  • Solved
DuckDuckGo Conditional Format
2
Replies
12
Views
280
MrDB4Excel
MrDB4Excel
D
  • Question
Organizing data for a textbook order workbook
Replies
0
Views
159
djaida
D
M
  • Question
My code works of one Userform but on the other it says "File not found"
Replies
0
Views
348
MO24
M

Forum statistics

Threads
1,214,784
Messages
6,121,535
Members
449,037
Latest member
tmmotairi

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