SOLVED

HELP WITH VBA OR FORMULA

Brass Contributor

Hi ,

let's say I give you a list of fruits (something like the attachment) and ask you choose the one you like from each list. let's say there are "N" items in the list and you only like 10 of them the most.
now you start selecting or marking or checking the one you like. every time you select your choice will enter or type in another cell. or even you can select all in one time maybe through VBA you create a "run" button. by clicking the "run" all your choices will be enter to a new cell one on one.
so at the end i would have a column selection of your 10 choices of 50. hope the problem is clear.

 

thanks a lot

28 Replies
best response confirmed by msm66 (Brass Contributor)
Solution

@msm66  First off, please don't create new duplicate threads.

As for VBA, of course VBA can do anything you can do in the sheet and more, but unless the VBA code is really small or trivial, the us volunteers here probably won't create it for you.  But in VBA you can create custom forms for data entry (I believe a PC version it is still required to create or edit the form but that they will work on a Mac, but they don't work if using Excel online).  You can also create active modules that will monitor sheet edits and make updates accordingly.

That said, I often try to achieve the functionality in the sheet to avoid problems with others being required to approve the macro functions before the sheet will work (and in some cases they can't).

My prior answer using drop down menus could be modified so each successive drop down list would exclude items already selected.

Another alternative is to add another column next to the list and just have the user type an "x" in that column instead of 'checkboxes' or maybe rank their favorite foods in that column (then you could even sort by that rank).

If you want checkboxes, they point to a cell so if you create that column next to the list and add checkboxes over each cell you can have each checkbox 'point' to that corresponding cell.

 

So if you take the suggestion of adding a column that they rank their favorites then you could use:

=IFERROR(INDEX(A:A,MATCH(ROW()-ROW($C$1),B:B,0)),"")

  assuming the food is in col A, the ranking is in col B, and this list is created in col C with C1 being a header line.

Various tweaks could be made to account for non-sequential and duplicate ranking and such if needed. 

Thank you Very much and a lot appreciate your effort to give me all the different possible options. Also thank you for informing I should not create duplicate threads. I thought the second problem description was more clear and understandable. So I might get better responses. I do prefer a solution with checkbox because make it much user friendly. Can you please guide me through that option?
I am working on a project and it looks like you are an expert. Not sure how can be in touch. but let me know if you are available.

@msm66  I am not an expert, when we talk about checkboxes.  I've used them, but not too fond of them for various reasons, some of which may just be me not knowing/learning advanced use options for them.  One of the main issues I have with them is the manual overhead to create and configure each of them.  So I have attached a sample document that I think is what you are looking for and as you take it and expand it you can see/decide for yourself (unless someone else here might know and chime in with some tricks to make it easier).  

In the attached I copied the lists you showed in your attached image (spelling errors corrected) into columns B, D, F and columns A, C, E are used to give spacing between the lists so the checkboxes can sit to left of each corresponding item.  The checkboxes are then configured to control the corresponding cell it is sitting over top.  The 3 "Selection" lists are then in columns H, J, and K also skipping a column between each.  This space is nice in appearance but more importantly makes copying the formula easier.  So try it out and see and if you like you can follow the following instructions to expand:

1. fill in the rest of you lists

2. "copy" or "add" additional checkboxes (tip: if you shift - right click multiple checkboxes you can then ctrl-drag them to copy many at a time)

3. configure each new checkbox to "control" the cell it is sitting on top of.  Ctrl-Right click box and select "Format Control" then on the "Control" tab type the cell reference into the Cell Link field. (note you can click on the cell but I find that harder since the checkbox is blocking it and I'm faster with my keyboard)

4. The formulas under the "Selected" list are copied from row 2 to 6 (i.e. max of 5 items). If more are needed you can copy the formula down to additional rows.  Bonus: I added conditional formatting to highlight a "Selected" column if it appears more items are are selected than can be shown in the "Selected" list. You can test this by selecting all 6 items in the first list.

I really appreciate taking your time and providing the best and most complete answer. This was exactly what I was looking for. I really like the date validation Idea you added to it.
Thanks alot

@mtarler 

Do you have any solution for multi checkbox list? 

I Have over 50 checkboxes in one column and another over 50 checkboxes in the other column, and so on. In total, I have over 10 columns that each need over 50 checkboxes. Do I Have to link over 500 checkboxes one by one? or is there any way to make it faster?

I search and found some VBA codes but they mostly are for multi checkboxes in one column. like this one:

but it is not what I need. Any thoughts or tricks you know?

Sub LinkChecks()
i = 2
For Each cb In ActiveSheet.CheckBoxes
cb.LinkedCell = Cells(i, "B").Address
i = i + 1
Next cb
End Sub

@msm66  Hi there again.  I did say "One of the main issues I have with them is the manual overhead to create and configure each of them."  I also tried to steer you to other options using drop down or simple "X" or numbers in adjacent cells.  I'm not trying to be petty, just apologetic that I'm not much more help to your current situation.  IF you created them in an orderly fashion you can create a macro similar to the one you found to assign the cells but if you are getting into writing a macro you can write one that monitors the page and on cell activation (i.e. click) toggle the background color and add/remove that value from the corresponding list.  I don't think it would be too hard, but it is VBA code.

Thanks for your response.
I did not quite catch when you mentioned it. When I get to the action I realized Holy moly. It takes me forever. the problem is I do know nothing about VBA.
thanks any way.

@msm66  I wrote a very simple VBA that will watch if you click on any cell in the range A2:F10 (you can tweak it) and if so it will toggle the cell to the left of an entry between blank and "1" (you can change that to "X" but I used "1" in this example to toggle the checkboxes that already existed in your example).  To see how it works and tweak it hit Alt-F11 and click on Sheet1 under VBAProject (Fruit Seletion.xlsb)

 

@mtarler 

I am struggling with a problem. I thought you should have some solution for it. 

I attached a new file excel so I can explain the problem better. So as you remember, you gave me a solution for fruit selection. 

Now, as you see in a new file that I sent you, each fruit has their nutrition facts. I am trying to find a solution that Let's say, when you select "green apple" not only transfer the green apple to "selection" column, but also transfer information in cells C2, D2, E2 and G2 (not f2) next to it. 

any thought?

 

(Sry, since I created the file in One drive I do not have developer tab to create the checkbox the way you thought me)

@msm66  I'm confused because I thought in another thread you had already created a sheet with all the checkboxes.  but in this sheet you have none so I assume you will use an "x" to indicate selection.  The easiest way is to use the array formula:

=FILTER(B:G, A:A="x")

in cell k2 and then just hide column O since you don't want to see col. F

you could replace A:A="x" with A:A<>"" to let anything be the selector

you could replace B:G with B:E and then add the same formula for G:G in Column O if you really don't want to just hide column O.

 

@mtarler 

Thanks for your efforts and Sorry for any misunderstanding. 

I am not trying to filter anything. yes, you're right we created all the checkbox lists in the previous thread. I attached more clear excel file. Let me ask my question one more time, hopefully, you can understand me better this time. 

1- I am looking for a formula, that when I select "Orange", all the information next to orange will transfer to selection 1 (as you see in the example, but I copied and pasted it) 

2- The second question is, what if when I select "orange" just "SERVINGS", "UNIT" AND "PROTEIN" amount be transferred. 

 

Thank you, I appreciate it 

@msm66  No, my apologies, I should have included my sample sheet.  FILTER() is just a function that 'grabs' a set of 'filtered' data.  So in this case I was telling it to get all the items in your original table (B:G) and display only those that pass the criteria (i.e. where checked off).  I have updated the checkbox sheet you sent with both methods:  the previous method expanded to include the additional columns and this new FILTER() method.

If you are wondering the +/- of each method I would say a couple basic things are:

a) the 'original' method is compatible with older versions of excel if someone happens to have an old version

b) the 'new' method is self expanding so if the list get bigger, it automatically expands 

 

