Excel decisionmaker

Fer9us

New Member
Joined
Nov 28, 2020
Messages
11
Office Version
  1. 365
Platform
  1. Windows
Hi MrExcel,

I'm trying to use excel to help me to create a tool to make complex decisions -- to find favourite items or options among a single list of multiple items or options.
This should be relatively simple to program, only I am a newbie at this.

I would really appreciate suggestions on how to code this process:

1. User inputs a number of items/options into LIST A.

2. Excel calculates the number of entries in LIST A and the number of combinations of pairs entries (without any repeats/duplicates).

3. Excel then generates a 2-COLUMN TABLE showing all the possible different pair combinations from the original list.

4. Entries from the 2-COLUMN TABLE are fed into a QUESTIONNAIRE (row by row), using a template "Which option from this pair is preferable? A or B?"

5. The user completes the QUESTIONNAIRE, and the votes for each individual item/option are tallied.

6. The item/option with the most votes wins, and the winning entry is displayed, followed by the runner up, 3rd place, 4th place and so on until all entries on LIST A have been put into order of preference.

END.

Note, ideally the program/code is scalable -- so could be used to for a list of up to "N" entries.

Look forward to hearing your ideas,

Fer9us
 
@rpaulson — do you have any suggestions on what I’m doing wrong?
Note this is an activex button I think - I tried earlier with a macro button but that was only directing me to the earlier two scripts.
Thanks,
Fergus
 
Upvote 0

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
I never used Active X Buttons.

I have the below code in a standard module.
VBA Code:
Sub form()
UserForm1.Show
End Sub

I then add a basic text box or a shape to my sheet
right click on it and assign-macro to open the form.

-Ross
 
Upvote 0
OK weird,
Without changing the "NEW BUTTON" I closed the form, restarted the machine and reopened it and now the button works.

Weird also, the userform comes up but its buttons don't change:

1607000265981.png


So it doesn't matter how many times I press Cow or Dog, the options don't seen to move on to the next options.

On the Calc tab, the combinations are all shown:

1607000352103.png


I've also followed your suggested steps to put the code you suggested into a standard module which I inserted in the VBA screen (Alt F11).
Then I created a fourth button under Developer > Insert > Form controls > Button (Form control), and assigned the Macro "form" to it. I called it THE RED BUTTON

That brings up the same userform, which presents me with two named options, but when I click them, the form stays the same.

As you can see below if I keep clicking Dog, I can keep going and it doesn't change the combination. The dog score just keeps going up.

1607001225582.png


Does this work on your side?
Thanks,

Fer9us
 
Upvote 0
on your form you have the text of num under cow.

this should actually be a number, it will show you the current selection you are on an will increment by 1 every time you answer the question.
this is also how the code to change the selections to new next row of the picking list.

I don't think you Named the label on your form "num", I think its probably still named Label1.
right click on the label in the form and make sure the (Name) Property is num

the name of this label must match the name used in the code. num = num + 1
 
Upvote 0
Looks like it's WORKING!

1607010162768.png


Retests wins!

I like the way it re-prioritises the list at the end based on the votes. Nice one.

Next step would be: Can I add a static text field to the Userform that presents the question to the User, "Which option do you prefer?"

Also, there's the issue of the tiebreaker.

Thanks for your guidance and patience with this - works well and it's pretty simple.
 

Attachments

  • 1607009943923.png
    1607009943923.png
    31.7 KB · Views: 4
Upvote 0
You can add anything that you would like onto the userform.

Personally I would add another label that would show the user the total questions that will be asked. ie 15

to do this
- add a label on your form and name it tot
then in the user for Private Sub UserForm_Initialize() code add the below line after the num=1 line

tot = ws.Cells(Rows.Count, "A").End(xlUp).Row
 
Upvote 0
Works well. I'll tweak it a bit more over the weekend. For now it's looking pretty schmick.
Thanks again,
Fer9us

1607120233685.png
 
Upvote 0
Hi Ross,
Hope all well with you.
Just wanted to let you know this app we made has been really useful for me and I've tried it out with several other people too facing complex decisions.
Excel_decisionmaker_1.png

As a possible upgrade, I've been thinking about the possibility of putting the pairs in a random order.
The problem is, when we hit the Prioritise button, the Userform takes pairs from the list in the order they were originally listed and this tends to be repetitive, i.e. it asks the user first to select between all the pairings with option A, then all the remaining pairings with option B, and then all the remaining pairings with option C, and so on.
Excel_decisionmaker_2.png

The problem with this approach is more to do with the way the form is used - and this approach changes the way the user thinks.

When we're answering these similar questions over and over - we're more likely to go into autopilot, or give a biased response.
Conversely, I think we're more likely to get a genuine response if, once the list of pairs is set up, it's put in a random order.
So I am hoping you can help tweak this system to keep the same approach where it generates a list of all the permutations, but then immediately shuffles them.
Is that something we could do?
thanks in advance,
Fergus
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,927
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top