I have some data in Excel in the following format (represented as CSV for simplicity here). Each row is a test result for a user. Each user has undergone multiple tests. For example:
Name,Speed,Agility,Strength Bob,3,2,1 Fred,4,3,2 Sue,6,3,8 Bob,,4,5 Fred,,1,2 Sue,,3,2
I need to end up with 1 row for each user. The test results for a given user should be combined by averaging the values. For cases of missing values (like speed which is only recorded once per user), it should just take the 1 value which is present.
I want to preserve the original data, but somehow generate the combined set of rows separately on demand.
Any suggestions on where to start? I'm using Excel via Office 365 (version 1901 Build 11231.20130 Click-to-Run) on Windows 10 64-bit.