0

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.

  • 1
    Pivot tables could work for this. In "Value Field Settings" you can select Average for each field and then select Max for the speed field. – Rey Juna Feb 7 at 3:15
0

Well, I ended up using VBA macro code to do this and it worked pretty well. I saved the file as a macro enabled workbook and then had to tweak some security options, but got it working.

The macro, when run, gathers all the data from the data entry worksheet and dynamically builds the results worksheet.

I'm betting Rey Juna's suggestion about pivot tables would also be valid, but I have no idea how to work with those, but I am a developer so writing the macro was more comfortable for me :)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.