@mtarler 

Thanks a lot. I did run and it works perfectly. but I am facing another problem now.

 

for example, when you choose "apple" and "orange" **bleep** either one through Rand() (column helper) and enter into "selection". the result will be orange or apple every time you run it. I tried this formula

=INDEX(FILTER(B2:G7,$A$2:$A$7),RANK($H$2,$H$2:$H$7),1)

and it works but the problem I will get an error sometimes because the number of "helper" is more than the selected fruits. 

is there any modification I should add to fix the problem?

In other words, when I select one fruit, give me only 1 rand(), two give me 2 rand().  but the issue is coming from that we do not know how many fruits will be selected so we will be able to assign each selection to each helper. 

I hope you understood what I am trying to do. I attached the file

@msm66  i'm sorry but I don't understand.  I don't understand what the RND() helper column is supposed to do and the attached sheet uses the older formula and seems to work fine.  But then again I don't think I understand something you are trying to do.

@mtarler 

My bad. you are right. I will try again. let me I start off with Rand() and "helper column" confusion. The reason I created the helper column formulated by Rand() is because it gives me the ability to pick random from the final selection list and also the helper column which is rand(). For example if you select "orange" "apple" and "banana" from the list with your formula it will return only these 3 options. The next step is picking a random fruit from the user selection. I have two huge problems here which cause so much time and basically turn all my effort useless. because I have to do lots of manual adjustment. Let me explain to you. step 1: select fruits from the main list through checkbox list. Next the info for each fruit will be transferred to the cells we have selected. step 2: Now I have to create another function to pick a random fruit and transfer it to another cell. Here is exactly the point that lack of efficiency starts kicking in.  In order to do that, I need to write Rand() in a cell and drag it down to the number of fruits that have been selected. for example every time user select 4 fruits I have to create a column with 4 rows with rand(), if the select 1 will be one row and ... the formula I use for this =INDEX(FILTER(B2:G7,$A$2:$A$7),RANK($H$2,$H$2:$H$7),1) Imagine every time I have to change the Rank based on the number of selections. If it is 5 fruits to get either one I have to adjust the rank from RANK($H$2,$H$2:$H$7) to RANK($H$2,$H$2:$H$5) . step 3: I am going to assign these selections of fruits between 7 days a week, so the user can eat those days as a choice of their favorite fruits. In order to do that, let's say X1 to x7 will be day 1 to day 7. so I need to enter the function manually for each cell x1 to x7 because let's say if you just enter the formula in X1 and you want to drag it all the way to x7, unfortunately it won't work then I will type it for each cell separately.Honestly I am so disappointed that I am not sure Excel is the right platform for what I am trying to do. I am looking for a more efficient and automated answer that ideally when I select fruits from selection, randomize it and transfer it straight to the x1 to x7 cells. I waste so much time that it makes more sense to just handle it manually. so frustrating. Please let me know what you think.I hope I explained it more clearly this time. 

