Merging one Column into another

Copper Contributor

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 Replies
best response confirmed by Hans Vogelaar (MVP)


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.





The old data you can delete as you want.


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.