LinkBack URL
About LinkBacks
Hi, im wanting to match 2 teams that have played each other previously to see their head to head record. Can some please help me with this? thanks
example.xlsx
Hi, im wanting to match 2 teams that have played each other previously to see their head to head record. Can some please help me with this? thanks
example.xlsx
Using the example you have attached, what do you want the output to look like?
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
Hi Glenn, the matched data can just go into columns G H I J K with each match listed in a separate row, preferably with the most recent at the top (ie. row 1)
thanks.
Not quite what I meant... can you manually put in the desired result in those columns and then we'll have a go at reproduicng them using Excel... Sorry for not explaining myself properly!
Glenn,
File attached. Previous matches between Team A and Team B.
example.xlsx
thanks.
LoL... at least there are now two teams that have played twice, now. I was a bit baffled by your first example!!
Glenn,
Amended attached. I usually have the 2 teams im wanting to be matched listed also G1/G2.
example.xlsx
thanks.
Edit: Sorry, yes I amended it so a team has played twice. thanks.
Last edited by prudential; 01-11-2015 at 05:35 AM.
If you don't like the helper column, this can be hidden. It can probably be achieved without the helper, using a slightly more complicated formula, too.
Last edited by Glenn Kennedy; 01-11-2015 at 05:44 AM.
With an helpcolumn in column G and H.
Then a filter on the data.
You can filter on column G to find all combinations of a specific team.
I column I is counted how many times the teams played against eachother.
See the attached file.
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
Glenn,
Just copied it to my existing worksheet which has over 1,000 games with teams playing on multiple ocassiions and it is only picking up the one instance of the teams playing.
thanks,
I1=IFERROR(INDEX(A:A,SMALL(INDEX((MMULT(($B$1:$B$6=TRANSPOSE($G$1:$G$2))+($D$1:$D$6=TRANSPOSE($G$1:$G$2)),{1;1})<2)*10^10+ROW($A$1:$A$6),0),ROW(A1))),"")
Try this array formula in I1 and copy across
Please confirm with Shift+Ctrl+Enter since it is a array formula
see the attached file
here is two types one is with array and another is without array.
Samba
Say thanks to those who have helped you by clicking Add Reputation star.
Did you adjust the ranges? I also forgot to mention that it's an array formula, which must be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
Once again, nflsales, a very impressive formula. I have never quite understood MMULT...
Glenn,
Yes, did the control + shirt for each formula before copying down. Then cut and paste to another sheet because this sheet is just a database and the output from the formula disappeared so it must be searching an empty cell or column.
thanks,
This is the formula after cutting/pasting from worksheet "Historical_Scores to new sheet.
=IFERROR(INDEX(Historical_Scores!A$1:A$3000,SMALL(IF(Historical_Scores!$G$1:$G$3000=$A$54&$A$55,ROW(Historical_Scores!$G$1:$G$3000),IF(Historical_Scores!$G$1:$G$3000=$A$55&$A$54,ROW(Historical_Scores!$G$1:$G$3000))),ROW($A54:$F54))),"")
This last bit is wrong:
,ROW($A54:$F54))),"")
Change to ,ROW(1:1))),"") hit CTRL + SHIFT + ENTER...
oK, THANKS, Testing it now.
Hi, I've got all working smoothly now. Tried to reply earlier but i think the site was down. Thanks again for your quality assistance, its much appreciated.
This is very odd. I can't access the second page of posts on this thread...
I can see it now!! I had to switch to threaded mode...
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks for the Reputation, BtW.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks