Tell me more ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.
up vote 1 down vote favorite

I am conducting a study in which each of the 300 participants are rating 12 prototypes each. We have 33 prototypes in total. How would I generate 300 lists each containing 12 random prototypes from the 33 in excel ensuring that no list contains a duplicate and all prototypes appear at least 100 times?

share|improve this question
1  
This belongs to stackoverflow. It also needs to be retagged with the word 'algorithm'. I don't know if Excel can do this, but it's a rather simple task for a program. – Trevoke Feb 5 '10 at 19:56
1  
This would be a trivial programming task, but very difficult to achieve in regular excel. VBA isn't difficult, and is inbuilt to excel! – Phoshi Feb 5 '10 at 20:06

3 Answers

up vote 1 down vote

This isn't the sort of thing Excel is good for, but you might be able to use the VB integration to do it.

share|improve this answer
up vote -1 down vote

Simplest way:

  1. Make 1 row for each Participant, starting in A2, using column A to put their name/participant#.

  2. Place one prototype per column in cells B1:AH1, then select and drag these down so that it fills each column for all 300 participants.

  3. Write a macro that randomly deletes 21 cells between B1:AH1 for each row

  4. Use sort in the tool menu to be able to check for duplicates

share|improve this answer
Dublicates. Might have to add this to my dictionary, right next to automagically. – BillN Feb 5 '10 at 21:29
This doesn't address the requirement that each prototype must appear 100 times. If you're going to write a macro to delete cells, you'd be better off writing a proc that generates the lists and checks them in the first place. – Dave DuPlantis Aug 26 '11 at 18:53
up vote -1 down vote

While it is definitely possible to do in Excel, it would be difficult since VBA is tricky to work with. If you need a more powerful spreadsheet tool you might want to look at Resolver One

share|improve this answer
it's actually not difficult at all in VBA. – Dave DuPlantis Aug 26 '11 at 18:51

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.