Need help with macro

[henrybrent1974]

I am defiantly new to macros. i have learned a lot from this forum but cant seem to figure out how to write this macro.
I am trying to take 3 player from each weekly results based on the previous week. For example, the first week the top 3 players would qualify. In the second week if any of the top 3 are the same as week #1 top 3 the take 4th place and so on. need this to continue for 30 weeks.

[TABLE="class: grid, width: 120"]
<tbody>[TR]
[TD][/TD]
[TD]Week #1
[/TD]
[/TR]
[TR]
[TD]1st
[/TD]
[TD]Jason
[/TD]
[/TR]
[TR]
[TD]2nd
[/TD]
[TD]Troy
[/TD]
[/TR]
[TR]
[TD]3rd
[/TD]
[TD]Mike
[/TD]
[/TR]
[TR]
[TD]4th
[/TD]
[TD]Ricky
[/TD]
[/TR]
[TR]
[TD]5th
[/TD]
[TD]John
[/TD]
[/TR]
[TR]
[TD]6th
[/TD]
[TD]Mark
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid, width: 120"]
<tbody>[TR]
[TD][/TD]
[TD]Week #2
[/TD]
[/TR]
[TR]
[TD]1st
[/TD]
[TD]Mike
[/TD]
[/TR]
[TR]
[TD]2nd
[/TD]
[TD]Joe
[/TD]
[/TR]
[TR]
[TD]3rd
[/TD]
[TD]Jake
[/TD]
[/TR]
[TR]
[TD]4th
[/TD]
[TD]Jason
[/TD]
[/TR]
[TR]
[TD]5th
[/TD]
[TD]Bubba
[/TD]
[/TR]
[TR]
[TD]6th
[/TD]
[TD]Trey
[/TD]
[/TR]
</tbody>[/TABLE]

So my qualifiers so for would be:
1. Jason
2. Troy
3. Mike
4. Joe
5. Jake
6. Bubba

Any help would be appreciated so much

 

[Peter_SSs]

Welcome to the MrExcel board!

Assuming the layout is as shown below (including a blank row between each week), try this in a copy of your workbook.

Sub Get_Qualifiers()
  Dim d As Object
  Dim rA As Range
  Dim Got As Long, r As Long
  
  Const NumPerWeek As Long = 3  '<- How many to pick from each week
  
  Set d = CreateObject("Scripting.Dictionary")
  d.CompareMode = 1
  For Each rA In Range("B1", Range("B" & Rows.Count).End(xlUp)).SpecialCells(xlConstants).Areas
    Got = 0
    r = 1
    Do
      r = r + 1
      If Not d.exists(rA.Cells(r).Value) Then
        d(rA.Cells(r).Value) = 1
        Got = Got + 1
      End If
    Loop Until Got = NumPerWeek Or r = rA.Rows.Count
  Next rA
  With Range("D1")
    .Value = "Qualifiers"
    .Offset(1).Resize(d.Count).Value = Application.Transpose(d.keys)
    .EntireColumn.AutoFit
  End With
End Sub

My sample data and results are below.
Note that only two qualifiers were added from week 3 because all the rest had already qualified. Is that what you want?

Book1
	A	B	C	D
1		Week#1		Qualifiers
2	1st	Jason		Jason
3	2nd	Troy		Troy
4	3rd	Mike		Mike
5	4th	Ricky		Joe
6	5th	John		Jake
7	6th	Mark		Bubba
8				Sam
9		Week#2		Jim
10	1st	Mike		Jen
11	2nd	Joe		Ann
12	3rd	Jake		Sue
13	4th	Jason
14	5th	Bubba
15	6th	Trey
16
17		Week#3
18	1st	Mike
19	2nd	Sam
20	3rd	Joe
21	4th	Jason
22	5th	Jim
23	6th	Troy
24
25		Week#4
26	1st	Jen
27	2nd	Sam
28	3rd	Ann
29	4th	Jason
30	5th	Sue
31	6th	Troy
Qualifiers

 

[henrybrent1974]

