Advanced Query or VBA Code

[d488z]

I have a spreadsheet that contains over 3000 rows of student data. The last two columns contain Staff Code and Class. I am looking for a way to add the staff code to the class (sometimes we have two or maybe three teachers). What I normally do is use the concatenate function. I sort the spreadsheet by class then go down and if that student has two teachers I manually copy the one Staff Code and paste it in the first cell (e.g CMF - GH) and then drag the staff code down until I reach the end of that class....and I do this for every shared class. There must be an easier way to do this. My eyes hurt scrolling down that much data.

[TABLE="width: 192"]
<colgroup><col span="2"></colgroup><tbody>[TR]
[TD]StaffCode[/TD]
[TD]Class[/TD]
[/TR]
[TR]
[TD]CMF[/TD]
[TD]7-ART-1e[/TD]
[/TR]
[TR]
[TD]CMF[/TD]
[TD]7-ART-1e
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 192"]
<colgroup><col span="2"></colgroup><tbody>[TR]
[TD]RK[/TD]
[TD]7-ART-1e[/TD]
[/TR]
[TR]
[TD]RK[/TD]
[TD]7-ART-1e[/TD]
[/TR]
[TR]
[TD]RK[/TD]
[TD]7-ART-1e
[/TD]
[/TR]
</tbody>[/TABLE]

so I add staff Code by manually adding (CMF - RK) and using the concatenate function the end result looks like this 7-ART-1e (CMF - RK)
The query needs to look at class 7-ART-1e and in this instance we have two teachers sharing the group, add their code as above.

Thanks for looking at my post.

[TABLE="width: 192"]
<colgroup><col span="2"></colgroup><tbody></tbody>[/TABLE]

 

[alansidman]

I used Power Query. Uploaded the table. Duplicated the Table and then merged the table with an Left Outer join on the class. Here is the Mcode for that action.

