Making Teams for Captains

[Beavis97]

Ok I have a spreadsheet that randomizes names and places them into groups. So F9-F31 is the list of names. H9-H31 is the group number. I would like to take the names for group 1 for instance and place them under someone's name. Same for group 2 and so on. I'm not really sure how to just extract names with groups numbers next to them

 

[FormR]

Welcome to the Forum!

I would like to take the names for group 1 for instance and place them under someone's name.

I think you will need to expand on this a little before anyone can give you some practical advice.

 

[Beavis97]

I have columns like this as an example

names Group Team Captain 1 Team Captain 2
Mike 1
Don 1
Tommy 2
Angela 2
Mel 3
Robert 3


so what I'm looking for is for the spreadsheet to take everyone with a 1 in the group column to be listed under team captain 1. Everyone with a group 2 under team captain 2 and so forth.

 

[Beavis97]

Names	Group		Team Captain 1	Team Captain 2	Team Captain 3
John	1
Mike	1
Jose	2
Robert	2
Tommy	3

<colgroup><col span="3"><col><col><col></colgroup><tbody>
</tbody>

so what I'm looking for is for the spreadsheet to take everyone with a 1 in the group column to be listed under team captain 1. Everyone with a group 2 under team captain 2 and so forth.

The reason I need it to do it for me is because the people are added to groups by random, so as I randomize the names, the group will change and thus the team captain will change

 

[DocAElstein]

Hi Beavis,
Here is a simple spreadsheet type code to get you started:

It takes this

Book1
	A	B	C	D
1	names Group Team Captain 1 Team Captain 2	Team Captain 1	Team Captain 2	Team Captain 3
2	Mike 1
3	Don 1
4	Tommy 2
5	Angela 2
6	Mel 3
7	Robert 3
Tabelle1

And after running the code you get this..

Book1
	A	B	C	D
1	names Group Team Captain 1 Team Captain 2	Team Captain 1	Team Captain 2	Team Captain 3
2	Mike 1	Mike	Tommy	Mel
3	Don 1	Don	Angela	Robert
4	Tommy 2
5	Angela 2
6	Mel 3
7	Robert 3
Tabelle1

Code:

[color=darkblue]Option[/color] [color=darkblue]Explicit[/color]
[color=darkblue]Sub[/color] TeamsforCaptainsSpreadsheet()
  [color=darkblue]Dim[/color] wks [color=darkblue]As[/color] Worksheet: [color=darkblue]Set[/color] wks = ActiveSheet
  [color=darkblue]Dim[/color] Name [color=darkblue]As[/color] [color=darkblue]Long[/color], LastName [color=darkblue]As[/color] [color=darkblue]Long[/color], TempLastName [color=darkblue]As[/color] [color=darkblue]Long[/color], TempNameOnly [color=darkblue]As[/color] [color=darkblue]String[/color] [color=green]'Variables for names[/color]
  [color=darkblue]Let[/color] LastName = wks.Cells(Rows.Count, 1).End(xlUp).Row [color=green]'Go to end of sheet in first column, come back up to last entry, get row thereof[/color]
  [color=darkblue]Dim[/color] Captain [color=darkblue]As[/color] [color=darkblue]Long[/color], lastCaptain [color=darkblue]As[/color] [color=darkblue]Long[/color] [color=green]'Variables for captains[/color]
  [color=darkblue]Let[/color] lastCaptain = wks.Cells(1, Columns.Count).End(xlToLeft).Column [color=green]'Go to end of sheet in first row, come back from right to left to last entry, get colun thereof[/color]
    [color=green]'go a loopin in a loop[/color]
    [color=darkblue]For[/color] Name = 2 [color=darkblue]To[/color] LastName [color=darkblue]Step[/color] 1 [color=green]'go along each name and for each name..[/color]
      [color=darkblue]For[/color] Captain = 2 [color=darkblue]To[/color] lastCaptain [color=darkblue]Step[/color] 1 [color=green]'..go along each captain...[/color]
        [color=darkblue]If[/color] Right(wks.Cells(Name, 1).Value, 1) = Right(wks.Cells(1, Captain).Value, 1) [color=darkblue]Then[/color] [color=green]' look for match in last number bit[/color]
          [color=darkblue]Let[/color] TempLastName = wks.Cells(Rows.Count, Captain).End(xlUp).Row [color=green]'..get last name row in captain column[/color]
          [color=darkblue]Let[/color] TempNameOnly = Left(wks.Cells(Name, 1).Value, (Len(wks.Cells(Name, 1).Value) - 1)) [color=green]'Chop off number[/color]
          [color=darkblue]Let[/color] wks.Cells(TempLastName + 1, Captain).Value = TempNameOnly [color=green]'tag the name on at the appropriate place[/color]
        [color=darkblue]Else[/color]
        [color=darkblue]End[/color] [color=darkblue]If[/color]
      [color=darkblue]Next[/color] Captain [color=green]'go to next captain[/color]
    [color=darkblue]Next[/color] Name [color=green]'go to next name[/color]
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

. I was writing this and another one a bit better when you re – replied so looks like you’re in a ‘urry…

..so see How you get on with this for now.

. Maybe catch you again later

Alan

 

[Beavis97]

