Vlookup(?) to create unique list...HELP!

bsweet0us

New Member
Joined
Apr 12, 2008
Messages
38
Office Version
  1. 365
Platform
  1. Windows
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!
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Forum statistics

Threads
1,224,802
Messages
6,181,048
Members
453,014
Latest member
Chris258

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