Thanks for the help. My weekly data is arranged week 1 thru week 5 from left to right then skip a row and week 6 thru week 10 right below and so forth. Sorry for putting my sample going down. i couldn't figure out how to make a table next to the other one.

 

[henrybrent1974]

Also it won' necessarily be 3 players taken every week. For example if 1st, 2nd and 3rd are already qualified then i only need 5th and 6th. Or if 2nd, 3rd, and 6th are qualified then I only need 1st ,4th, and 5th. I have attached a link to a picture of the way I have my spreadsheet arranged. Thanks again for the help.

https://drive.google.com/open?id=0B7Y9W2TFDGP9Zl84QkQzdzZ6ak0

 

[Peter_SSs]

For example if 1st, 2nd and 3rd are already qualified then i only need 5th and 6th.

In that example, wouldn't you need 4th, 5th and 6th?
For the sample data I posted previously, were my results correct, particularly noting the comment just above the screen shot about week 3?

I have attached a link to a picture of the way I have my spreadsheet arranged.

That is useful to know but I cannot copy from that and there is too much to be bothered typing out to test. Could you post the sample in this thread using one of the tools linked in my signature block below? Then I can copy/paste the sample data.

 

[henrybrent1974]

Yes you are correct and yes the code worked until it got to where if 4 of the 6 places were already qualified. Then it would give me the blank cell below the 6th place or the week# for the next week.
here the my data like you asked. Thanks again.

Book1
	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC
1
2			WEEK #1			WEEK #2			WEEK #3			WEEK #4			WEEK #5
3		1st	DAVE		1st	KANE		1st	ROBBY		1st	CALVIN		1st	ROBBY
4		2nd	DREW		2nd	RICKY D		2nd	BRENT G		2nd	STEVE T		2nd	RYAN
5		3rd	KANE		3rd	BRAD		3rd	MIKE H		3rd	KANE		3rd	CALVIN
6		4th	TRAVIS		4th	MOOSE		4th	JOSH		4th	JONATHAN		4th	MIKE B
7		5th	DAVID		5th	BRANDON		5th	CAROL		5th	MOOSE		5th	STEVE T
8		6th	RICKY L		6th	MARK		6th	DONALD		6th	ALTON		6th	JONATHAN
9
10			WEEK #6			WEEK #7			WEEK #8			WEEK #9			WEEK #10
11		1st	DAVE		1st	TROY		1st	ROBBY		1st	BRENT A		1st	DAVE
12		2nd	JASON R		2nd	RICKY D		2nd	TROY		2nd	ROBBY		2nd	TROY
13		3rd	TROY		3rd	DAVE		3rd	ALLEN		3rd	JIMMY		3rd	TOBY
14		4th	BRENT A		4th	ROBBY		4th	NICK		4th	JASON L		4th	MARK L
15		5th	DAVID		5th	CORY		5th	CURTIS JR		5th	RICKY D		5th	FRANKIE
16		6th	ROBBY		6th	MIKE B		6th	****		6th	STEVE T		6th	MOOSE
17
18			WEEK #11			WEEK #12			WEEK #13			WEEK #14			WEEK #15
19		1st	MOOSE		1st			1st			1st			1st
20		2nd	TRAVIS		2nd			2nd			2nd			2nd
21		3rd	BRENT A		3rd			3rd			3rd			3rd
22		4th	ROBBY		4th			4th			4th			4th
23		5th	FLOYD		5th			5th			5th			5th
24		6th	TROY		6th			6th			6th			6th
25
Summary

 

[Peter_SSs]

