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]

Did you do this first before running the code: Uploaded the table. Duplicated the Table and then (this is where the code kicks in) merged the table with an Left Outer join on the class.

 

[d488z]

Hi Alan

I know where I am going wrong, being an absolute novice.

When you say uploaded the table and duplicated it.

I have a new a new worksheet with just 2 columns (Class & Staffcode)

I then go to Data (Office 365) and click on table range from the ribbon. This gives me the Cell references and click on OK.

This brings me to power query editor, I can see my two columns. I can right Click on Columns and duplicate....but I feel this is where I am making a mistake. I am duplicating columns where as your code your duplicating a table.

When I do the outer merge I get a new column but data says null...this is the code I pasted out of Advanced Editor....it looks nothing like yours.

let
Source = Excel.CurrentWorkbook(){[Name="Table8"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Class", type text}, {"StaffCode", type text}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Class"}, #"Changed Type", {"StaffCode"}, "Changed Type", JoinKind.LeftOuter),
#"Expanded Changed Type" = Table.ExpandTableColumn(#"Merged Queries", "Changed Type", {"Class", "StaffCode"}, {"Changed Type.Class", "Changed Type.StaffCode"})
in
#"Expanded Changed Type"

 

[Paul Ked]

Hi Dante

I saw your code and it's far easier to implement than PQ, nice one!

 

[DanteAmor]

Hi Dante

I saw your code and it's far easier to implement than PQ, nice one!

Thanks @Paul Ked, It's correct the macro works from post #3 , @d488z, he asked for some changes and I put them in post #6 .