Discussion Re: HELP WITH VBA OR FORMULA in Excel
https://techcommunity.microsoft.com/t5/excel/help-with-vba-or-formula/m-p/1514305#M68202
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?<BR />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.<BR /><BR />Fri, 10 Jul 2020 13:14:34 GMTmsm662020-07-10T13:14:34ZHELP WITH VBA OR FORMULA
https://techcommunity.microsoft.com/t5/excel/help-with-vba-or-formula/m-p/1512710#M68124
<P><SPAN>Hi ,</SPAN></P><P><SPAN>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.</SPAN><BR /><SPAN>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.</SPAN><BR /><SPAN>so at the end i would have a column selection of your 10 choices of 50. hope the problem is clear.</SPAN></P><P> </P><P><SPAN>thanks </SPAN>a lot</P>Thu, 09 Jul 2020 18:55:52 GMThttps://techcommunity.microsoft.com/t5/excel/help-with-vba-or-formula/m-p/1512710#M68124msm662020-07-09T18:55:52ZRe: HELP WITH VBA OR FORMULA
https://techcommunity.microsoft.com/t5/excel/help-with-vba-or-formula/m-p/1512894#M68129
<P><LI-USER uid="714984"></LI-USER> First off, please don't create new duplicate threads.</P><P>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.</P><P>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).</P><P>My prior answer using drop down menus could be modified so each successive drop down list would exclude items already selected.</P><P>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).</P><P>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.</P><P> </P><P>So if you take the suggestion of adding a column that they rank their favorites then you could use:</P><LI-CODE lang="excel">=IFERROR(INDEX(A:A,MATCH(ROW()-ROW($C$1),B:B,0)),"")</LI-CODE><P> 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.</P><P>Various tweaks could be made to account for non-sequential and duplicate ranking and such if needed. </P>Thu, 09 Jul 2020 20:20:05 GMThttps://techcommunity.microsoft.com/t5/excel/help-with-vba-or-formula/m-p/1512894#M68129mtarler2020-07-09T20:20:05ZRe: HELP WITH VBA OR FORMULA
https://techcommunity.microsoft.com/t5/excel/help-with-vba-or-formula/m-p/1514305#M68202
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?<BR />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.<BR /><BR />Fri, 10 Jul 2020 13:14:34 GMThttps://techcommunity.microsoft.com/t5/excel/help-with-vba-or-formula/m-p/1514305#M68202msm662020-07-10T13:14:34ZRe: HELP WITH VBA OR FORMULA
https://techcommunity.microsoft.com/t5/excel/help-with-vba-or-formula/m-p/1514663#M68225
<P><LI-USER uid="714984"></LI-USER> 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). </P><P>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:</P><P>1. fill in the rest of you lists</P><P>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)</P><P>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)</P><P>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. <U>Bonus</U>: 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.</P>Fri, 10 Jul 2020 15:24:27 GMThttps://techcommunity.microsoft.com/t5/excel/help-with-vba-or-formula/m-p/1514663#M68225mtarler2020-07-10T15:24:27ZRe: HELP WITH VBA OR FORMULA
https://techcommunity.microsoft.com/t5/excel/help-with-vba-or-formula/m-p/1516444#M68301
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.<BR />Thanks alot<BR />Sat, 11 Jul 2020 17:44:42 GMThttps://techcommunity.microsoft.com/t5/excel/help-with-vba-or-formula/m-p/1516444#M68301msm662020-07-11T17:44:42ZRe: HELP WITH VBA OR FORMULA
https://techcommunity.microsoft.com/t5/excel/help-with-vba-or-formula/m-p/1520167#M68577
<P><LI-USER uid="570951"></LI-USER> </P><P>Do you have any solution for multi checkbox list? </P><P>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?</P><P>I search and found some VBA codes but they mostly are for multi checkboxes in one column. like this one:</P><P>but it is not what I need. Any thoughts or tricks you know?</P><DIV class="line number1 index0 alt2">Sub LinkChecks()</DIV><DIV class="line number3 index2 alt2">i = 2</DIV><DIV class="line number4 index3 alt1">For Each cb<SPAN> </SPAN>In ActiveSheet.CheckBoxes</DIV><DIV class="line number5 index4 alt2">cb.LinkedCell = Cells(i,<SPAN> </SPAN>"B").Address</DIV><DIV class="line number6 index5 alt1">i = i + 1</DIV><DIV class="line number7 index6 alt2">Next cb</DIV><DIV class="line number8 index7 alt1">End Sub</DIV>Tue, 14 Jul 2020 02:46:54 GMThttps://techcommunity.microsoft.com/t5/excel/help-with-vba-or-formula/m-p/1520167#M68577msm662020-07-14T02:46:54ZRe: HELP WITH VBA OR FORMULA
https://techcommunity.microsoft.com/t5/excel/help-with-vba-or-formula/m-p/1520190#M68579
<P><LI-USER uid="714984"></LI-USER> Hi there again. I did say "<SPAN>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.</SPAN></P>Tue, 14 Jul 2020 03:10:09 GMThttps://techcommunity.microsoft.com/t5/excel/help-with-vba-or-formula/m-p/1520190#M68579mtarler2020-07-14T03:10:09ZRe: HELP WITH VBA OR FORMULA
https://techcommunity.microsoft.com/t5/excel/help-with-vba-or-formula/m-p/1520196#M68581
Thanks for your response.<BR />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.<BR />thanks any way.<BR />Tue, 14 Jul 2020 03:26:55 GMThttps://techcommunity.microsoft.com/t5/excel/help-with-vba-or-formula/m-p/1520196#M68581msm662020-07-14T03:26:55ZRe: HELP WITH VBA OR FORMULA
https://techcommunity.microsoft.com/t5/excel/help-with-vba-or-formula/m-p/1520288#M68589
<P><LI-USER uid="714984"></LI-USER> 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)</P><P> </P>Tue, 14 Jul 2020 04:54:42 GMThttps://techcommunity.microsoft.com/t5/excel/help-with-vba-or-formula/m-p/1520288#M68589mtarler2020-07-14T04:54:42ZRe: HELP WITH VBA OR FORMULA
https://techcommunity.microsoft.com/t5/excel/help-with-vba-or-formula/m-p/1534258#M69213
<P><LI-USER uid="570951"></LI-USER> </P><P>I am struggling with a problem. I thought you should have some solution for it. </P><P>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. </P><P>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. </P><P>any thought?</P><P> </P><P>(Sry, since I created the file in One drive I do not have developer tab to create the checkbox the way you thought me)</P>Mon, 20 Jul 2020 21:55:16 GMThttps://techcommunity.microsoft.com/t5/excel/help-with-vba-or-formula/m-p/1534258#M69213msm662020-07-20T21:55:16ZRe: HELP WITH VBA OR FORMULA
https://techcommunity.microsoft.com/t5/excel/help-with-vba-or-formula/m-p/1534423#M69217
<P><LI-USER uid="714984"></LI-USER> 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:</P><LI-CODE lang="excel">=FILTER(B:G, A:A="x")</LI-CODE><P>in cell k2 and then just hide column O since you don't want to see col. F</P><P>you could replace A:A="x" with A:A<>"" to let anything be the selector</P><P>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.</P><P> </P>Mon, 20 Jul 2020 23:15:07 GMThttps://techcommunity.microsoft.com/t5/excel/help-with-vba-or-formula/m-p/1534423#M69217mtarler2020-07-20T23:15:07ZRe: HELP WITH VBA OR FORMULA
https://techcommunity.microsoft.com/t5/excel/help-with-vba-or-formula/m-p/1534664#M69223
<P><LI-USER uid="570951"></LI-USER> </P><P>Thanks for your efforts and Sorry for any misunderstanding. </P><P>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. </P><P>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) </P><P>2- The second question is, what if when I select "orange" just "SERVINGS", "UNIT" AND "PROTEIN" amount be transferred. </P><P> </P><P>Thank you, I appreciate it </P>Tue, 21 Jul 2020 02:21:45 GMThttps://techcommunity.microsoft.com/t5/excel/help-with-vba-or-formula/m-p/1534664#M69223msm662020-07-21T02:21:45ZRe: HELP WITH VBA OR FORMULA
https://techcommunity.microsoft.com/t5/excel/help-with-vba-or-formula/m-p/1534703#M69227
<P><LI-USER uid="714984"></LI-USER> 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.</P><P>If you are wondering the +/- of each method I would say a couple basic things are:</P><P>a) the 'original' method is compatible with older versions of excel if someone happens to have an old version</P><P>b) the 'new' method is self expanding so if the list get bigger, it automatically expands </P><P> </P>Tue, 21 Jul 2020 03:42:31 GMThttps://techcommunity.microsoft.com/t5/excel/help-with-vba-or-formula/m-p/1534703#M69227mtarler2020-07-21T03:42:31ZRe: HELP WITH VBA OR FORMULA
https://techcommunity.microsoft.com/t5/excel/help-with-vba-or-formula/m-p/1546191#M69774
<P><LI-USER uid="570951"></LI-USER> </P><P>Thanks a lot. I did run and it works perfectly. but I am facing another problem now.</P><P> </P><P>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</P><P>=INDEX(FILTER(B2:G7,$A$2:$A$7),RANK($H$2,$H$2:$H$7),1)</P><P>and it works but the problem I will get an error sometimes because the number of "helper" is more than the selected fruits. </P><P>is there any modification I should add to fix the problem?</P><P>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. </P><P>I hope you understood what I am trying to do. I attached the file</P>Sun, 26 Jul 2020 04:35:32 GMThttps://techcommunity.microsoft.com/t5/excel/help-with-vba-or-formula/m-p/1546191#M69774msm662020-07-26T04:35:32ZRe: HELP WITH VBA OR FORMULA
https://techcommunity.microsoft.com/t5/excel/help-with-vba-or-formula/m-p/1546968#M69832
<P><LI-USER uid="714984"></LI-USER> 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.</P>Mon, 27 Jul 2020 02:55:43 GMThttps://techcommunity.microsoft.com/t5/excel/help-with-vba-or-formula/m-p/1546968#M69832mtarler2020-07-27T02:55:43ZRe: HELP WITH VBA OR FORMULA
https://techcommunity.microsoft.com/t5/excel/help-with-vba-or-formula/m-p/1550112#M69974
<P><LI-USER uid="570951"></LI-USER> </P><P>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. </P>Tue, 28 Jul 2020 14:38:50 GMThttps://techcommunity.microsoft.com/t5/excel/help-with-vba-or-formula/m-p/1550112#M69974msm662020-07-28T14:38:50ZRe: HELP WITH VBA OR FORMULA
https://techcommunity.microsoft.com/t5/excel/help-with-vba-or-formula/m-p/1552083#M70048
<P><LI-USER uid="714984"></LI-USER> 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:</P><LI-CODE lang="excel">=ROUNDUP(COUNT($J:$J)*RAND(),0)</LI-CODE><P>then to select that RAND selection from Selection 1 table I would use:</P><LI-CODE lang="excel">=OFFSET($I$1:$N$1,ROUNDUP(COUNT($J:$J)*RAND(),0),0)</LI-CODE><P>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</P><P>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.</P><P> </P><P> </P><P> </P><P> </P>Wed, 29 Jul 2020 03:16:45 GMThttps://techcommunity.microsoft.com/t5/excel/help-with-vba-or-formula/m-p/1552083#M70048mtarler2020-07-29T03:16:45ZRe: HELP WITH VBA OR FORMULA
https://techcommunity.microsoft.com/t5/excel/help-with-vba-or-formula/m-p/1575220#M71301
<P><LI-USER uid="570951"></LI-USER> </P><P>Hi again, sorry for getting back to you late. </P><P>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?</P><P> </P><P>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. </P><P> </P><P>let's say I have in A1:10 the following:</P><P>bbb</P><P>ccc</P><P>ddd</P><P>eee</P><P>fff</P><P>aaa</P><P>bbb</P><P>ccc</P><P>rrr</P><P>ttt</P><P> </P><P>and in B1;B8</P><P>jjj</P><P>iii</P><P>aaa</P><P>bbb</P><P>ccc</P><P>ddd</P><P>aaa</P><P>jjj</P><P> </P><P>how should I use the OFFSET function that not only gives me a random but also avoids any duplicates? </P><P> </P><P>I used this but it is not working</P><P>=COUNTIFS(OFFSET($A$1,COUNTA(A1:A10)*RAND(),0),"")</P><P> </P><P>looking for a solution except for data validation or helper column to generate random. </P><P>Thank you </P>Sat, 08 Aug 2020 21:07:05 GMThttps://techcommunity.microsoft.com/t5/excel/help-with-vba-or-formula/m-p/1575220#M71301msm662020-08-08T21:07:05ZRe: HELP WITH VBA OR FORMULA
https://techcommunity.microsoft.com/t5/excel/help-with-vba-or-formula/m-p/1575355#M71307
<P><LI-USER uid="714984"></LI-USER> 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:</P><LI-CODE lang="excel">=SORTBY(UNIQUE($A$2:$A$11),RANDARRAY(COUNTA(UNIQUE($A$2:$A$11)),1,,,))</LI-CODE><P>This is a dynamic array and will automatically spill down the needed rows. Hope that helps.</P>Sat, 08 Aug 2020 22:56:35 GMThttps://techcommunity.microsoft.com/t5/excel/help-with-vba-or-formula/m-p/1575355#M71307mtarler2020-08-08T22:56:35ZRe: HELP WITH VBA OR FORMULA
https://techcommunity.microsoft.com/t5/excel/help-with-vba-or-formula/m-p/1576420#M71358
<P><LI-USER uid="570951"></LI-USER> </P><P>Thanks,</P><P>I tried it but it looks like give me the same result as =INDEX(A2:A11, RAND()) correct??</P><P> </P><P>Let me express my problems one more time in a better way. </P><P>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. </P><P>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. </P><P> </P><P>I also used this =OFFSET($A$2,COUNTA(A3:AX11)*RAND(),0)</P><P>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. </P><P> </P><P>Hope I could explain better this time. </P><P> </P>Sun, 09 Aug 2020 15:07:16 GMThttps://techcommunity.microsoft.com/t5/excel/help-with-vba-or-formula/m-p/1576420#M71358msm662020-08-09T15:07:16ZRe: HELP WITH VBA OR FORMULA
https://techcommunity.microsoft.com/t5/excel/help-with-vba-or-formula/m-p/1579588#M71555
<P><LI-USER uid="714984"></LI-USER> Hi. I've been toying with this latest request/comment and have been struggling with it. First let me clarify what you want. </P><P>Let's say you have the letters a-z in column A</P><P>then in column C you want a formula that will return a <U>random</U> selection from column A and that formula can return multiple UNIQUE random items so that no 2 items in column C are the same (e.g. 'm', 'g', 't', 'b')</P><P>then in column E you want to do the same and have unique random items from column A. </P><P>My question is if this column E must also be unique from column C so 'n', 'c', 'r' would be ok but 't', ... would not because 't' was already selected in column C.</P><P>Also what happens if or when you don't have enough unique items left?</P><P> </P><P>So the formula I gave before with an added index will give you a random selection of unique values from column A:</P><LI-CODE lang="excel">=INDEX(SORTBY(UNIQUE(FILTER($A$1:$A$19,LEN($A$1:$A$19)>1)),RANDARRAY(COUNTA(UNIQUE(FILTER($A$1:$A$19,LEN($A$1:$A$19)>1))),1,,,)),SEQUENCE(5))</LI-CODE><P>where the range in this formula is $A$1:$A$19 and in this case it will only return the first 5 items it finds (see the '5' in the SEQUENCE() function)</P><P>This is DIFFERENT than your COUNTIF formula because no 2 cells in this response will be duplicates but using that COUNTIF and copying it down depending on the RAND() number generated there is a chance for a duplicate.</P><P>Now as for ANOTHER column that also pulls random items from A; if that has to also be UNIQUE from the previous selection of random pick, I'm at a loss. I could do it using VBA and probably using a helper column, but your request to do it without, I couldn't figure out a way. Feel free to post this NEW problem as a new topic and see if anyone else can help. </P><P>That said, if I can convince you to just use a helper column (and just hide the column) then use the formula I gave in my previous response (without the INDEX portion) to create a UNIQUE random list from the original and then just look up a selection from each of the other columns. First column could be direct references and then the second column can count how many are in the first column and OFFSET by that number plus its own row number ... That is very possible.</P><P> </P>Mon, 10 Aug 2020 21:30:23 GMThttps://techcommunity.microsoft.com/t5/excel/help-with-vba-or-formula/m-p/1579588#M71555mtarler2020-08-10T21:30:23ZRe: HELP WITH VBA OR FORMULA
https://techcommunity.microsoft.com/t5/excel/help-with-vba-or-formula/m-p/1579633#M71558
<P><LI-USER uid="570951"></LI-USER> </P><P>Thanks for taking your time and sorry if I gave you a hard time. I do not min if you cursed me a lot. lol</P><P> </P><P>Well, I just used the formula and it is working but there is one problem. </P><P>but let me first point to where I possibly did not explain it well. you are right about your example regarding column A and getting random in Column C. </P><P>That is all I need. i am not going to take random from C and run it in E. </P><P>I have column A TO Z . but I need random data from each column with no duplication into different cells.</P><P>your formula give me 5 rows since we put Sequence 5. I tried 1 and copy it in different cells and I see the duplicate again.</P><P>I have column A including A to Z . but looking for a formula when i copy and paste it let's say C1, C5, D5, D8 AND E3 none of the cells show me the same data. for example show me a, b, c, d, e. no duplicate. </P><P>If you do not have any idea , that fine. i appreciate a lot it so far all the helps you did. </P><P> </P><P> </P>Mon, 10 Aug 2020 22:07:36 GMThttps://techcommunity.microsoft.com/t5/excel/help-with-vba-or-formula/m-p/1579633#M71558msm662020-08-10T22:07:36ZRe: HELP WITH VBA OR FORMULA
https://techcommunity.microsoft.com/t5/excel/help-with-vba-or-formula/m-p/1580046#M71564
<P><LI-USER uid="714984"></LI-USER> Yeah it still sounds like you want that column E in my example even if it is just 1 cell the point is that you want it to be unique from the cell in column C. The problem is that when you copy that formula you have a new RAND() function that is completely independent from the previous and both RAND() could give the same or different answers so sometimes you can get the same output and sometimes you don't. I tried to a bunch of tricks to include the previous RAND selection in the unique formula but they didn't work. But those were 'general' solutions that would be 'flexible'. let me ask if it is correct to make the following 'assumptions' about your data:</P><P>1) the original set of data doesn't have duplicates (i.e. no need to filter it)</P><P>2) you only need a few discreet selections</P><P>Here is an example of the formula I came up with:</P><LI-CODE lang="excel">=INDEX($A$1:$A$11,INDEX(SORT(ROW($A$1:$A$11)*($A$1:$A$11<>$C$1)*($A$1:$A$11<>$D$1),,-1),RAND()*SUM(--($A$1:$A$11<>$C$1)*($A$1:$A$11<>$D$1))))</LI-CODE><P>where original data is $A$1:$A$11 then the 1st random selection was in C1 and the 2nd was in D1 and this is the 3rd selection </P><P>See attached</P><P> </P>Tue, 11 Aug 2020 02:35:46 GMThttps://techcommunity.microsoft.com/t5/excel/help-with-vba-or-formula/m-p/1580046#M71564mtarler2020-08-11T02:35:46ZRe: HELP WITH VBA OR FORMULA
https://techcommunity.microsoft.com/t5/excel/help-with-vba-or-formula/m-p/1591035#M72145
Thank you so much and sorry for the late response. I finally had to use the helper column. it was a pain in the butt but I kinda make it happen.<BR />I might have more questions in the future that will bother you soon again. lol<BR />Thanks again.<BR />Sun, 16 Aug 2020 00:51:37 GMThttps://techcommunity.microsoft.com/t5/excel/help-with-vba-or-formula/m-p/1591035#M72145msm662020-08-16T00:51:37ZRe: HELP WITH VBA OR FORMULA
https://techcommunity.microsoft.com/t5/excel/help-with-vba-or-formula/m-p/1602060#M72601
<P><LI-USER uid="570951"></LI-USER> </P><P>hi again, </P><P>quick question, </P><P>you helped this formula before to generate random data from a column with no duplication. </P><PRE>=INDEX(SORTBY(UNIQUE(FILTER($A$1:$A$19,LEN($A$1:$A$19)>1)),RANDARRAY(COUNTA(UNIQUE(FILTER($A$1:$A$19,LEN($A$1:$A$19)>1))),1,,,)),SEQUENCE(5))</PRE><P>I modify the formula to </P><P>=SORTBY(UNIQUE(AY$3:AY$13),RANDARRAY(COUNTA(UNIQUE(AY$3:AY$13)),1,,,))</P><P> </P><P>where I have data in column AY. The only problem I am facing is seeing "0" in the result. </P><P>in column AY I have (AY3 = " BLUE " AY4 = "RED" ) . The outcome after putting in a cell will be "RED" "BLUE" and a "0". </P><P>I have tens of columns like this, I am getting the result the only issue how to get rid of that "0" so I can see only solid "red" and "BLUE"</P><P> </P><P>Any suggestion? it will be painful if I have to change the reference per column. because they are a lot. </P>Thu, 20 Aug 2020 17:27:10 GMThttps://techcommunity.microsoft.com/t5/excel/help-with-vba-or-formula/m-p/1602060#M72601msm662020-08-20T17:27:10ZRe: HELP WITH VBA OR FORMULA
https://techcommunity.microsoft.com/t5/excel/help-with-vba-or-formula/m-p/1602217#M72615
<P><LI-USER uid="714984"></LI-USER> you decided to drop 2 parts of the formula out:</P><P>the INDEX( ... , SEQUENCE(..)) portion was to automatically fill down the number of unique random values you wanted (i.e. if you just copy your formula down you can get repeats while using the INDEX creates an array output using the same random array)</P><P>and the </P><PRE>FILTER($A$1:$A$19,LEN($A$1:$A$19)>1)</PRE><P>part gets rid of the blanks so you don't get that 0. It is repeated on both parts so the array lengths (without the blanks) are the same. </P><P>Did the original formula not work?</P><P> </P>Thu, 20 Aug 2020 18:29:01 GMThttps://techcommunity.microsoft.com/t5/excel/help-with-vba-or-formula/m-p/1602217#M72615mtarler2020-08-20T18:29:01ZRe: HELP WITH VBA OR FORMULA
https://techcommunity.microsoft.com/t5/excel/help-with-vba-or-formula/m-p/1602446#M72628
<P><LI-USER uid="570951"></LI-USER> </P><P>Thanks</P><P>it is working but for example, if I do not have more than 5 data in the reference column, let's say 2, the result will be the first two cells with data but the rest I received "#ref!"</P><P> </P><P>I am trying to see just the result because, in the end, I have to manually on one delete those errors. taking so much time. ;(</P>Thu, 20 Aug 2020 20:04:20 GMThttps://techcommunity.microsoft.com/t5/excel/help-with-vba-or-formula/m-p/1602446#M72628msm662020-08-20T20:04:20ZRe: HELP WITH VBA OR FORMULA
https://techcommunity.microsoft.com/t5/excel/help-with-vba-or-formula/m-p/1602475#M72630
<P><LI-USER uid="714984"></LI-USER> try wrapping the formula with an IFERROR()</P><LI-CODE lang="excel">=IFERROR(INDEX(SORTBY(UNIQUE(FILTER($A$1:$A$19,LEN($A$1:$A$19)>1)),RANDARRAY(COUNTA(UNIQUE(FILTER($A$1:$A$19,LEN($A$1:$A$19)>1))),1,,,)),SEQUENCE(5)),"")</LI-CODE>Thu, 20 Aug 2020 20:15:36 GMThttps://techcommunity.microsoft.com/t5/excel/help-with-vba-or-formula/m-p/1602475#M72630mtarler2020-08-20T20:15:36ZRe: HELP WITH VBA OR FORMULA
https://techcommunity.microsoft.com/t5/excel/help-with-vba-or-formula/m-p/1603943#M72687
lovely and beautiful<BR />thank you for everything<BR />it works <LI-EMOJI id="lia_winking-face" title=":winking_face:"></LI-EMOJI>Fri, 21 Aug 2020 14:18:15 GMThttps://techcommunity.microsoft.com/t5/excel/help-with-vba-or-formula/m-p/1603943#M72687msm662020-08-21T14:18:15Z