I want to do this with a formula if possible as I am not familiar with Macros.
I am trying to combine baseball stats from multiple sources. However, the sources don't always have the same list of players. In my example the Source 1 has many more names than Source 2; source 2 only has one name that source 1 doesn't have. What I would like to do is somehow create a new matrix with the average of the data. So if Player A has 45 HRs from one source and 35 in another the final matrix will show 40 HR. Also if Source 1 has Player B but Source 2 does not I want the final Matrix to show Player B with source 1's data (or inverse of this).
I think this can really boil down to creating a column of unique player names from all the sources but I am unsure as to how to do this.
I have also shown a matrix of source 2 that has been spread out so that it lines up with source 1; meaning that if there is a player in Source 1 but not Source 2 that there will be a blank in the spread out version. If somebody can think of a way to recreate this with a formula that would also solve my problem.
Anything that could help or maybe point me in the right direction would be great.
Let me know if I need to explain more. Thanks!
Baseball Stats Question.xls
Bookmarks