let
    Source = Table.NestedJoin(#"Table1 (2)", {"Class"}, #"Table1 (2)", {"Class"}, "Table1 (2)", JoinKind.LeftOuter),
    #"Expanded Table1 (2)" = Table.ExpandTableColumn(Source, "Table1 (2)", {"StaffCode"}, {"Table1 (2).StaffCode"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Table1 (2)", "Custom", each if [#"Table1 (2).StaffCode"]=[StaffCode] then null else[StaffCode]),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = "RK")),
    #"Merged Columns" = Table.CombineColumns(#"Filtered Rows",{"Table1 (2).StaffCode", "Custom"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"Merged"),
    #"Reordered Columns" = Table.ReorderColumns(#"Merged Columns",{"Merged", "StaffCode", "Class"}),
    #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"StaffCode"})
in
    #"Removed Columns"

[TABLE="class: thin_grid"]
<tbody>[TR]
[TD]v[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ECF0F0]#ECF0F0[/URL] , align: center"]A[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ECF0F0]#ECF0F0[/URL] , align: center"]B[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ECF0F0]#ECF0F0[/URL] , align: center"]1[/TD]
[TD="bgcolor: #FFFFFF"]Merged[/TD]
[TD="bgcolor: #FFFFFF"]Class[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ECF0F0]#ECF0F0[/URL] , align: center"]2[/TD]
[TD="bgcolor: #FFFFFF"]CMF-RK[/TD]
[TD="bgcolor: #FFFFFF"]7-ART-1e[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ECF0F0]#ECF0F0[/URL] , align: center"]3[/TD]
[TD="bgcolor: #FFFFFF"]CMF-RK[/TD]
[TD="bgcolor: #FFFFFF"]7-ART-1e[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ECF0F0]#ECF0F0[/URL] , align: center"]4[/TD]
[TD="bgcolor: #FFFFFF"]CMF-RK[/TD]
[TD="bgcolor: #FFFFFF"]7-ART-1e[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ECF0F0]#ECF0F0[/URL] , align: center"]5[/TD]
[TD="bgcolor: #FFFFFF"]CMF-RK[/TD]
[TD="bgcolor: #FFFFFF"]7-ART-1e[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ECF0F0]#ECF0F0[/URL] , align: center"]6[/TD]
[TD="bgcolor: #FFFFFF"]CMF-RK[/TD]
[TD="bgcolor: #FFFFFF"]7-ART-1e[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ECF0F0]#ECF0F0[/URL] , align: center"]7[/TD]
[TD="bgcolor: #FFFFFF"]CMF-RK[/TD]
[TD="bgcolor: #FFFFFF"]7-ART-1e[/TD]
[/TR]
</tbody>[/TABLE]

 

[DanteAmor]

Assuming you have the headings in row 1, based on this the last 2 columns are searched.
The macro puts the result in a new column after the last column.


I did the test with 3200 records and it took 3 seconds.
Try and tell me.

Sub test()
  Dim lc As Long, lr As Long, i As Long, f As Range, cad As String
  Dim a() As Variant, b() As Variant, j As Long
  Application.ScreenUpdating = False
  lc = Cells(1, Columns.Count).End(xlToLeft).Column
  lr = Cells(Rows.Count, lc).End(xlUp).Row
  Columns(lc).Offset(, 1).ClearContents
  a = Range(Cells(2, lc - 1), Cells(lr, lc)).Value
  ReDim b(1 To UBound(a), 1 To 1)
  For i = 1 To UBound(a)
    cad = ""
    For j = 1 To UBound(a)
      If a(j, 2) = a(i, 2) Then If InStr(1, cad, a(j, 1)) = 0 Then cad = cad & " " & a(j, 1)
    Next
    b(i, 1) = Trim(cad)
  Next
  Range("D2").Resize(UBound(b)).Value = b()
End Sub

 

[d488z]

Hello Alan

Thanks for helping me out. I am still learning, so I have got to the Power Query Editor and everything is greyed out, the only options to me is Close & Load, Manage Parameters, Date source settings and New Sources. I think I broke something .... because excel isn't responding now. ooops I am using 0ffice 365... took a while to find it, I have never used power query before.

Kind Regards
M Dabbs

 

[d488z]

Hello DanteAmor

I have used the code and at first I couldn't see anything happening so I copied just the two columns of data into a new worksheet and hey presto!!! I got the result. .

In my original extract of data the two columns are V & W (the last two columns on the worksheet).

I then use the find and replace to add a dash in between the names...so CMF RK becomes CMF - RK ...I then need to wrap this in Brackets.

I then use this formula =CONCATENATE(B2," (",D2,")") on the example I am working on.


The end result I am trying to achieve is 7-ART-1e (CMF - RK)
Thanks
M Dabbs

 

[DanteAmor]

Try this

Sub test()
  Dim lc As Long, lr As Long, i As Long, f As Range, cad As String
  Dim a() As Variant, b() As Variant, j As Long
  Application.ScreenUpdating = False
  'lc = Cells(1, Columns.Count).End(xlToLeft).Column
  lc = Columns("W").Column
  lr = Cells(Rows.Count, lc).End(xlUp).Row
  Columns(lc).Offset(, 1).ClearContents
  a = Range(Cells(2, lc - 1), Cells(lr, lc)).Value
  ReDim b(1 To UBound(a), 1 To 1)
  For i = 1 To UBound(a)
    cad = ""
    For j = 1 To UBound(a)
      If a(j, 2) = a(i, 2) Then
        If InStr(1, cad, a(j, 1)) = 0 Then
          If cad = "" Then
            cad = a(j, 1)
          Else
            cad = cad & " (" & a(j, 1) & ")"
          End If
        End If
      End If
    Next
    b(i, 1) = Trim(cad)
  Next
  Cells(2, lc + 1).Resize(UBound(b)).Value = b()
End Sub

 

[alansidman]

To learn more about PQ, click on the link in my signature.

 

[d488z]

To learn more about PQ, click on the link in my signature.

Hello Alan

I did see the link and tried to access it at work....our IT department has blocked the URL . I am now watching the video at home. Fingers crossed will try again tomorrow.

Thanks
M Dabbs

 

[DanteAmor]

@d488z Did You see my updated code in post #7 ?

 

[d488z]

Hello Alan

I went into Power Query Editor, New Source, Other Sources, Blank Query and then into advanced editor pasted the code in.

I pasted the code into advanced editor and I get this back.

Expression.Error: The import Table1 (2) matches no exports. Did you miss a module reference? and when I click on Go to Error....

Expression.Error: The name 'Table1 (2)' wasn't recognized. Make sure it's spelled correctly.

Is there a step I am missing out, I note at the top your first reply "uploaded the table"...

Kind Regards
M Dabbs