Hi,
I added In line 1 VIS# and Home# to the proper column, + in DE:GU - it is needed for the formula to workproperly.
DE and across for all yellow region:
=IFERROR(INDEX($A$3:$DA$593,AGGREGATE(15,6,((ROW($J$3:$J$571)/((LEFT(INDEX($A$3:$DA$3,MATCH(DE$2,$A$2:$DA$2,0)):INDEX($A$1000:$DA$1000,MATCH(DE$2,$A$2:$DA$2,0)),SEARCH("PTSSCRD",INDEX($A$3:$DA$3,MATCH(DE$2,$A$2:$DA$2,0)):INDEX($A$1000:$DA$1000,MATCH(DE$2,$A$2:$DA$2,0)))-1))=($C$3:$C$578)))),ROWS($M$2:$M2))-1,COLUMNS($A$2:J2)),"")
FA and across for the red region:
=IFERROR(INDEX($A$3:$DA$593,AGGREGATE(15,6,((ROW($J$3:$J$571)/((LEFT(INDEX($A$3:$DA$3,MATCH(FA$2,$A$1:$DA$1,0)):INDEX($A$1000:$DA$1000,MATCH(FA$2,$A$1:$DA$1,0)),SEARCH("PTSSCRD",INDEX($A$3:$DA$3,MATCH(FA$2,$A$1:$DA$1,0)):INDEX($A$1000:$DA$1000,MATCH(FA$2,$A$1:$DA$1,0)))-1))=($F$3:$F$578)))),ROWS($M$2:$M2))-1,COLUMNS($A$1:J1)),"")
I think that your manually typed in results were wrong for Home1
as Lebron James is CLEPtsScrd and HOME is NWO
Hopefully this formulae will work for you this time....
BTW - It was really hard to work with such a big data file, next time just post a sample according to the instructions in the forum, given in the yellow banner at the top of the page....
Bookmarks