Posted this question in a different thread, but titled it poorly, so here goes a second (hopefully more successful) time.
The purpose of the workbook is to simulate the NFL draft. Sheet 2 lists various mock drafts with the team names across row 1 and names down each column. An example:
...............A................................B
(Row 1) Miami.......................St. Louis
(Row 2) Chris Long.................Jake Long
(Row 3) Chris Long.................Jake Long
(Row 4) Jake Long..................Chris Long
(Row 5) Vernon Gholston.........Chris Long
(Row 6) Matt Ryan.................Chris Long
Sheet 3 has uniques lists in "even" columns (B, D, F, etc.) created by obtaining unique lists using an advanced fileter. "Odd" columns (A, C, E, etc.) use a COUNTIF formula to determine the number of times each unique entry appears in the list from sheet 2. From the example above, sheet 3 would contain:
............A...............B.......................C.......................D
(Row 1) 2..........Chris Long.................2.................Jake Long
(Row 2) 1..........Jake Long..................3..................Chris Long
(Row 3) 1..........Vernon Gholston..........0.......................0
(Row 4) 1...........Matt Ryan.................0........................0
Here is the formula I currently have in cell B1 on sheet 1 to get the player with the most "votes" from column A on sheet 3 and return the corresponding value from column B:
=VLOOKUP(MAX(Sheet3!A2:A50),Sheet3!A2:B50,2,FALSE)
The formula places "Chris Long" in cell A1 of sheet 1 per the formula.
The remainder of my list is where I'm stuck. I need to populate 30 more cells with values obtained from sheet 3 that do not duplicate any of the cells previously populated.
Using the same formula from cell A2 in A3 (but changing the columns from A & B to C & D) would put Chris Long in cell A3. However, since he is already in cell A2, I need the formula to recongize this and select the next highest value from column C and return the matching value in column D.
To (possibly) further compund the problem, there is a good chance the highest value in an "odd" cell will correspond to more than one value in its corresponding "even" cell. I need the formula to recognize that and not change the LOOKUP value. (For example, if column E on sheet3 has 4 for Chris Long and 4 for Matt Ryan, I need the formula to realize Chris Long has already been used but Matt Ryan hasn't, thus returning the value "Matt Ryan.")
This process would need to be replicated throughout cells A4 - A32, with each returned value only being used once.
It is not necessary a formula is used as I have a button with a macro already, so if I could add to that VB script that would be great. That being said, I know NOTHING anout VB, so that would require a walkthrough or a copy/paste.
THANKS IN ADVANCE! LOOK FORWARD TO TRYING YOUR SUGGESTIONS!
The purpose of the workbook is to simulate the NFL draft. Sheet 2 lists various mock drafts with the team names across row 1 and names down each column. An example:
...............A................................B
(Row 1) Miami.......................St. Louis
(Row 2) Chris Long.................Jake Long
(Row 3) Chris Long.................Jake Long
(Row 4) Jake Long..................Chris Long
(Row 5) Vernon Gholston.........Chris Long
(Row 6) Matt Ryan.................Chris Long
Sheet 3 has uniques lists in "even" columns (B, D, F, etc.) created by obtaining unique lists using an advanced fileter. "Odd" columns (A, C, E, etc.) use a COUNTIF formula to determine the number of times each unique entry appears in the list from sheet 2. From the example above, sheet 3 would contain:
............A...............B.......................C.......................D
(Row 1) 2..........Chris Long.................2.................Jake Long
(Row 2) 1..........Jake Long..................3..................Chris Long
(Row 3) 1..........Vernon Gholston..........0.......................0
(Row 4) 1...........Matt Ryan.................0........................0
Here is the formula I currently have in cell B1 on sheet 1 to get the player with the most "votes" from column A on sheet 3 and return the corresponding value from column B:
=VLOOKUP(MAX(Sheet3!A2:A50),Sheet3!A2:B50,2,FALSE)
The formula places "Chris Long" in cell A1 of sheet 1 per the formula.
The remainder of my list is where I'm stuck. I need to populate 30 more cells with values obtained from sheet 3 that do not duplicate any of the cells previously populated.
Using the same formula from cell A2 in A3 (but changing the columns from A & B to C & D) would put Chris Long in cell A3. However, since he is already in cell A2, I need the formula to recongize this and select the next highest value from column C and return the matching value in column D.
To (possibly) further compund the problem, there is a good chance the highest value in an "odd" cell will correspond to more than one value in its corresponding "even" cell. I need the formula to recognize that and not change the LOOKUP value. (For example, if column E on sheet3 has 4 for Chris Long and 4 for Matt Ryan, I need the formula to realize Chris Long has already been used but Matt Ryan hasn't, thus returning the value "Matt Ryan.")
This process would need to be replicated throughout cells A4 - A32, with each returned value only being used once.
It is not necessary a formula is used as I have a button with a macro already, so if I could add to that VB script that would be great. That being said, I know NOTHING anout VB, so that would require a walkthrough or a copy/paste.
THANKS IN ADVANCE! LOOK FORWARD TO TRYING YOUR SUGGESTIONS!