If Then Statement Help

[freddyboots]

Hi.

I think I need an if then statement but I not sure. The mini sheet attached below is how I would like it to appear. The column on the left hand side has the Player Name, Position and ADP. I would like the player names to appear in each round by Position. Each round is an increment of 10. So in Round 1 it is ADP numbers 1 to 10. Round 2 is 11 to 20, Round 3 is 21 to 30 and so on. So if you look under the Position group of RB in Round 1 you will see all the names of the players that have an ADP value between 1 and 10. The example attached below is how I would like it to appear. I am trying to get the formula to show the player names by position and by round. Any help is greatly appreciated.

Thank you in advance.

ADP Test.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	Player Name	Position	ADP
2	Christian McCaffrey	RB	1					QB
3	Dalvin Cook	RB	2						Round 1	Round 2	Round 3	Round 4
4	Derrick Henry	RB	3								Patrick Mahomes	Josh Allen
5	Alvin Kamara	RB	4
6	Saquon Barkley	RB	5
7	Travis Kelce	TE	6
8	Jonathan Taylor	RB	7
9	Ezekiel Elliott	RB	8
10	Tyreek Hill	WR	9
11	Nick Chubb	RB	10
12	Austin Ekeler	RB	11
13	Stefon Diggs	WR	12					RB
14	Davante Adams	WR	13						Round 1	Round 2	Round 3	Round 4
15	Aaron Jones	RB	14						Christian McCaffrey
16	DeAndre Hopkins	WR	15						Dalvin Cook
17	Antonio Gibson	RB	16						Derrick Henry
18	Najee Harris	RB	17						Alvin Kamara
19	Joe Mixon	RB	18						Saquon Barkley
20	Calvin Ridley	WR	19						Jonathan Taylor
21	Darren Waller	TE	20						Ezekiel Elliott
22	Justin Jefferson	WR	21						Nick Chubb
23	George Kittle	TE	22					WR
24	Patrick Mahomes	QB	23						Round 1	Round 2	Round 3	Round 4
25	DK Metcalf	WR	24						Tyreek Hill
26	A.J. Brown	WR	25
27	Clyde Edwards-Helaire	RB	26
28	Michael Thomas	WR	27
29	Keenan Allen	WR	28					TE
30	D'Andre Swift	RB	29						Round 1	Round 2	Round 3	Round 4
31	Terry McLaurin	WR	30						Travis Kelce
32	Allen Robinson	WR	31
33	Josh Allen	QB	32
34	J.K. Dobbins	RB	33
35	Miles Sanders	RB	34
36	CeeDee Lamb	WR	35
37	Amari Cooper	WR	36
38	Mike Evans	WR	37
39	Chris Carson	RB	38
40	Julio Jones	WR	39
41	David Montgomery	RB	40
ADP

 

[smozgur]

Welcome to the MrExcel Message Board!

There might be different approaches, like using the filter in VBA, maybe using only functions, maybe something else. (Actually, I believe it would be much easier with dynamic array formulas, but it requires Excel 365).

The following is my VBA approach since your question mentions "If-Then" and your sample doesn't contain any formula. Basically, I assume you expect a VBA solution.
New positions could be added into the p array in the code, and if there are more rounds, then it could be done by changing 4 in the For j = 1 to 4 loop (I don't know the game, so I don't know if positions and rounds are strictly defined or not). And I also assume that the source table could be sorted by any column.

One note - I didn't understand how you place the position tables in the result, like how many rows are blank after the previous table. So, I just used one row as the separator.

The code contains comments, but let me know if you have any questions. I hope it helps.

Sub doReport()
Dim sht As Worksheet
Dim rng As Range
Dim cll As Range
Dim pos As Variant
Dim i As Integer
Dim j As Integer
Dim ndx As Integer
Dim maxndx As Integer
Dim trg As Range

    ' Source worksheet and range assignment
    ' according to sample mini-sheet
    Set sht = ActiveSheet
    Set rng = sht.Range("A1").CurrentRegion
    Set rng = rng.Offset(1).Resize(rng.Rows.Count - 1)
  
    ' Positions array
    pos = Array("QB", "RB", "WR", "TE")

    ' Starting cell of the report
    Set trg = sht.Range("H2")
  
    ' Loop through positions
    For i = 0 To UBound(pos)
        ' Using a variable to store
        ' the max position row count
        ' Reset it here as it will be assigned
        ' after each round processing
        maxndx = 0
        ' Set the position label
        trg.Value = pos(i)
      
        ' Loop through rounds
        ' Assuming ADP might not be ordered
        ' otherwise, it could be even easier to get the rounds
        ' by selecting 10 rows for each round
        For j = 1 To 4
            ' Set the round label
            trg.Offset(1, j).Value = "Round " & j
            ' Using a row counter to place the names
            ' Reset the counter
            ndx = 0
          
            ' Loop through entire table
            For Each cll In rng.Rows
                ' If the position matches with the current pos(i) value
                ' and ADP is between the round limits then get the name
                If cll.Cells(, 2).Value = pos(i) And _
                    cll.Cells(, 3).Value > 10 * (j - 1) And _
                    cll.Cells(, 3).Value <= 10 * j Then
                  
                    ' Increment the counter
                    ' Means "go to next row in the round column"
                    ndx = ndx + 1
                  
                    ' As I said, get the name
                    trg.Offset(ndx + 1, j).Value = cll.Cells(, 1)
                  
                End If
            Next cll
            maxndx = Application.WorksheetFunction.Max(ndx, maxndx)
        Next j
      
        ' Set starting cell for the next position
        Set trg = trg.Offset(maxndx + 3)
    Next i
