SOLVED

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)
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))

 

dscheikey_0-1665071198241.png

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.
1 best response

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

 

dscheikey_0-1665071198241.png

The old data you can delete as you want.

 

View solution in original post