Vlookup, counitf, other problem

**g.ralph.rogers** · 02-14-2012, 12:56 PM

Attached is a workbook I am building to seed wrestlers in local tournaments. Currently I am running into an issue looking up who a wrestler has beaten or lost to on their personal sheet(ie. 'Wrestler A' columns B and E) and comparing it to the 'Home Sheet'!A2:100 list of wrestlers in attendance. on 'Home Sheet' columns F and G I need to return like I have in F2 for each wrestler both wins over and lost to wrestlers.

The end result is that I want to assign points based on this field and add it to column O so that I can calculate it into the seeding field.

**mrice** · 02-14-2012, 01:13 PM

Try this user defined function.

Please Login or Register  to view this content.

Open the VBA editor (Alt F11). Insert a new module (insert - module) and paste in the above. Go back to sheet (Alt F11) and type =GetWins(A2) into F2 and copy down.

This relies on a strict naming convention and column placement for your individual sheets.

**NBVC** · 02-14-2012, 01:15 PM

If you add a UserDefined (ALT+F11, INSERT|Module and paste) function like:

Please Login or Register  to view this content.

then use formula:

=SUBSTITUTE(TRIM(aconcat(IF('Wrestler A'!$E$2:$E$12="W",'Wrestler A'!$B$2:$B$12,"")," "))," ",",")

confirmed with CTRL+SHIFT+ENTER not just ENTER and copy down.

same in Losses column, just change "W" to "L"

You can use INDIRECT to copy down and get to appropriate sheet based on column A having same name as sheet name.

e.g.

=SUBSTITUTE(TRIM(aconcat(IF(INDIRECT("'"&$A2&"'!$E$2:$E$12")="W",INDIRECT("'"&$A2&"'!$B$2:$B$12"),"")," "))," ",",")

**g.ralph.rogers** · 02-14-2012, 02:28 PM

Originally Posted by mrice

Try this user defined function.

Please Login or Register  to view this content.

Open the VBA editor (Alt F11). Insert a new module (insert - module) and paste in the above. Go back to sheet (Alt F11) and type =GetWins(A2) into F2 and copy down.

This relies on a strict naming convention and column placement for your individual sheets.

KINDA WORKS. ADDITIONS.

Part 1: Small change. (see 'Home Sheet'!G3 which should read "A,D,L,B,P" only) So, the beaten wrestler also has to have been entered on 'Home Sheet'!A2:17. If they are not, then they are not to be entered into column G since the match had no bearing on this specific group of wrestlers.

Part 2: I have to now apply the DATE column for Wrestler A,B,C,etc. If Wrestler A wrestled Wrestler B two times and won one and lost one within two weeks of eachother then it cannot show up on the list. I call that a wash and it does not calculate into the seeding of the wrestler.

Also, there will be 16 Wrestler sheets (A through P) total

Any idea.

**mrice** · 02-14-2012, 04:26 PM

Elaboration of the function to meet the first question

Please Login or Register  to view this content.

The second one is more tricky, what about cases where there have been three bouts within the two week period or does this never happen?

**g.ralph.rogers** · 02-16-2012, 12:44 PM

very very rare for three to happen in two weeks. four can never happen. So if it was 3 in two weeks then the majority wins the seed. So the wrestler with 2 wins should be given the priority or points or however we do that.