+ Reply to Thread
Results 1 to 58 of 58

Lookup multiple occurrences of the same value in two different columns

  1. #1
    Registered User
    Join Date
    03-27-2018
    Location
    Manchester, England
    MS-Off Ver
    2016
    Posts
    76

    Question Lookup multiple occurrences of the same value in two different columns

    Hi, I posted here yesterday so apologies for the two posts in two days - I am trying to do this independently I am just struggling with the really big formulas.

    The attached workbook explains the problem however here is a brief overview:

    I'm trying to return a number value from one sheet to another.
    This issue lies as I am trying to return these by names on one sheet into the correct location on the next sheet. However one persons name can appear multiple times and I am struggling to get the last value each time.

    The spreadsheet explains the problem better as its much more visual.

    I hope someone can help and thanks in advance for any time this may take.

    Matthew
    Attached Files Attached Files
    Last edited by 6StringJazzer; 03-28-2018 at 09:39 AM. Reason: clarified title

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Lookup multiple occurrences of the same value in two different columns

    What are the rules for the change in ELO? It seems to be that ELO is increased by 100 if the name appears in column A of the Match sheet, and decreases by 20 if the name is in column C - is this correct?

    Pete

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,381

    Re: Lookup multiple occurrences of the same value in two different columns

    I think you have a fundamental problem here, unless I'm misinterpreting the workbook, and that is that the old ELO numbers, apart from the one immediately preceding the current one, are not recorded, so without VBA, you are not going to be able to do what you want. Well, not unless you record all the changes somewhere, that is.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  4. #4
    Registered User
    Join Date
    03-27-2018
    Location
    Manchester, England
    MS-Off Ver
    2016
    Posts
    76

    Re: Lookup multiple occurrences of the same value in two different columns

    The formula is fairly complex mathematically and uses odds (formula 1) to deduce who is most likely to win based on previous ranks and then has another formula to generate the outcome (formula 2). I just entered simple numbers for the purpose of the workbook as my understanding is that as the formulas are complicated, it can only be achieved through a lookup.

    formula 1 - odds
    =IFERROR(1/(1+10^((M8-L8)/400)),"")

    formula 2 - new ranks
    =IFERROR(L8+96*(1-N8),"")

    Thank you 6stringjazzer for refining the title as I was unsure what type of problem this was.

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,381

    Re: Lookup multiple occurrences of the same value in two different columns

    Did you see my query?

  6. #6
    Registered User
    Join Date
    03-27-2018
    Location
    Manchester, England
    MS-Off Ver
    2016
    Posts
    76

    Re: Lookup multiple occurrences of the same value in two different columns

    Thanks for the feedback. I'm not 100% sure I understand the issue. The point of the ELO sheet is to record all of the changes for each person in the next cell to the right. And in the ELO every person has a base start of 1000 that is represented in ELO 1. After they have played I'm trying to get the updated ELO to be recorded next to the original.
    Sorry if you already understood that and I misinterpreted your reply, I'm just trying to provide as much information as possible.
    Is there a way around this problem? I'm happy to make extra sheets if needed.



    Edit: Yes thanks, I was just typing my response

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,381

    Re: Lookup multiple occurrences of the same value in two different columns

    Yes, I see where you are coming from, but you are trying to populate the ELO table from the other sheet, aren't you? And on the other sheet, the only ELO data present is the current ELO rating and the one immediately before it. Any previous ratings have been overwritten by these new figures, haven't they? So you can't do what you want from the data you have - you need to have a record of ALL changes to the ELO status somewhere (could be hidden) to be able to do this. They need recording. Both the ELO and the other sheet could then read from the normalised data. I suspect this may make no sense at all to you - but hopefully it will!

    Or am I missing something fundamental? Wouldn't be the first time!!!

  8. #8
    Registered User
    Join Date
    03-27-2018
    Location
    Manchester, England
    MS-Off Ver
    2016
    Posts
    76

    Re: Lookup multiple occurrences of the same value in two different columns

    I got the ELO idea from a different spreadsheet however the spreadsheet is for a different purpose and is locked so I cant edit it to suit my scenario.
    Due to it being locked I cant figure out how this person has overcome this obstacle.

    Due to file size I cant upload the original either - As I have just tried. I have found a link to the original spreadsheet however I can't post it due to not meeting the required number of posts. It also has a youtube tutorial that explained how to use but didnt explain how this had been achieved.

    Sorry about this
    Last edited by Hinchliffe2012; 03-28-2018 at 10:47 AM.

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,381

    Re: Lookup multiple occurrences of the same value in two different columns

    Yes, I think I have missed something - hang on, I'll have another look.

  10. #10
    Registered User
    Join Date
    03-27-2018
    Location
    Manchester, England
    MS-Off Ver
    2016
    Posts
    76

    Re: Lookup multiple occurrences of the same value in two different columns

    Thank you so much for this

  11. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,381

    Re: Lookup multiple occurrences of the same value in two different columns

    This can be done very easily with a helper column - is this OK? It could be hidden.

  12. #12
    Registered User
    Join Date
    03-27-2018
    Location
    Manchester, England
    MS-Off Ver
    2016
    Posts
    76

    Re: Lookup multiple occurrences of the same value in two different columns

    Yes definitely! Thanks

  13. #13
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,381

    Re: Lookup multiple occurrences of the same value in two different columns

    Mmmm ... Hit a stumbling block. Will keep thinking. Maybe someone else following this will have an idea.

  14. #14
    Registered User
    Join Date
    03-27-2018
    Location
    Manchester, England
    MS-Off Ver
    2016
    Posts
    76

    Re: Lookup multiple occurrences of the same value in two different columns

    Thanks for trying. Is there an area I could look into that you think could be of assistance?

  15. #15
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,381

    Re: Lookup multiple occurrences of the same value in two different columns

    Right. Here we go. Match sheet:

    H3: =COUNTIF($A$3:$C3,A3) - copy down
    I3: =COUNTIF($A$3:$C3,C3) - copy down

    ELO sheet:

    B2: =IFNA(INDEX(Match!$D$3:$D$17,MATCH(ELO!A2,Match!$A$3:$A$17,0)),INDEX(Match!$E$3:$E$17,MATCH(ELO!A2,Match!$C$3:$C$17,0))) - copy down

    B3: =SUMPRODUCT((((Match!$H$3:$H$17=COLUMNS($A:A))*(Match!$A$3:$A$17=$A2)*Match!$F$3:$F$17)+((Match!$I$3:$I$17=COLUMNS($A:A))*(Match!$C$3:$C$17=$A2)*Match!$G$3:$G$17))) - copy across and down

    You can conditionally format the table range to hide 0s if you wish.

    You can also extend the ranges to suit the real data. Maybe tables would help, then you could use column references which would grow automatically as the match data grows.
    Attached Files Attached Files
    Last edited by AliGW; 03-28-2018 at 12:10 PM.

  16. #16
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Lookup multiple occurrences of the same value in two different columns

    I inserted a new column D in the Match sheet, and used this as a helper column with this formula in D3:

    =IF(OR(A3="",C3=""),"","_"&A3&"_"&COUNTIF(A$3:A3,A3)+COUNTIF(C$3:C3,A3)&"-"&C3&"_"&COUNTIF(A$3:A3,C3)+COUNTIF(C$3:C3,C3)&"_")

    Copy this down as far as you need to in order to accommodate more matches being added - I've copied to row 30 in the attached file.

    Then in C2 of the ELO sheet I used this formula to make adjustments to the previous ELO value, depending on the result:

    =IF(COUNTIF(Match!$D:$D,"_"&$A2&"_"&COLUMNS($C:C)&"-*"),B2+100,IF(COUNTIF(Match!$D:$D,"*-"&$A2&"_"&COLUMNS($C:C)&"_"),B2-20,""))

    Here you can see (in red) that I've just made a simple adjustment of +100 for a win or -20 for a loss, as previously discussed, so you will need to put your actual adjustment formulae in here to suit. You can copy this formula across and down, as required, to give you the appropriate ELO values dependent on the number of matches that each player has had.

    I'm not sure what you want to show on the Match sheet as columns E and F (as now are) should in my mind show the ELO value when a particular match starts, rather than just the arbitrary starting values at the beginning of the tournament (i.e. for Adam's 2nd match, his ELO should be 1100, going down to 1080, and for his 3rd match it should be 1080 going up to 1180, in my opinion).

    Anyway, hope this helps.

    Pete
    Attached Files Attached Files

  17. #17
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,381

    Re: Lookup multiple occurrences of the same value in two different columns

    Two completely different approaches to choose from - lucky boy! Take your pick.

  18. #18
    Registered User
    Join Date
    03-27-2018
    Location
    Manchester, England
    MS-Off Ver
    2016
    Posts
    76

    Re: Lookup multiple occurrences of the same value in two different columns

    Oh wow thank you so much. I am going out in 10 minutes so I shall attempt tonight or tomorrow morning. Thanks so much for your assistance.

    I did notice that in ELO 1 there was a formula however I currently have a simple formula in that column to generate the start rank of 1500. Should I delete mine? Ive put it below.

    =IF(A2="","",1500)


    Both of the suggestions are really really complex (to me at least) so thank you as I don't think I would have ever figured this out individually.
    Last edited by Hinchliffe2012; 03-28-2018 at 12:22 PM.

  19. #19
    Registered User
    Join Date
    03-27-2018
    Location
    Manchester, England
    MS-Off Ver
    2016
    Posts
    76

    Re: Lookup multiple occurrences of the same value in two different columns

    Thank you Pete as well. I will look into these both later. Really appreciate it - thanks

    It is the starting ranks before the match. Sorry - in my example workbook I put the wrong numbers, you are right that it follows down.
    Last edited by Hinchliffe2012; 03-28-2018 at 12:17 PM.

  20. #20
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,381

    Re: Lookup multiple occurrences of the same value in two different columns

    Let us know how you get on.

  21. #21
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Lookup multiple occurrences of the same value in two different columns

    Quote Originally Posted by Hinchliffe2012 View Post
    ... It is the starting ranks before the match ...
    Okay, I've extended the table in the ELO sheet, because if you have 15 players who play each other then you will have 14 matches each, and thus 15 different ELOs including the starting value.

    I've put these formulae in the cells stated on the Match sheet:

    E3: =IF(D3="","",INDEX(ELO!$B$2:$P$15,MATCH($A3,ELO!$A$2:$A$15,0),SUBSTITUTE(LEFT(D3,FIND("-",D3)-1),"_"&A3&"_","")))

    F3: =IF(D3="","",INDEX(ELO!$B$2:$P$15,MATCH($C3,ELO!$A$2:$A$15,0),SUBSTITUTE(SUBSTITUTE(MID(D3,FIND("-",D3)+1,255),"_",""),C3,"")))

    G3: =IF(D3="","",INDEX(ELO!$B$2:$P$15,MATCH($A3,ELO!$A$2:$A$15,0),1+SUBSTITUTE(LEFT(D3,FIND("-",D3)-1),"_"&A3&"_","")))

    H3: =IF(D3="","",INDEX(ELO!$B$2:$P$15,MATCH($C3,ELO!$A$2:$A$15,0),1+SUBSTITUTE(SUBSTITUTE(MID(D3,FIND("-",D3)+1,255),"_",""),C3,"")))

    Note the similarity between the 3rd and 1st of these formulae and between the 4th and 2nd - they only differ by 1+, as shown in red. These can be copied down to return the appropriate ELO values for each player.

    I've also added a bit of conditional formatting based on players' names, so that you can see more easily how the ELO values vary with time.

    Hope this helps.

    Pete
    Attached Files Attached Files

  22. #22
    Registered User
    Join Date
    03-27-2018
    Location
    Manchester, England
    MS-Off Ver
    2016
    Posts
    76

    Re: Lookup multiple occurrences of the same value in two different columns

    Hi Pete, thanks for helping.

    I did this prior to checking your most recent reply. However, this is my current sheet complete with all the formulas from the actual document.

    I keep getting circular errors currently.

    I havent checked your reply yet but I will read it and try it out after posting this.

    Thanks for the assistance.

    Edit: I have gone over your most recent sheet, however I don't understand how to include my calculation that replaces the +100 and -20 due to it requiring two separate columns (see my attached sheet) that arent accounted for in this and I'm uncertain how I include them. Hope this makes some sense.
    Attached Files Attached Files
    Last edited by Hinchliffe2012; 03-28-2018 at 06:02 PM.

  23. #23
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Lookup multiple occurrences of the same value in two different columns

    I've cleared up the circular reference errors that you had in a copy that I am working on, but I think that your adjustment formula is incorrect. Instead of my simplistic B2+100 and B2-20, you have B2+96*(1-Match!$G3) and B2+96*(0-Match!$H3). If the formula is copied across, then Adam's ELO values will always be adjusted by the odds on row 3, whereas it should be adjusted by the odds on the appropriate row for the second, third match etc. Also, it will give wrong results when it is copied down, as the 3 will change sequentially to 4, 5, 6 etc., even if the player does not appear on that row, and I think that might give rise to some circular ref errors even in my current version (I've turned that error reporting off for now).

    I need to come up with a way of determining the appropriate row where the odds should be returned from, but it's getting a bit late here, so I'll pick it up tomorrow sometime. In the meantime, here's the file I have been working on so you can see how it is fitting together (I've also simplified the CF formulae in it).

    Hope this helps.

    Pete
    Attached Files Attached Files

  24. #24
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Lookup multiple occurrences of the same value in two different columns

    I've amended the formula in C2 of the ELO sheet to this:

    =IF(COUNTIF(Match!$D:$D,"_"&$A2&"_"&COLUMNS($C:C)&"-*"),B2+96*(1-INDEX(Match!$G:$G,MATCH("_"&$A2&"_"&COLUMNS($C:C)&"-*",Match!$D:$D,0))),IF(COUNTIF(Match!$D:$D,"*-"&$A2&"_"&COLUMNS($C:C)&"_"),B2+96*(0-INDEX(Match!$H:$H,MATCH("*-"&$A2&"_"&COLUMNS($C:C)&"_",Match!$D:$D,0))),""))

    and this can be copied across and down as required.

    The bolded sections relates to the B2+96*(1-Match!$G3) and B2+96*(0-Match!$H3) terms that you had before, but the coloured sections show the replacement for the simple G3 and H3 cells in the Match sheet. Essentially, for Adam's second game it finds where Adam_2 occurs in column D of the Match sheet and then uses the odds value in that row of either column G or H depending on whether the game was won or lost. Similarly for Adam's 3rd game, and so on.

    This seems to work, but of course I have no idea what this is about - what game is being played, and what does ELO stand for?

    Hope this helps.

    Pete
    Attached Files Attached Files

  25. #25
    Registered User
    Join Date
    03-27-2018
    Location
    Manchester, England
    MS-Off Ver
    2016
    Posts
    76

    Re: Lookup multiple occurrences of the same value in two different columns

    Thanks! You really have gone above and beyond here.

    ELO is simply a rating system that is used in chess. It starts from a base level (can be anything) and it accounts for both peoples ELO and the odds (based on their original ELOs) to determine who should win. If the person with the highest original ELO wins he will increase by a small amount and the loser will decrease by a similarly small amount as he was expected to lose. If a person with an ELO of 1000 beats someone with 2000 then the person with 1000 would increase hugely as their odds of victory would be really small. The person with 2000 would decrease significantly. Ive linked he wikipedia entry of the system and a youtube video that explained it to me - I have slightly changed the formula.
    I work at a wrestling club and I am working with a few other semi local clubs. The people would be the members of the club and the competition is wrestling. Obviously, after a match I could manually enter their newest ELOs however it is to be used by all coaches and other than me - who is also completely out of my depth at this stage of the spreadsheet but it is easy past this point I feel - have no experience using Excel. Another reason I am doing this is to improve my own excel skills as I am doing a business masters and excel is likely to be fundamental when I graduate.

    https://en.wikipedia.org/wiki/Elo_rating_system

    https://www.youtube.com/watch?v=1_xLjI6zN3I

    Last edited by Hinchliffe2012; 03-29-2018 at 06:32 AM.

  26. #26
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Lookup multiple occurrences of the same value in two different columns

    Thanks for the explanation, and I'm happy to help.

    If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post and mark this thread as SOLVED.

    Pete

  27. #27
    Registered User
    Join Date
    03-27-2018
    Location
    Manchester, England
    MS-Off Ver
    2016
    Posts
    76

    Re: Lookup multiple occurrences of the same value in two different columns

    I will do, I just have some errands to do. But thanks for the help.

    And I will leave recommendations too.

    Matt

  28. #28
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,381

    Re: Lookup multiple occurrences of the same value in two different columns

    I thought it was a rock band fronted by Jeff Lynn.

  29. #29
    Registered User
    Join Date
    03-27-2018
    Location
    Manchester, England
    MS-Off Ver
    2016
    Posts
    76

    Re: Lookup multiple occurrences of the same value in two different columns

    Hi, I have turned all of Petes formulas into my own in a document. I have then added them to my sheet. However I only get Numbers for Old ELO 2 (Not ELO 1) and after entering 5 matches I start getting #VALUE even in ELO 2 column.

    Also, on my ELO sheet I am gettin #VALUE errors. However if the person was in the first match column, there data is starting from where ELO 3 should be - meaning ELO 2 (The first non base rank) is skipped.

    Ive attached my notebook to show this.
    Attached Files Attached Files

  30. #30
    Registered User
    Join Date
    03-27-2018
    Location
    Manchester, England
    MS-Off Ver
    2016
    Posts
    76

    Re: Lookup multiple occurrences of the same value in two different columns

    Pete:
    Old ELO for P1
    =IF(D3="","",INDEX(ELO!$B$2:$P$15,MATCH($A3,ELO!$A$2:$A$15,0),SUBSTITUTE(LEFT(D3,FIND("-",D3)-1),"_"&A3&"_","")))
    Old ELO for P2
    =IF(D3="","",INDEX(ELO!$B$2:$P$15,MATCH($C3,ELO!$A$2:$A$15,0),SUBSTITUTE(SUBSTITUTE(MID(D3,FIND("-",D3)+1,255),"_",""),C3,"")))
    New ELO for P1
    =IF(D3="","",INDEX(ELO!$B$2:$P$15,MATCH($A3,ELO!$A$2:$A$15,0),1+SUBSTITUTE(LEFT(D3,FIND("-",D3)-1),"_"&A3&"_","")))
    New ELO for P2
    =IF(D3="","",INDEX(ELO!$B$2:$P$15,MATCH($C3,ELO!$A$2:$A$15,0),1+SUBSTITUTE(SUBSTITUTE(MID(D3,FIND("-",D3)+1,255),"_",""),C3,"")))
    ELO 2->
    =IF(COUNTIF(Match!$D:$D,"_"&$A2&"_"&COLUMNS($C:C)&"-*"),B2+96*(1-INDEX(Match!$G:$G,MATCH("_"&$A2&"_"&COLUMNS($C:C)&"-*",Match!$D:$D,0))),IF(COUNTIF(Match!$D:$D,"*-"&$A2&"_"&COLUMNS($C:C)&"_"),B2+96*(0-INDEX(Match!$H:$H,MATCH("*-"&$A2&"_"&COLUMNS($C:C)&"_",Match!$D:$D,0))),""))
    Help Sheet
    =IF(OR(A3="",C3=""),"","_"&A3&"_"&COUNTIF(A$3:A3,A3)+COUNTIF(C$3:C3,A3)&"-"&C3&"_"&COUNTIF(A$3:A3,C3)+COUNTIF(C$3:C3,C3)&"_")

    Me:
    Old ELO for P1
    =IF(Fightcards!W6="","",INDEX(ELO!$B$2:$AY$176,MATCH(Fightcards!$E6,ELO!$A$2:$A$176,0),SUBSTITUTE(LEFT(Fightcards!W6,FIND("-",Fightcards!W6)-1),"_"&Fightcards!E6&"_","")))
    Old ELO for P2
    =IF(Fightcards!W6="","",INDEX(ELO!$B$2:$AY$176,MATCH(Fightcards!$G6,ELO!$A$2:$A$176,0),SUBSTITUTE(SUBSTITUTE(MID(Fightcards!W6,FIND("-",Fightcards!W6)+1,255),"_",""),Fightcards!G6,"")))
    New ELO for P1
    =IF(Fightcards!W6="","",INDEX(ELO!$B$2:$AY$176,MATCH(Fightcards!$E6,ELO!$A$2:$A$176,0),1+SUBSTITUTE(LEFT(Fightcards!W6,FIND("-",Fightcards!W6)-1),"_"&Fightcards!E6&"_","")))
    New ELO for P2
    =IF(Fightcards!W6="","",INDEX(ELO!$B$2:$AY$176,MATCH(Fightcards!$G6,ELO!$A$2:$A$176,0),1+SUBSTITUTE(SUBSTITUTE(MID(Fightcards!W6,FIND("-",Fightcards!W6)+1,255),"_",""),Fightcards!G6,"")))
    ELO 2--->
    =IF(COUNTIF(Fightcards!$W:$W,"_"&ELO!$A2&"_"&COLUMNS(ELO!$C:C)&"-*"),ELO!B2+96*(1-INDEX(Match!$O:$O,MATCH("_"&$ELO!A2&"_"&COLUMNS(ELO!$C:C)&"-*",Fightcards!$W:$W,0))),IF(COUNTIF(Fightcards!$W:$W,"*-"&ELO!$A2&"_"&COLUMNS(ELO!$C:C)&"_"),ELO!B2+96*(0-INDEX(Fightcards!$P:$P,MATCH("*-"&ELO!$A2&"_"&COLUMNS(ELO!$C:C)&"_",Fightcards!$W:$W,0))),""))
    Help SHeet
    =IF(OR(FightcardsE6="",Fightcards!G6=""),"","_"&Fightcards!E6&"_"&COUNTIF(Fightcards!E$6:Fightcards!E6,Fightcards!E6)+COUNTIF(Fightcards!G$6:Fightcards!G6,Fightcards!G6)&"-"&Fightcards!G6&"_"&COUNTIF(Fightcards!E$6:Fightcards!E6,G6)+COUNTIF(Fightcards!G$6:Fightcards!G6,Fightcards!G6)&"_")

  31. #31
    Registered User
    Join Date
    03-27-2018
    Location
    Manchester, England
    MS-Off Ver
    2016
    Posts
    76

    Re: Lookup multiple occurrences of the same value in two different columns

    Apologies, my help sheet formula was slightly wrong and that threw everything. Ive edited that now and I think this is solved. Just going to check over everything a couple times.
    Attached Files Attached Files

  32. #32
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Lookup multiple occurrences of the same value in two different columns

    Something else you might want to consider is producing a "league" table of the rankings at any point in time, using each player's latest ELO value. I've shown how you can do this in the shaded columns of the ELO sheet in this latest file.

    You might like to consider using the number of games as a tie-break in case you have two or more ELOs that are the same. I've not bothered to do that as the ELO is actually a fractional value, so it is unlikely that any two will be exactly the same (except at the start of a tournament).

    Hope you have now got it all sorted out.

    Pete
    Attached Files Attached Files

  33. #33
    Registered User
    Join Date
    03-27-2018
    Location
    Manchester, England
    MS-Off Ver
    2016
    Posts
    76

    Re: Lookup multiple occurrences of the same value in two different columns

    Oh brilliant, I was going to try implement that so thanks!

    Just one super tiny problem...

    If a person with a double barrelled surname loses, then its fine and all works perfectly. If a person with a double barrelled surname wins it puts #VALUE everywhere.
    Ive tried to spot a difference in the W:W help sheet (D:D on yours) but have been unsuccessful. I read online it's to do with the " " however they seemed to match up.
    This is only a small problem so if a fix is very complicated then its fine as I can just edit their names in my base sheet - but for authenticity reasons (and because I just want to get better on Excel) I thought it best to see if a solution is feasible.

    Thanks

    Matt
    Attached Files Attached Files
    Last edited by Hinchliffe2012; 03-29-2018 at 02:01 PM.

  34. #34
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Lookup multiple occurrences of the same value in two different columns

    Hi Matt,

    The problem is that I have also used a hyphen in the helper formula in column D to split the two names, and other formulae detect this to determine if the name is on the left or right of the hyphen. If you have a hyphen in the name, then it would be better to use a different character in my formulae to avoid the problems, e.g. the pipe character "|", and the best approach will be to use Find & Replace (CTRL-H) rather than to adjust each individual formula and then copy it down again.

    In the attached file I first selected the range D3 to J17 in the Match sheet and then did CTRL-H and:

    Find what: "-"
    Replace with: "|"
    Then click on Replace All.

    It is important to include the double-Quotes, so that the minus symbols used in some formulae do not get changed.

    Then in the ELO sheet I selected the range C2:P15 and did CTRL-H again, with:

    Find what: "-
    Replace with: "|
    Replace all

    With the same range selected you need to repeat this with:

    Find what: -"
    Replace with: |"
    Replace All

    and that should rectify the situation. I did notice that with this file you do get an error with the sorted list, and this is because there does happen to be two identical ELO values (which are ranked as 12=, and so there is no 13th element). You can overcome this by changing the formula in S2 to this:

    =RANK(R2,$R$2:$R$15)+COUNTIF(R$2:R2,R2)-1

    and copying this down. This gives you a tie-break for duplicate ELO values, but does not include any adjustment for number of games played which I suggested earlier.

    Hope this helps.

    Pete
    Attached Files Attached Files

  35. #35
    Registered User
    Join Date
    03-27-2018
    Location
    Manchester, England
    MS-Off Ver
    2016
    Posts
    76

    Re: Lookup multiple occurrences of the same value in two different columns

    Thanks!

    I changed the '-' as suggested and it works perfectly now.

    Was wondering if I could get your opinion on a couple features I was wanting to include.

    I'm wanting to be able to count how many wins-losses-draws a person has. My plan was to use the countif name is in A3 of Match! column for wins. However in my sheet, where it says def. (B:B i think) Its actually a dropdown box where I can choose 'draw.'. My method above would still count this as a win. I'm not asking you to solve this I'm just wondering what direction to go in and if this is achievable?

    Secondly, to highlight strengths and weaknesses in peoples game, I want to be able to breakdown their wins as 'submission','technical ko' and 'decision'. In my card sheet there is a dropdown that allows you to choose the method of victory. It has about 20 different types of submission and same for technical ko so its quite extensive. Would it be possible to be able to calculate this or should I give up on this? The rest of the spreadsheet is not ready for this yet but it was an aim. I also understand that because of the 40ish methods of victory, these would have to be categorized in the formula? Or would a if 'submission' is present +1 type thing be an option? Again, the fact only one person wins and it could say draw. would add massive complexity to this so im not sure if its actually achievable.

    Thirdly, would it be possible to calculate win streaks and/or loss streaks. Ideally in the same column so someone could be on a win streak of '+6' and the person under them could be on '-2'. This is simply for making the sheet more holistic as it adds little practical value but it is something i'd like to include.

    Fourthly, the rankings you made were exactly what my next aim was. However, these are pound-for-pound rankings (dont know if that makes sense?). In reality everyone has an individual weight class and they fight people of same gender in same/ similar class. I have a base sheet of everyone and I have divided them up by weight class on a different sheet using an array if index small row formula (was very impressed by myself) and the ELO will go next to it - should be simple if I use the same lookups I have for this. Also I dont know if its possible but to only show the top 15 in ranked order?

    Lastly, thank you so much for the help. I genuinely couldn't have done any of this without you and ALiGW's input. I also realise it has taken up alot of your time and effort so thank you. It means a lot.
    Also, I'm not asking you to solve all these things, I'm just wondering your take as admittedly I just dreampt up these ideas with limited knowledge of excel.

    Matt

  36. #36
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Lookup multiple occurrences of the same value in two different columns

    Hi Matt,

    glad it worked for you.

    I was wondering about drawn results. I suppose that you could use another character to indicate a draw, (like "^" which will not appear in a name) so that you would use this instead of the "|" in the helper column, like this:

    =IF(OR(A3="",C3=""),"","_"&A3&"_"&COUNTIF(A$3:A3,A3)+COUNTIF(C$3:C3,A3)&IF(B3="draw","^","|")&C3&"_"&COUNTIF(A$3:A3,C3)+COUNTIF(C$3:C3,C3)&"_")

    The formulae to return the current and previous ELO values in columns E:F and I:J would obviously have to be changed, but these are fairly trivial - where you have FIND("|",D3) this would need to become:

    FIND(IF(B3="draw","^","|"),D3)

    I'm not sure what calculations would be applied to the ELO values in the event of a draw, but clearly those formulae in the ELO sheet would need to be adjusted also.

    You could use COUNTIFS instead of COUNTIF to count the number of draws, where you are looking for a particular name AND column B contains "draw" - obviously, for a draw the name could appear in either column A or column C.

    It would be possible to have a "Win type" column and to control this with a dependent drop-down.

    A variation of the formulae in the ELO sheet would allow you to return W D or L instead of actually calculating the new ELO values, so this would give you the win/loss streaks.

    If you have the names and ELOs in separate sheets for division/gender, then it should be fairly straightforward to produce the ranking tables. For the ranking table I gave you, you could make this a top-10, for example, just by deleting from 11 downwards, so you don't need it to cover all the names.

    Hope this helps, Matt - it has been an interesting exercise for me and I wish you well for its future development. Feel free to come back here if you have any other problems.

    Pete

  37. #37
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Lookup multiple occurrences of the same value in two different columns

    Hi Matt,

    I've gone ahead and showed in the attached workbook you how you could include some of the facilities discussed above.

    I've used the "^" character to indicate a draw, as suggested, so the formulae on the Match sheet have been adjusted to accommodate this, as I pointed out in the previous post. I've also changed some of the results (column B) to test this out. The formula for calculating the ELOs have been changed, so C2 in the ELO sheet has become this:

    =IF(OR(COUNTIF(Match!$D:$D,"_"&$A2&"_"&COLUMNS($C:C)&"^*"),COUNTIF(Match!$D:$D,"*^"&$A2&"_"&COLUMNS($C:C)&"_")),B2,IF(COUNTIF(Match!$D:$D,"_"&$A2&"_"&COLUMNS($C:C)&"|*"),B2+96*(1-INDEX(Match!$G:$G,MATCH("_"&$A2&"_"&COLUMNS($C:C)&"|*",Match!$D:$D,0))),IF(COUNTIF(Match!$D:$D,"*|"&$A2&"_"&COLUMNS($C:C)&"_"),B2+96*(0-INDEX(Match!$H:$H,MATCH("*|"&$A2&"_"&COLUMNS($C:C)&"_",Match!$D:$D,0))),"")))

    which might be easier to read if I show it like this:

    =IF(OR(COUNTIF(Match!$D:$D,"_"&$A2&"_"&COLUMNS($C:C)&"^*"),COUNTIF(Match!$D:$D,"*^"&$A2&"_"&COLUMNS($C:C)&"_")),B2,
    IF(COUNTIF(Match!$D:$D,"_"&$A2&"_"&COLUMNS($C:C)&"|*"),B2+96*(1-INDEX(Match!$G:$G,MATCH("_"&$A2&"_"&COLUMNS($C:C)&"|*",Match!$D:$D,0))),
    IF(COUNTIF(Match!$D:$D,"*|"&$A2&"_"&COLUMNS($C:C)&"_"),B2+96*(0-INDEX(Match!$H:$H,MATCH("*|"&$A2&"_"&COLUMNS($C:C)&"_",Match!$D:$D,0))),
    "")))

    I don't know how the previous ELO value will change in the event of a draw, so for this I've just assumed that it stays the same (shown in red), but this may need to be changed. This formula gets copied across and down.

    The ranking table now shows the top 10 only, no matter how many players you have.

    I've also added a Win/Draw/Loss table to the right, which uses the same principles as for the ELO calculations, i.e. in Y2:

    =IF(OR(COUNTIF(Match!$D:$D,"_"&$A2&"_"&COLUMNS($Y:Y)&"^*"),COUNTIF(Match!$D:$D,"*^"&$A2&"_"&COLUMNS($Y:Y)&"_")),"D",
    IF(COUNTIF(Match!$D:$D,"_"&$A2&"_"&COLUMNS($Y:Y)&"|*"),"W",
    IF(COUNTIF(Match!$D:$D,"*|"&$A2&"_"&COLUMNS($Y:Y)&"_"),"L",
    "")))

    and again, this is copied across and down.

    Hope this helps.

    Pete
    Attached Files Attached Files

  38. #38
    Registered User
    Join Date
    03-27-2018
    Location
    Manchester, England
    MS-Off Ver
    2016
    Posts
    76

    Re: Lookup multiple occurrences of the same value in two different columns

    Hi Pete, sorry Ive been busy today.

    The win ELO is calculated as 1-x. The loss is 0-x. The draw I was going to enter manually as its rare as just 0.5-x. This is because say a 2000 draws against a 1000. Its actually a bad result for the 2000 as they were such huge favorites so the 2000 would still decrease just less significantly, while the 1000 increases less significantly.
    I apologize but I don't fully understand your previous post (the two formula parts)
    [EDIT]: Looked at the workbook and the text again, I really don't understand what you were meaning about the draw ^ thing. Sorry.

    I really like the Win/Loss/Draw section! I didn't fully understand what you originally meant but I really like it. Would a steak formula that goes from right to left be available? Like, Looks at the most right word (win loss or draw). And counts to the left until the repetition is broken? Don't have a clue if this exists however thought it worth a question.

    Currently my fightcards are layed out like this:
    Weightclass-MatchType-Fighter1-def/draw-Fighter2-MethodOfVictory-The ELO stuff

    Ideally I wouldn't want an extra column of 'submission, ko, decision' as this visually is repetitive with the method of victory. Also the comprehensive method of victory list is required to show tendencies. Is there a way to do this without the additional simplified manual column?

    Ive attached a text document of the actual list - don't know if this is of relevance.

    Edit 2: How did you do the highlight of 'adam' so it wasnt a fill and all adams and adams stats where highlighted?

    Matt
    Attached Files Attached Files
    Last edited by Hinchliffe2012; 03-30-2018 at 12:37 PM.

  39. #39
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Lookup multiple occurrences of the same value in two different columns

    Hi Matt,

    I've been working on this a bit more off and on, but as it is not finished I won't post the workbook yet. However, I thought I'd just respond to a few of your points.

    The colours are obtained through conditional formatting. If you select cell A3 on the Match sheet and click on Conditional Formatting | Manage Rules you will see that I have set up 3 rules and that they relate to the range A3:A17. Each rule is for $A3 to be equal to some name, and an appropriate colour is used. I only set it up for 3 names, but this could be extended to more names with more colours. The same set of rules apply to columns H and L. A similar set of rules apply to columns C, I and M, although these are looking at the names in column C. However, I've changed this approach in the workbook I've been working on, so that you can decide which names to highlight more easily, without having the distraction of too many multi-coloured cells.

    Thanks for your victory list. I've set this up with two columns and dependent data validation so that it is easier to choose from the lists - more on how to do this can be found here:

    http://www.contextures.com/xlDataVal02.html

    There are quite a few threads on the forum about consecutive results (or streaks). I've never looked into this in detail, but you might be able to find something that you can adapt. I was thinking more along the line of the "last 5 results" (like with football), which can be obtained quite simply with RIGHT(results_list,5) where results_list is just those individual W D L concatenated together.

    A draw is a result, even if it is not very likely, so the formula needs to be able to cope with it - I'm not happy with using a manual approach for various reasons. The formula that I quoted above is basically "if it is a draw then do the draw_calculation (which for now is just to take the previous ELO value), if it was a win then do the win_calculation, if it was a loss then do the loss_calculation, otherwise do nothing". A win is determined by the name being on the left side of the "|" character and a loss if the name is on the right side, so by using a different character for a draw, then that can also be determined. There is a complication here, though, as the name could be on the left OR the right of the "^", and so this needs to be determined so that the appropriate value for the ELO can be used. Suppose we have a draw for the match between Fred and John where the ELO could be 1200 and 1000 respectively. Then this could be recorded as:

    ... Fred ... draw ... John .............. 1200 ... 1000

    or it could be recorded as:

    ... John ... draw ... Fred .............. 1000 ... 1200

    so the formula needs to take account of where the name occurs in order to use the correct ELO value.

    This is not particularly difficult, it's just a bit awkward to make sure all the logic is working correctly, and I hope to complete this tomorrow as rain is forecast. You also have a 96*adjustment in the other formulae - will this also apply?

    Anyway, that'll do for now.

    Pete

  40. #40
    Registered User
    Join Date
    03-27-2018
    Location
    Manchester, England
    MS-Off Ver
    2016
    Posts
    76

    Re: Lookup multiple occurrences of the same value in two different columns

    Hi Pete,

    Thanks for the conditional formatting suggestion. I have done this and it seems perfect. Also, it's allowed me to add more possibilities in the drop downs without overpopulating the lists.
    Am I right to think that some variation of a countif or countifs formula will be required for counting the styles of victory?

    After searching the forum, I'm thinking something along these lines for the streak:
    https://www.excelforum.com/excel-gen...ingle-row.html
    Though, I will need to see if this formula does the current streak if its negative. Other than that, this looks like the most similar format to my current design.
    [Edit 2]: After checking the formula I dont think this does account for losses. Would repeating the formula in the same cell with a different result be a possibility. Alternatively, these could be two helper columns? And the actual column could be win streak cell minus loss streak cell. Obviously one of these will always be zero? Not sure if this makes sense but I thinks i's possible. Probably not very efficient now Im thinking about it though... maybe?

    With the win the elo is 1-x for player 1 in the left column and 0-x for the player in the right column.
    For a draw it is 0.5 - x for the player in the left column and 0.5-x for the player in the right column.
    The x in all scenarious includes formula that is dependent on the previous elo. Thus, I dont think it matters if Fred (1200) is on the left and john on the right (1000) or vice versa.
    Regardless of their positioning I think it resluts in the same outcome (at least in my head - I very easily could be missing something here)


    [Edit]: Just to clarify, the win loss draw formulas are exactly the same except for the 1 minus; 0 minus and 0.5 minus. The k value (the 96) stays the same in all formulas. If I was to change it, then I would have to change it in all. The impact of the k value is that it basically affects how sensitive the ELOs are to a result. 96 is high, meaning a win and loss both increase and decrease by more than average. In chess I think they use 36 or 48 as the k value.

    Thanks


    [UPDATE]: I am really having a lot of difficulty at the moment. I started this yesterday, came back to it today, but I am still being unsuccessful.
    Specifically, editing the D:D (W:W for me) formula; the ELO! sheet formula and the WLD! sheet formula - to account for a draw. Before draws were considered everything worked perfectly. Now I'm getting values everywhere and have just reverted everything back to how it was previous. My main issue is I can't see where my formula issues are. I don't know which formula is creating the values as they all interlink. I will keep trying, I'm just updating my current stage.
    As for the WLD sheet (The win loss draw table has its own sheet on mine. I cant get any value to appear but I'm presuming this is again due to the D:D (W:W) formula not working.


    [UPDATE 2]: I have solved my previous issues regarding the ELO and the draws so mine now resembles yours. I also have realized what you meant about the draws. Currently one area of the formula mentions wins. One mentions loses. I think 2 need to represent draws. Both using the 0.5 minus but with one taking odds from the left column and one taking odds from the right column. I dont know how to do this but I am on the same page of understanding the problem.
    Also, my Win Loss Draw sheet now works the same as yours in your last post.
    Also, I have unsolved the thread by request.

    Thanks again
    Last edited by Hinchliffe2012; 04-01-2018 at 09:58 AM.

  41. #41
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,381

    Re: Lookup multiple occurrences of the same value in two different columns

    This thread is marked solved - if you want more input, I suggest you remove that tag for now.

  42. #42
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Lookup multiple occurrences of the same value in two different columns

    Hi Matt,

    I think I've been over-thinking the draw calculation, but I'll leave it as previously reported and go and do some gardening (i.e. a draw result just returns the previous value of ELO). I thought I'd let you have what I've been working on so far, though, so that you can decide how best to proceed.

    First of all, I've inserted some columns along the lines of your comments in Post #38, but I've used two columns for the victory (F and G), which I've called Outcome and Method - these are dependent drop-downs, so you choose the Outcome first, and then the appropriate method. You did say that you didn't want two columns, but I think choosing from a short list twice is preferable than choosing from one long list (though obviously, you are the one who will be using this).

    I've then used H and I columns to show the player's recent "form", i.e. the last 5 results. These are the results as the fight is recorded, so that you can see for Adam's first fight there was no previous form (5 dots), but for his second fight his form is "....D", and then for the third fight it becomes "...DL", and so on. In the ELO sheet I have concatenated all the results into on string in column AM, to enable this display to be achieved. This is an alternative to the "streak" approach.

    You will notice that I've also changed the conditional formatting, and now there is a key for Track Players in column R. In cells R3, R4 and R5 you can choose a player from the drop-down, and their data will be highlighted in that colour. I thought this would be easier to highlight a particular player's progress rather than have individual colours for each player. You can see the amended rules that I've applied in C3 and E3.

    As I said earlier, I've got to get my head around the draw calculation, but hopefully this workbook will give you some ideas.

    If you are having some difficulty transfering formulae across, then you should take this workbook and insert columns and rows where necessary so that it matches your own workbook, and rename the sheets so that they match yours, and then it should be easier to transfer them across. Note that I have used some named ranges in this workbook - your can see them by clicking on Name Manager in the Formulas tab.

    Hope this helps.

    Pete
    Attached Files Attached Files

  43. #43
    Registered User
    Join Date
    03-27-2018
    Location
    Manchester, England
    MS-Off Ver
    2016
    Posts
    76

    Re: Lookup multiple occurrences of the same value in two different columns

    I really like the application of that tracker. And I have sorted my issues I was having with the previous formulas so everything works well now. (I was just being stupid)
    As for the draw, I'm currently thinking you were correct and I was oversimplifying it.
    The draw needs to account for both the odds of player 1 and player 2. So doesnt it need a 0.5-xforplayer1 and a 0.5-xforplayer2. So instead of being 1 calc for winning, 1 for losing, would having 2 for draw be the best solution?

    Have fun gardening and happy easter

    Matt

  44. #44
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Lookup multiple occurrences of the same value in two different columns

    Hi Matt,

    just taking a bit of a rest from some digging (I find that gardening really helps to clarify the mind).

    My thoughts are that there are 3 scenarios for a draw:

    a). A player draws against an opponent with the same ranking - I would have thought here that the ELO values for both players would remain the same, as that would be the expected outcome.

    b). A player draws against an opponent with a higher ranking - Here the first player should have their ELO increased (better than expected performance) and the 2nd player should be decreased.

    c). a player draws against an opponent with lower ranking - Here the first player should have the ELO decreased (worse than expected performance) and the 2nd player should be increased.

    In all cases you have said that the odds are to be taken as 0.5 - what we will need to find in the formula is the ELO for the second player (whether they are on the left or right of the "^"), compare this with the ELO for the first player (which is B2 if the formula is in C2) and then determine a, b or c above. This implies 2 IF statements to replace the red B2 in the formulae from a few posts back, i.e.:

    IF ELO1 = ELO2 then return ELO1 (B2) unchanged, IF ELO1>ELO2 then apply decrease to ELO1, otherwise apply increase to ELO1.

    Obviously there is a lot more needed to get this working and this is quite different from the other part of the formula for win/lose as we need to find the other players current ELO (they might have played more or fewer times than the player we are considering, so we can't assume the ELO is in the same column).

    I hope you agree with the logic, and I'll take a detailed look at the workbook later on when it gets dark.

    Pete
    Last edited by Pete_UK; 04-01-2018 at 11:20 AM.

  45. #45
    Registered User
    Join Date
    03-27-2018
    Location
    Manchester, England
    MS-Off Ver
    2016
    Posts
    76

    Re: Lookup multiple occurrences of the same value in two different columns

    Not exactly. The ELO calculation is what changes from win to loss to draw. The odds are generated independent of this. So if the ELOs are the same, the odds formula already generates the same numbers. The odds formula result is then included in the ELO calculation. To clarify, if the original ELOs are equal then this will already generate the same odds of victory for each and the same resultant new ELO.
    In the ELO calculation the only thing that changes is the odds and the 1,0.5 and 0. So in a draw of equal ELO the odds are the same. The 0.5 is the same. So the outcome is the same. All this is currently accounted for in the ELO and odds formulas.
    Odds formula:
    (1/(1+10^((O6-N6)/400))

    Apologies as the last part of your message has lost me.

    Matt

  46. #46
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Lookup multiple occurrences of the same value in two different columns

    Hi Matt,

    it would help me if you showed what results you would expect (manually) from the following 3 drawn results:

    Dave (ELO = 1500) vs John (ELO = 1500)

    Dave (ELO = 1500) vs Fred (ELO = 1000)

    Dave (ELO = 1500) vs Frank (ELO = 2000)

    and then I could hopefully understand more what the formula needs to do. Can you put this together in a little workbook and attach it.

    Cheers,

    Pete

  47. #47
    Registered User
    Join Date
    03-27-2018
    Location
    Manchester, England
    MS-Off Ver
    2016
    Posts
    76

    Re: Lookup multiple occurrences of the same value in two different columns

    Yes sure, see attached. Should have done this earlier as its easier than explaining.

    Ive also got the streak sorted now but it's only on my actual workbook. If youd like I can add it to the one you have been working on? Would only take a couple minutes so its up to you.

    Matt

    Feel free to rename the columns as you see fit to make it clear. I just named them quickly. The format follows the rest of the regular workbook.
    Attached Files Attached Files

  48. #48
    Registered User
    Join Date
    03-27-2018
    Location
    Manchester, England
    MS-Off Ver
    2016
    Posts
    76

    Re: Lookup multiple occurrences of the same value in two different columns

    Added text explaining a potential solution.
    Attached Files Attached Files

  49. #49
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Lookup multiple occurrences of the same value in two different columns

    Hi Matt,

    thanks for this - it confirms what I thought in Post #44. i.e. if the two ELOs are the same then they remain the same after a draw, otherwise the higher one is reduced and the lower one is increased (both using odds of 0.5).

    I'll have a stab at it tomorrow.

    I followed your thread about the streak, but it seems to me that the answer will be constantly varying in time. For someone's first game there will be no streak, after 5 bouts there could be a streak of up to 5 wins, but that streak does not apply to the earlier bouts. Maybe I'm misunderstanding how you want to make use of this, so yes, it would be interesting to see how you have implemented it.

    Cheers for now.

    Pete

  50. #50
    Registered User
    Join Date
    03-27-2018
    Location
    Manchester, England
    MS-Off Ver
    2016
    Posts
    76

    Re: Lookup multiple occurrences of the same value in two different columns

    No worries,

    Here is the updated version with the streaks. Ive tested it a fair bit but had no issue so far.

    Matt
    Attached Files Attached Files

  51. #51
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Lookup multiple occurrences of the same value in two different columns

    Hi Matt,

    thanks for the workbook with the streak formula in - I think I see now how you want to use it.

    After much head-scratching I've managed to come up with this formula for C2 in the ELO sheet, which takes account of Draws, Wins and Losses:

    =IF(OR(COUNTIF(Match!$J:$J,"_"&$A2&"_"&COLUMNS($C:C)&"^*"),COUNTIF(Match!$J:$J,"*^"&$A2&"_"&COLUMNS($C:C)&"_")),B2+96*(0.5-IFERROR(VLOOKUP("_"&$A2&"_"&COLUMNS($C:C)&"^*",Match!$J:$N,COLUMNS(Match!$J:$M),0),VLOOKUP("*^"&$A2&"_"&COLUMNS($C:C)&"_",Match!$J:$N,COLUMNS(Match!$J:$N),0))),IF(COUNTIF(Match!$J:$J,"_"&$A2&"_"&COLUMNS($C:C)&"|*"),B2+96*(1-INDEX(Match!$M:$M,MATCH("_"&$A2&"_"&COLUMNS($C:C)&"|*",Match!$J:$J,0))),IF(COUNTIF(Match!$J:$J,"*|"&$A2&"_"&COLUMNS($C:C)&"_"),B2+96*(0-INDEX(Match!$N:$N,MATCH("*|"&$A2&"_"&COLUMNS($C:C)&"_",Match!$J:$J,0))),"")))

    The part in red replaces the red B2 in the previous formula, and is effectively the "draw_calculation". The section in blue is the "win_calculation" and in green the "lose_calculation".

    I've applied this to the attached workbook and I've also added in your calculations from the previous workbook - all the draw results match, but obviously you can delete the yellow table if you are satisfied with the calculations.

    Hope this helps.

    Pete
    Attached Files Attached Files

  52. #52
    Registered User
    Join Date
    03-27-2018
    Location
    Manchester, England
    MS-Off Ver
    2016
    Posts
    76

    Re: Lookup multiple occurrences of the same value in two different columns

    Thanks!

    Not sure how to overcome this. My helper column is hidden from the original data and is far to the right of the odds column. Its my understanding the VLOOKUP goes left to right, so would this work with my workbook? Or would a index match need to be done?

    Matt

  53. #53
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Lookup multiple occurrences of the same value in two different columns

    I used VLOOKUP as it's a bit shorter, but I'll convert it to INDEX/MATCH for you after dinner.

    Pete

  54. #54
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Lookup multiple occurrences of the same value in two different columns

    Here you go, Matt, with this formula in C2:

    =IF(OR(COUNTIF(Match!$J:$J,"_"&$A2&"_"&COLUMNS($C:C)&"^*"),COUNTIF(Match!$J:$J,"*^"&$A2&"_"&COLUMNS($C:C)&"_")),B2+96*(0.5-IFERROR(INDEX(Match!$M:$M,MATCH("_"&$A2&"_"&COLUMNS($C:C)&"^*",Match!$J:$J,0)),INDEX(Match!$N:$N,MATCH("*^"&$A2&"_"&COLUMNS($C:C)&"_",Match!$J:$J,0)))),IF(COUNTIF(Match!$J:$J,"_"&$A2&"_"&COLUMNS($C:C)&"|*"),B2+96*(1-INDEX(Match!$M:$M,MATCH("_"&$A2&"_"&COLUMNS($C:C)&"|*",Match!$J:$J,0))),IF(COUNTIF(Match!$J:$J,"*|"&$A2&"_"&COLUMNS($C:C)&"_"),B2+96*(0-INDEX(Match!$N:$N,MATCH("*|"&$A2&"_"&COLUMNS($C:C)&"_",Match!$J:$J,0))),"")))

    I've copied across and down in the attached workbook.

    Hope this helps.

    Pete
    Attached Files Attached Files

  55. #55
    Registered User
    Join Date
    03-27-2018
    Location
    Manchester, England
    MS-Off Ver
    2016
    Posts
    76

    Re: Lookup multiple occurrences of the same value in two different columns

    Thanks, that works a treat. Such a long formula!

    Matt

  56. #56
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Lookup multiple occurrences of the same value in two different columns

    Well, it was even longer while I was developing it, as I thought I needed IF(ELO1 = ELO2, calculate, IF(ELO1 < ELO2, calculate, calculate)) ... and each calculate had an "IF it's on the LHS then get the left ELO/odds otherwise get the right ELO/odds", and then I set about simplifying it.

    Anyway, if that solves the problem you can mark the thread as SOLVED again.

    Pete

  57. #57
    Registered User
    Join Date
    11-15-2018
    Location
    asd
    MS-Off Ver
    2007
    Posts
    7

    Re: Lookup multiple occurrences of the same value in two different columns

    Pete_Uk I need Help ... i want to use Elo Ranking for football. How can I transform from expect from each team to odds 1 X 2?
    I saw something on a site but did not explain how 1.jpg

  58. #58
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Lookup multiple occurrences of the same value in two different columns

    As you were told in your other post, you should not ask questions in someone else's thread and try to hijack it - instead, you should start your own thread, with a link back to this one if your think it is relevant.

    You can also see in this thread that it helps if you attach a sample Excel workbook, rather than a picture of one.

    To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post. Don't try to use the Paperclip icon (attachments button), as it doesn't work on this forum.

    Hope this helps.

    Pete

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Using VLOOKUP to Search for Multiple Occurrences with same Lookup Value
    By rosetc16 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-08-2018, 11:30 PM
  2. Looking up multiple occurrences of lookup value, returning corresponding data
    By radioactivity in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-02-2015, 02:22 AM
  3. [SOLVED] lookup and list matches of a ref no with multiple occurrences
    By SKooLZ in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-04-2015, 04:46 PM
  4. [SOLVED] Lookup to find multiple occurrences
    By y_not in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-13-2013, 01:40 PM
  5. Replies: 6
    Last Post: 05-12-2012, 11:40 AM
  6. Replies: 3
    Last Post: 12-10-2009, 06:21 PM
  7. [SOLVED] lookup multiple occurrences of a value excel
    By ckl in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-03-2005, 02:06 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1