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

 

[Peter_SSs]

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

Post #9?

 

[shg]

Very clever use of Aggregate, Peter.

 

[Beavis97]

Post #9?

Hey Peter,

Sorry, I posted that before I got the notification that you replied, my apologies. I am trying to get the spreadsheet I was working on at work emailed to me as we speak, but I do have a question. The 1, 2, and 3 under team captain is required in order for the formula to work, correct?

 

[Peter_SSs]

Very clever use of Aggregate, Peter.

Thanks shg.

The 1, 2, and 3 under team captain is required in order for the formula to work, correct?

For the particular formula I posted, "Yes" but there should be alternatives if that is not possible or not desired.

For example,

A. If the numbers in column B just represent what column of the results to put the name in then ..

Excel Workbook
	A	B	C	D	E	F
1	Names	Group		Team Captain X	Team Captain AA	Team Captain 23
2	Mike	1		Mike	Tommy	Mel
3	Don	1		Don	Angela	Robert
4	Tommy	2
5	Angela	2
6	Mel	3
7	Robert	3
Teams by Column

B. If column B is always numbers less than 10 & has to match the number from D1, E1, etc then ..

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

C. More generally, if column B has to match the last "word" from D1, E1 etc then ..

Excel Workbook
	A	B	C	D	E	F
1	Names	Group		Team Captain Jen	Captain 23	This Team Captain is Bob
2	Mike	Bob		Angela	Don	Mike
3	Don	23		Robert	Mel	Tommy
4	Tommy	Bob
5	Angela	Jen
6	Mel	23
7	Robert	Jen
Teams by Last 'Word'

 

[Beavis97]

Peter,

I was finally able to obtain the spreadsheet and I was able to modify your formula slightly in order to obtain the correct results...thank you so much

 

[Peter_SSs]

Peter,

I was finally able to obtain the spreadsheet and I was able to modify your formula slightly in order to obtain the correct results...thank you so much

Cheers, glad you got a successful outcome.

 

[DocAElstein]

…


….

Wow!......Amazing formula

Beavus,

. I think there is never a right way to do these things.
. A way that works for you (for speed considerations etc.) and you are happy with and maybe understand is sufficient. I considered first, as always a formula rather than VBA, but went with VBA as I did not think a formula was possible!!! – Amazing Formula!!. I personally would probably stay with the VBA solution but only because I could never unfortunately understand such an amazing formula! But if you are not too clued up on VBA either then the formula from Peter will almost certainly be the easiest for you to implement so stick with it. Take your pick – your choice

Alan.



Peter
.. Amazing. I did not think it was possible. I could never understand it well enough to be able to comfortably use it. Shame. But It is a great one for such a typical Problem. I bet a lot of people will be copying that to their archives!
Alan

………………………………………….





P.s. Just to round up my contribution, here is a second code I had started – so I finished!!. Similar to the first, but an “Array” type solution – It “captures” the whole spreadsheet data initially in an Array, does all the rearranging in VBA “internally” as it were writing results to another Array that is then pasted into the sheet at the end. Not my favorite as even less easy to follow through, but it is the more typical “professional” approach (I think?) (Second of course to that amazing formula!!)

Code VBA “Array type” solution for Post #8