End Sub

Result:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Player Name	Position	ADP
2	Christian McCaffrey	RB	1					QB
3	Dalvin Cook	RB	2						Round 1	Round 2	Round 3	Round 4
4	Derrick Henry	RB	3								Patrick Mahomes	Josh Allen
5	Alvin Kamara	RB	4
6	Saquon Barkley	RB	5					RB
7	Travis Kelce	TE	6						Round 1	Round 2	Round 3	Round 4
8	Jonathan Taylor	RB	7						Christian McCaffrey	Austin Ekeler	Clyde Edwards-Helaire	J.K. Dobbins
9	Ezekiel Elliott	RB	8						Dalvin Cook	Aaron Jones	D'Andre Swift	Miles Sanders
10	Tyreek Hill	WR	9						Derrick Henry	Antonio Gibson		Chris Carson
11	Nick Chubb	RB	10						Alvin Kamara	Najee Harris		David Montgomery
12	Austin Ekeler	RB	11						Saquon Barkley	Joe Mixon
13	Stefon Diggs	WR	12						Jonathan Taylor
14	Davante Adams	WR	13						Ezekiel Elliott
15	Aaron Jones	RB	14						Nick Chubb
16	DeAndre Hopkins	WR	15
17	Antonio Gibson	RB	16					WR
18	Najee Harris	RB	17						Round 1	Round 2	Round 3	Round 4
19	Joe Mixon	RB	18						Tyreek Hill	Stefon Diggs	Justin Jefferson	Allen Robinson
20	Calvin Ridley	WR	19							Davante Adams	DK Metcalf	CeeDee Lamb
21	Darren Waller	TE	20							DeAndre Hopkins	A.J. Brown	Amari Cooper
22	Justin Jefferson	WR	21							Calvin Ridley	Michael Thomas	Mike Evans
23	George Kittle	TE	22								Keenan Allen	Julio Jones
24	Patrick Mahomes	QB	23								Terry McLaurin
25	DK Metcalf	WR	24
26	A.J. Brown	WR	25					TE
27	Clyde Edwards-Helaire	RB	26						Round 1	Round 2	Round 3	Round 4
28	Michael Thomas	WR	27						Travis Kelce	Darren Waller	George Kittle
29	Keenan Allen	WR	28
30	D'Andre Swift	RB	29
31	Terry McLaurin	WR	30
32	Allen Robinson	WR	31
33	Josh Allen	QB	32
34	J.K. Dobbins	RB	33
35	Miles Sanders	RB	34
36	CeeDee Lamb	WR	35
37	Amari Cooper	WR	36
38	Mike Evans	WR	37
39	Chris Carson	RB	38
40	Julio Jones	WR	39
41	David Montgomery	RB	40
Sheet1 (2)

 

[freddyboots]

Thank you for the reply. I am new to VBA... I copied the code and pasted into a module in the spreadsheet and saved it as a .xlsm file. When I opened the .xlsm file, nothing happens. How do I get it to move the ADP order to place them in the the position groupings by round? Also, there are 18 rounds.. so should I list it as : For j = 1 To 18 ?

 

[freddyboots]

That VBA code worked. I had to run a macro for it to work. Thank you so much !!!

 

[smozgur]

That VBA code worked. I had to run a macro for it to work. Thank you so much !!!

Sorry, I was away all day. That's correct, you need to call doReport macro in order to run it. Glad to hear you figured it out.
(You can ideally create a button on the worksheet and assign this macro, so you can run it by clicking on the button).

Yes, if my assumptions are correct then the code could be adjusted for more items as I explained:

New positions could be added into the p array in the code, and if there are more rounds, then it could be done by changing 4 in the For j = 1 to 4 loop (I don't know the game, so I don't know if positions and rounds are strictly defined or not). And I also assume that the source table could be sorted by any column.