If Then Statement Help

freddyboots

New Member
Joined
Jul 24, 2021
Messages
5
Office Version
  1. 2016
  2. 2013
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
ABCDEFGHIJKL
1Player NamePositionADP
2Christian McCaffreyRB1QB
3Dalvin CookRB2Round 1Round 2Round 3Round 4
4Derrick HenryRB3Patrick MahomesJosh Allen
5Alvin KamaraRB4
6Saquon BarkleyRB5
7Travis KelceTE6
8Jonathan TaylorRB7
9Ezekiel ElliottRB8
10Tyreek HillWR9
11Nick ChubbRB10
12Austin EkelerRB11
13Stefon DiggsWR12RB
14Davante AdamsWR13Round 1Round 2Round 3Round 4
15Aaron JonesRB14Christian McCaffrey
16DeAndre HopkinsWR15Dalvin Cook
17Antonio GibsonRB16Derrick Henry
18Najee HarrisRB17Alvin Kamara
19Joe MixonRB18Saquon Barkley
20Calvin RidleyWR19Jonathan Taylor
21Darren WallerTE20Ezekiel Elliott
22Justin JeffersonWR21Nick Chubb
23George KittleTE22WR
24Patrick MahomesQB23Round 1Round 2Round 3Round 4
25DK MetcalfWR24Tyreek Hill
26A.J. BrownWR25
27Clyde Edwards-HelaireRB26
28Michael ThomasWR27
29Keenan AllenWR28TE
30D'Andre SwiftRB29Round 1Round 2Round 3Round 4
31Terry McLaurinWR30Travis Kelce
32Allen RobinsonWR31
33Josh AllenQB32
34J.K. DobbinsRB33
35Miles SandersRB34
36CeeDee LambWR35
37Amari CooperWR36
38Mike EvansWR37
39Chris CarsonRB38
40Julio JonesWR39
41David MontgomeryRB40
ADP
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
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.

VBA Code:
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
ABCDEFGHIJKL
1Player NamePositionADP
2Christian McCaffreyRB1QB
3Dalvin CookRB2Round 1Round 2Round 3Round 4
4Derrick HenryRB3Patrick MahomesJosh Allen
5Alvin KamaraRB4
6Saquon BarkleyRB5RB
7Travis KelceTE6Round 1Round 2Round 3Round 4
8Jonathan TaylorRB7Christian McCaffreyAustin EkelerClyde Edwards-HelaireJ.K. Dobbins
9Ezekiel ElliottRB8Dalvin CookAaron JonesD'Andre SwiftMiles Sanders
10Tyreek HillWR9Derrick HenryAntonio GibsonChris Carson
11Nick ChubbRB10Alvin KamaraNajee HarrisDavid Montgomery
12Austin EkelerRB11Saquon BarkleyJoe Mixon
13Stefon DiggsWR12Jonathan Taylor
14Davante AdamsWR13Ezekiel Elliott
15Aaron JonesRB14Nick Chubb
16DeAndre HopkinsWR15
17Antonio GibsonRB16WR
18Najee HarrisRB17Round 1Round 2Round 3Round 4
19Joe MixonRB18Tyreek HillStefon DiggsJustin JeffersonAllen Robinson
20Calvin RidleyWR19Davante AdamsDK MetcalfCeeDee Lamb
21Darren WallerTE20DeAndre HopkinsA.J. BrownAmari Cooper
22Justin JeffersonWR21Calvin RidleyMichael ThomasMike Evans
23George KittleTE22Keenan AllenJulio Jones
24Patrick MahomesQB23Terry McLaurin
25DK MetcalfWR24
26A.J. BrownWR25TE
27Clyde Edwards-HelaireRB26Round 1Round 2Round 3Round 4
28Michael ThomasWR27Travis KelceDarren WallerGeorge Kittle
29Keenan AllenWR28
30D'Andre SwiftRB29
31Terry McLaurinWR30
32Allen RobinsonWR31
33Josh AllenQB32
34J.K. DobbinsRB33
35Miles SandersRB34
36CeeDee LambWR35
37Amari CooperWR36
38Mike EvansWR37
39Chris CarsonRB38
40Julio JonesWR39
41David MontgomeryRB40
Sheet1 (2)
 
Upvote 0
Solution
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 ?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top