[color=darkblue]Sub[/color] TeamsforCaptainsArray()
  [color=darkblue]Dim[/color] Data() [color=darkblue]As[/color] Variant: [color=darkblue]Dim[/color] Results() [color=darkblue]As[/color] Variant [color=green]'Arrays for input and output[/color]
  [color=darkblue]Dim[/color] wks [color=darkblue]As[/color] Worksheet: [color=darkblue]Set[/color] wks = ActiveSheet [color=green]'This is the sheet you are looking at[/color]
  [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]Long[/color] [color=green]'Variables for names[/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 Captain[/color]
  [color=darkblue]Dim[/color] HitFlag [color=darkblue]As[/color] Boolean: [color=green]'Flag to indicate if match in Captain number and name number found, Initially we have found no match ('cos we 'aint started yet!!)[/color]
 
  [color=darkblue]Let[/color] Data() = wks.UsedRange.Value [color=green]'Convenient way to "Capture" data in one go- NOTE- Usind UsedRange rather tnan Currentrange because of empty heading: SO nothing elklse must be on spreadsheet![/color]
  [color=darkblue]Let[/color] LastName = [color=darkblue]UBound[/color](Data, 1): [color=darkblue]Let[/color] lastCaptain = [color=darkblue]UBound[/color](Data, 2) [color=green]'Get last names and captain from Array Limits[/color]
  [color=darkblue]ReDim[/color] Results(1 [color=darkblue]To[/color] ((LastName - 1) / 3), 1 [color=darkblue]To[/color] (lastCaptain - 3)) [color=green]'We have enough info to dimension output array: ASSUMES ALL TEAMS ARE SAME SIZE[/color]
    [color=green]'same loopy stuff but putting output in Array rather than spreadsheet[/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 = 4 [color=darkblue]To[/color] lastCaptain [color=darkblue]Step[/color] 1 [color=green]'..go along each captain...[/color]
        [color=darkblue]If[/color] Data(Name, 2) = [color=darkblue]CDbl[/color](Right(Data(1, Captain), 1)) [color=darkblue]Then[/color] [color=green]' look for match in number in column 2 and last number bit in captain name. Converting to a number is necerssary or comparison does not work.[/color]
          [color=darkblue]For[/color] TempNameOnly = 1 [color=darkblue]To[/color] lastCaptain - 3 [color=darkblue]Step[/color] 1 [color=green]'Need to do extra loop to find next empty cell in Array[/color]
            [color=darkblue]If[/color] Results(TempNameOnly, Captain - 3) = "" [color=darkblue]Then[/color] [color=green]'So only if we have an empty cell in Array[/color]
              [color=darkblue]Let[/color] Results(TempNameOnly, Captain - 3) = Data(Name, 1)
              [color=darkblue]Exit[/color] [color=darkblue]For[/color] [color=green]'We exit the loop for finding an empty cell in Array as we just found one![/color]
            [color=darkblue]Else[/color] [color=green]'Make no entry if something is already there[/color]
            [color=darkblue]End[/color] [color=darkblue]If[/color] [color=green]'we now....[/color]
          [color=darkblue]Next[/color] TempNameOnly '....go back and look again for an empty cell in Array
        [color=darkblue]Let[/color] HitFlag = [color=darkblue]False[/color] [color=green]'This is the place to set the flag back to no match[/color]
        [color=darkblue]Else[/color] [color=green]'[/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]Let[/color] wks.Range("D2").Resize((UBound(Results, 1)), (UBound(Results, 2))).Value = Results [color=green]'This is a convenient way to paste the results in one go: resize cell D4 to the size of the output results Array then make this range of values directly equal to the array.[/color]
[color=darkblue]End[/color] [color=darkblue]Sub[/color] [color=green]'TeamsforCaptainsArray()[/color]

… and again SHimpfGlified, without Comments etc.

[color=darkblue]Sub[/color] TeamsforCaptainsArraySHimpfGlified()
  [color=darkblue]Dim[/color] Data(): [color=darkblue]Dim[/color] Results()
  [color=darkblue]Dim[/color] Name [color=darkblue]As[/color] [color=darkblue]Long[/color], TempLastName [color=darkblue]As[/color] [color=darkblue]Long[/color], TempNameOnly [color=darkblue]As[/color] [color=darkblue]Long[/color], Captain [color=darkblue]As[/color] [color=darkblue]Long[/color], HitFlag [color=darkblue]As[/color] [color=darkblue]Boolean[/color]
 
   Data() = ActiveSheet.UsedRange.Value
  [color=darkblue]ReDim[/color] Results(1 [color=darkblue]To[/color] [color=darkblue]UBound[/color](Data, 1), 1 [color=darkblue]To[/color] [color=darkblue]UBound[/color](Data, 2))
   
    [color=darkblue]For[/color] Name = 2 [color=darkblue]To[/color] [color=darkblue]UBound[/color](Data, 1)
      [color=darkblue]For[/color] Captain = 4 [color=darkblue]To[/color] [color=darkblue]UBound[/color](Data, 2)
        [color=darkblue]If[/color] Data(Name, 2) = [color=darkblue]CDbl[/color](Right(Data(1, Captain), 1)) [color=darkblue]Then[/color]
          [color=darkblue]For[/color] TempNameOnly = 1 [color=darkblue]To[/color] [color=darkblue]UBound[/color](Data, 2) - 3
            [color=darkblue]If[/color] Results(TempNameOnly, Captain - 3) = "" [color=darkblue]Then[/color]
            Results(TempNameOnly, Captain - 3) = Data(Name, 1)
            [color=darkblue]Exit[/color] [color=darkblue]For[/color]
            Else: [color=darkblue]End[/color] [color=darkblue]If[/color]
          [color=darkblue]Next[/color] TempNameOnly
          HitFlag = [color=darkblue]False[/color]
        Else: [color=darkblue]End[/color] [color=darkblue]If[/color]
      [color=darkblue]Next[/color] Captain
    [color=darkblue]Next[/color] Name
 
 Range("D2").Resize((UBound(Results, 1)), (UBound(Results, 2))).Value = Results()
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

.. and an XL 2007 File ( “AndButtHead.xlsm” ) with all codes in it for both Post #5 and post # 8:

https://app.box.com/s/0zr3b9jry0c2xjn3qt9h

 

[Beavis97]

Well it was working until I brought it to work. All cells are blank now

 

[Beavis97]

Alright. Well Peter. We appear to be using excel from 2007. Is there a way to modify the code to work properly?

 

[Peter_SSs]

Alright. Well Peter. We appear to be using excel from 2007. Is there a way to modify the code to work properly?

Yes, but what is your actual circumstance/layout?
As per post #9 or post #14A, post #14B, post #14C or something else?