ilikered100
New Member
- Joined
- May 29, 2013
- Messages
- 46
OK... so here's what I need that I can't figure out. Below in Column A is a list of Students with their teacher's name. In column G is a list of Teacher's names for this worksheet. In H2, I have a counta formula which tells me the number of teachers for this grade.
I need a macro that will search column A, starting in row 2, for a teacher's name (partial text **) and paste that cell into it's corresponding column. so Jeff Doe/Brown and Patty Cake/Brown will be copied into column B (cells B2 and B3 respectively). Sally Seashell/Lewis and Jenny Jam/Lewis will be pasted into column C (cells, C2 and C3 respectively)... and so forth until it has done it the number of times that equals the variable in H2.
Also, I would like the search for partial text (Teacher Name in Row 1) to reference the text in cells B1, C1, D1, etc. instead of searching for the actual text of the teacher's name.
Lastly, I'm not sure the counta formula is needed. There may be a better way to do this, I'm just not sure what it is.
I need to accomplish this on several different spreadsheets with varying numbers of students and teachers. Any help is much appreciated.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Student/Teacher[/TD]
[TD]Brown[/TD]
[TD]Lewis [/TD]
[TD]Peterson [/TD]
[TD]Smith[/TD]
[TD][/TD]
[TD] Teacher List[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]John Doe/Smith[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Brown[/TD]
[TD]=counta(F2:F10)
[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Jeff Doe/Brown[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Lewis[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Suzie Que/Smith[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Peterson[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Sally Seashell/Lewis[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Smith[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Jenny Jam/Lewis[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Helen Hill/Peterson[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Patty Cake/Brown[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I need a macro that will search column A, starting in row 2, for a teacher's name (partial text **) and paste that cell into it's corresponding column. so Jeff Doe/Brown and Patty Cake/Brown will be copied into column B (cells B2 and B3 respectively). Sally Seashell/Lewis and Jenny Jam/Lewis will be pasted into column C (cells, C2 and C3 respectively)... and so forth until it has done it the number of times that equals the variable in H2.
Also, I would like the search for partial text (Teacher Name in Row 1) to reference the text in cells B1, C1, D1, etc. instead of searching for the actual text of the teacher's name.
Lastly, I'm not sure the counta formula is needed. There may be a better way to do this, I'm just not sure what it is.
I need to accomplish this on several different spreadsheets with varying numbers of students and teachers. Any help is much appreciated.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Student/Teacher[/TD]
[TD]Brown[/TD]
[TD]Lewis [/TD]
[TD]Peterson [/TD]
[TD]Smith[/TD]
[TD][/TD]
[TD] Teacher List[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]John Doe/Smith[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Brown[/TD]
[TD]=counta(F2:F10)
[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Jeff Doe/Brown[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Lewis[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Suzie Que/Smith[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Peterson[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Sally Seashell/Lewis[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Smith[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Jenny Jam/Lewis[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Helen Hill/Peterson[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Patty Cake/Brown[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]