@msm66  I'm sorry you are having so much trouble.  I am still not sure I understand all that you are trying to do but 1 tip I think may help is that instead of using x number of RAND() and then finding the RANK() a more common way is to use N*RAND() to scale the RAND over the range you are interested or more specifically for your case something like:

=ROUNDUP(COUNT($J:$J)*RAND(),0)

then to select that RAND selection from Selection 1 table I would use:

=OFFSET($I$1:$N$1,ROUNDUP(COUNT($J:$J)*RAND(),0),0)

With these new dynamic arrays you only need to enter this formula in cell P6 and it will automatically "spill" over into columns Q:U

I'm not understanding the requirements for the 7 days.  If it is the "same" that you want a random selection you can use the same formula for each day.  If you need to 'remove' previously selected fruit (i.e. so you don't each a banana all 7 days) it gets a bit more complicated because then what if you have <7 selected fruits and such.  I hope this helps.

 

 

 

 

@mtarler 

Hi again, sorry for getting back to you late. 

I was working on it and now it is working the way it was supposed to work. I used the Offset function with a few tweaks.  how can I apply OFFSET with Countif while getting randoms to avoid duplicates?

 

I have multi-columns and each column has a different number of rows. the date is in text formats. I am getting the answers but sometimes generate duplicates. 

 

let's say I have in A1:10 the following:

bbb

ccc

ddd

eee

fff

aaa

bbb

ccc

rrr

ttt

 

and in B1;B8

jjj

iii

aaa

bbb

ccc

ddd

aaa

jjj

 

how should I use the OFFSET function that not only gives me a random but also avoids any duplicates? 

 

I used this but it is not working

=COUNTIFS(OFFSET($A$1,COUNTA(A1:A10)*RAND(),0),"")

 

looking for a solution except for data validation or helper column to generate random.  

Thank you 

@msm66  again, I'm not sure exactly how this fits in, but it sounds like you want to take a column and randomly order the unique values.  Here is a formula to do that:

=SORTBY(UNIQUE($A$2:$A$11),RANDARRAY(COUNTA(UNIQUE($A$2:$A$11)),1,,,))

This is a dynamic array and will automatically spill down the needed rows.  Hope that helps.

@mtarler 

Thanks,

I tried it but it looks like give me the same result as =INDEX(A2:A11, RAND()) correct??

 

Let me express my problems one more time in a better way. 

I am trying to get random data from A1: A11 in different cells. let's say D1, D4, and D5. but with no repetition. if you help me with function structure I will figure out the rest to modify the formula. 

Data are in text format and more than one word. I tried to get help from REMOVE DUPLICATE in data tab, it works but still, show me duplicates. 

 

I also used this =OFFSET($A$2,COUNTA(A3:AX11)*RAND(),0)

it is working perfectly the only problem is when I drag down the cell to copy the formula in the number of the rows that I want, sometimes you will see repetition. If you have any idea hoe adjust this formula to avoid duplicates that will the best answer to my question. 

 

Hope I could explain better this time. 

 

1 best response

Accepted Solutions
best response confirmed by msm66 (Brass Contributor)
Solution

@msm66  First off, please don't create new duplicate threads.

As for VBA, of course VBA can do anything you can do in the sheet and more, but unless the VBA code is really small or trivial, the us volunteers here probably won't create it for you.  But in VBA you can create custom forms for data entry (I believe a PC version it is still required to create or edit the form but that they will work on a Mac, but they don't work if using Excel online).  You can also create active modules that will monitor sheet edits and make updates accordingly.

That said, I often try to achieve the functionality in the sheet to avoid problems with others being required to approve the macro functions before the sheet will work (and in some cases they can't).

My prior answer using drop down menus could be modified so each successive drop down list would exclude items already selected.

Another alternative is to add another column next to the list and just have the user type an "x" in that column instead of 'checkboxes' or maybe rank their favorite foods in that column (then you could even sort by that rank).

If you want checkboxes, they point to a cell so if you create that column next to the list and add checkboxes over each cell you can have each checkbox 'point' to that corresponding cell.

 

So if you take the suggestion of adding a column that they rank their favorites then you could use:

=IFERROR(INDEX(A:A,MATCH(ROW()-ROW($C$1),B:B,0)),"")

  assuming the food is in col A, the ranking is in col B, and this list is created in col C with C1 being a header line.

Various tweaks could be made to account for non-sequential and duplicate ranking and such if needed. 

View solution in original post