Thanks for the data (I should have asked for the expected results too. ). Anyway here is my attempt. The colours below were applied manually & for my own benefit for checking. Each week heading has a colour (I'm not sure how distinguishable they will be in the forum when I post this) and the qualifiers from that week are given the same colour below and in column AE. If a person has already qualified from a previous week I have coloured them with the colour from the week they qualified (eg Kane in Week 2 and Week 4 is yellow because he qualified in Week 1). Only 2 people qualified from Week 5 because all the others had previously qualified.

Sub Get_Qualifiers_v2()
  Dim d As Object
  Dim rA As Range
  Dim Got As Long, r As Long
  
  Const NumPerWeek As Long = 3  '<- How many to pick from each week
  Const WeeksAcross As Long = 5 '<- How many weeks across the sheet in each set of rows
  
  Set d = CreateObject("Scripting.Dictionary")
  d.CompareMode = 1
  For Each rA In Range("O2", Range("O" & Rows.Count).End(xlUp)).Resize(, 3 * WeeksAcross - 1).SpecialCells(xlConstants).Areas
    If rA.Rows.Count > 1 Then
      Got = 0
      r = 0
      Do
        r = r + 1
        If Not d.exists(rA.Cells(r, 2).Value) Then
          d(rA.Cells(r, 2).Value) = 1
          Got = Got + 1
        End If
      Loop Until Got = NumPerWeek Or r = rA.Rows.Count
    End If
  Next rA
  With Range("AE1")
    .Value = "Qualifiers"
    .Offset(1).Resize(d.Count).Value = Application.Transpose(d.keys)
    .EntireColumn.AutoFit
  End With
End Sub

Book1
	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE
1																	Qualifiers
2		WEEK #1			WEEK #2			WEEK #3			WEEK #4			WEEK #5			DAVE
3	1st	DAVE		1st	KANE		1st	ROBBY		1st	CALVIN		1st	ROBBY			DREW
4	2nd	DREW		2nd	RICKY D		2nd	BRENT G		2nd	STEVE T		2nd	RYAN			KANE
5	3rd	KANE		3rd	BRAD		3rd	MIKE H		3rd	KANE		3rd	CALVIN			RICKY D
6	4th	TRAVIS		4th	MOOSE		4th	JOSH		4th	JONATHAN		4th	MIKE B			BRAD
7	5th	DAVID		5th	BRANDON		5th	CAROL		5th	MOOSE		5th	STEVE T			MOOSE
8	6th	RICKY L		6th	MARK		6th	DONALD		6th	ALTON		6th	JONATHAN			ROBBY
9																	BRENT G
10		WEEK #6			WEEK #7			WEEK #8			WEEK #9			WEEK #10			MIKE H
11	1st	DAVE		1st	TROY		1st	ROBBY		1st	BRENT A		1st	DAVE			CALVIN
12	2nd	JASON R		2nd	RICKY D		2nd	TROY		2nd	ROBBY		2nd	TROY			STEVE T
13	3rd	TROY		3rd	DAVE		3rd	ALLEN		3rd	JIMMY		3rd	TOBY			JONATHAN
14	4th	BRENT A		4th	ROBBY		4th	NICK		4th	JASON L		4th	MARK L			RYAN
15	5th	DAVID		5th	CORY		5th	CURTIS JR		5th	RICKY D		5th	FRANKIE			MIKE B
16	6th	ROBBY		6th	MIKE B		6th	****		6th	STEVE T		6th	MOOSE			JASON R
17																	TROY
18		WEEK #11			WEEK #12			WEEK #13			WEEK #14			WEEK #15			BRENT A
19	1st	MOOSE		1st			1st			1st			1st				CORY
20	2nd	TRAVIS		2nd			2nd			2nd			2nd				ALLEN
21	3rd	BRENT A		3rd			3rd			3rd			3rd				NICK
22	4th	ROBBY		4th			4th			4th			4th				CURTIS JR
23	5th	FLOYD		5th			5th			5th			5th				JIMMY
24	6th	TROY		6th			6th			6th			6th				JASON L
25																	TOBY
26																	MARK L
27																	FRANKIE
28																	TRAVIS
29																	FLOYD
30
Qualifiers

 

[henrybrent1974]

Thanks so much for your help!!!! Works great!!!!

 

[Peter_SSs]

Thanks so much for your help!!!! Works great!!!!

That's good, thanks for letting us know.

 

[henrybrent1974]

Something I have notice that I would need to happen also. If for any week that 5th place would qualify then I would need it to take 6th place also since on a tournament bracket 5th/6th is considered a tie so if i have to take one as qualified then i need to take both. Thanks in advance for any help.