oh well no, not in a hurry at all, it just didn't look good the first time I posted it, so I reposted it as a more readable version

 

[Beavis97]

A	B	C	D	E	F
Names	Group		Team Captain 1	Team Captain 2	Team Captain 3
Mike	1
Don	1
Tommy	2
Angela	2
Mel	3
Robert	3

<tbody>
</tbody>

This actually looks much better, I guess I should have use this form. At any rate, will your code work for this? this is what is actually looks like on the original spreadsheet

 

[DocAElstein]

.......oh well no, not in a hurry at all, it just didn't look good the first time I posted it, so I reposted it as a more readable version......... At any rate, will your code work for this? this is what is actually looks like on the original spreadsheet

OK, no prob..

This modified code:-

[color=darkblue]Option[/color] [color=darkblue]Explicit[/color]
[color=darkblue]Sub[/color] TeamsforCaptainsSpreadsheet()
  [color=darkblue]Dim[/color] wks [color=darkblue]As[/color] Worksheet: [color=darkblue]Set[/color] wks = ActiveSheet
  [color=darkblue]Dim[/color] Name [color=darkblue]As[/color] [color=darkblue]Long[/color], LastName [color=darkblue]As[/color] [color=darkblue]Long[/color], TempLastName [color=darkblue]As[/color] [color=darkblue]Long[/color], TempNameOnly [color=darkblue]As[/color] [color=darkblue]String[/color] [color=green]'Variables for names[/color]
  [color=darkblue]Let[/color] LastName = wks.Cells(Rows.Count, 1).End(xlUp).Row [color=green]'Go to end of sheet in first column, come back up to last entry, get row thereof[/color]
  [color=darkblue]Dim[/color] Captain [color=darkblue]As[/color] [color=darkblue]Long[/color], lastCaptain [color=darkblue]As[/color] [color=darkblue]Long[/color] [color=green]'Variables for captains[/color]
  [color=darkblue]Let[/color] lastCaptain = wks.Cells(1, Columns.Count).End(xlToLeft).Column [color=green]'Go to end of sheet in first row, come back from right to left to last entry, get colun thereof[/color]
    [color=green]'go a loopin in a loop[/color]
    [color=darkblue]For[/color] Name = 2 [color=darkblue]To[/color] LastName [color=darkblue]Step[/color] 1 [color=green]'go along each name and for each name..[/color]
      [color=darkblue]For[/color] Captain = 3 [color=darkblue]To[/color] lastCaptain [color=darkblue]Step[/color] 1 [color=green]'..go along each captain...[/color]
        Debug.Print wks.Cells(Name, 1).Offset(0, 1).Value & "  " & Right(wks.Cells(1, Captain).Value, 1)
        [color=darkblue]If[/color] [color=darkblue]CStr[/color](wks.Cells(Name, 1).Offset(0, 1).Value) = Right(wks.Cells(1, Captain).Value, 1) [color=darkblue]Then[/color] [color=green]' look for match in last number bit[/color]
          [color=darkblue]Let[/color] TempLastName = wks.Cells(Rows.Count, Captain).End(xlUp).Row [color=green]'..get last name row in captain column[/color]
          [color=darkblue]Let[/color] wks.Cells(TempLastName + 1, Captain).Value = wks.Cells(Name, 1).Value [color=green]'tag the name on at the appropriate place[/color]
        [color=darkblue]Else[/color] [color=green]'Do nothing for no match[/color]
        [color=darkblue]End[/color] [color=darkblue]If[/color]
      [color=darkblue]Next[/color] Captain [color=green]'go to next captain[/color]
    [color=darkblue]Next[/color] Name [color=green]'go to next name[/color]
[color=darkblue]End[/color] [color=darkblue]Sub[/color] [color=green]'TeamsforCaptainsSpreadsheet()[/color]

Takes this

Book1
	A	B	C	D	E	F
1	Names	Group		Team Captain 1	Team Captain 2	Team Captain 3
2	Mike	1
3	Don	1
4	Tommy	2
5	Angela	2
6	Mel	3
7	Robert	3
Tabelle2

And after running the code you get this:

Book1
	A	B	C	D	E	F
1	Names	Group		Team Captain 1	Team Captain 2	Team Captain 3
2	Mike	1		Mike	Tommy	Mel
3	Don	1		Don	Angela	Robert
4	Tommy	2
5	Angela	2
6	Mel	3
7	Robert	3
Tabelle2

..Might be “off” shortly for a while..

. Let me know ‘ow you get on.

. Any problems I’ll “sort you out’ maybe tomorrow if no one else picks the Thread up.

Alan
Bavaria

 

[Peter_SSs]

Do you really need a macro?
Are you using Excel 2010 or later? (If not, this could be modified)
Could the team numbers go in a separate row? (If not, this could be modified)

Formula in D3 is copied across & Down.

Excel Workbook
	A	B	C	D	E	F
1	Names	Group		Team Captain	Team Captain	Team Captain
2				1	2	3
3	Mike	1		Mike	Tommy	Mel
4	Don	1		Don	Angela	Robert
5	Tommy	2
6	Angela	2
7	Mel	3
8	Robert	3
9
Teams

 

[Beavis97]

Is there no way to do it without code? Or is code the only way? Just asking