Forum Discussion
Solved
Merging one Column into another
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
- David Ancliffe - C Team - 95
- Vera Dubrovnik - B Team - 93
- Harry Sahota - C Team - 86
- 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
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.
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.
- 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.
