Merging one Column into another

[Charlie257]

Using Excel 365 for Mac.
Each month I get a list of names teams and scores. (see example 1)

I then have a second spreadsheet with 6 months of trends, (Example 2)
Each month I have to remove the month column from 7 months (In this example January) and replace with current Month (July). 

With 200 players in 8 different teams it takes me a long time changing the Trends table manually.

Any suggestions

Example 1

July Scores

1. David Ancliffe -   C Team - 95
2. Vera Dubrovnik - B Team - 93
3. Harry Sahota   -   C Team  - 86
4. Ralph Mitchell   - A team - 82

Example 2

                                                 Jan    Feb  Mar Apr May Jun TOTAL

David Ancliffe      C  Team         65       70    80  65   72 74      426

Harry Sahota       C Team          65       65    72   54  82   84     422

Vera Dubrovnik    B  Team         64       65     69   72  74   76    420     

Ralph Mitchell      A Team          71      69    68   66   66   68    408

[#2 · Accepted solution]

@Charlie257 

Hello Charlie,
My suggestion would be to collect the data in one worksheet and do the analysis in a second one. I did this in my example with SUMPRODUCT().

Have a look at my example file. Here you can set the first month to be evaluated by changing the cell C1 in Analysis.

=SUMPRODUCT(Data!$D$2:$D$29,(Data!$A$2:$A$29=C$1)*(Data!$B$2:$B$29=$A2))

The old data you can delete as you want.

[#3]

Thank you @dscheikey. It took me a while to work out how to use your method, but now I understand it, the time I save will substantial each month.