HELP WITH VBA OR FORMULA

msm66  try wrapping the formula with an IFERROR()

=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)),"")

mtarler 

Thanks

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!"

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. ;(

msm66  you decided to drop 2 parts of the formula out:

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)

and the  

FILTER($A$1:$A$19,LEN($A$1:$A$19)>1)

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. 

Did the original formula not work?

mtarler 

hi again, 

quick question, 

you helped this formula before to generate random data from a column with no duplication. 

=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))

I modify the formula to 

=SORTBY(UNIQUE(AY$3:AY$13),RANDARRAY(COUNTA(UNIQUE(AY$3:AY$13)),1,,,))

where I have data in column AY. The only problem I am facing is seeing "0" in the result. 

in column AY  I have (AY3 = " BLUE " AY4 = "RED" ) . The outcome after putting in a cell will be "RED" "BLUE" and a "0". 

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"

Any suggestion? it will be painful if I have to change the reference per column. because they are a lot. 

msm66  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:

1) the original set of data doesn't have duplicates (i.e. no need to filter it)

2) you only need a few discreet selections

Here is an example of the formula I came up with:

=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))))

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   

See attached

mtarler 

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

Well, I just used the formula and it is working but there is one problem. 

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. 

That is all I need. i am not going to take random from C and run it in E. 

I have column A TO Z . but I need random data from each column with no duplication into different cells.

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.

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. 

If you do not have any idea , that fine. i appreciate a lot it so far all the helps you did. 

msm66  Hi.  I've been toying with this latest request/comment and have been struggling with it.  First let me clarify what you want. 

Let's say you have the letters a-z in column A

then in column C you want a formula that will return a random 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')

then in column E you want to do the same and have unique random items from column A. 

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.

Also what happens if or when you don't have enough unique items left?

So the formula I gave before with an added index will give you a random selection of unique values from column A:

=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))

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)

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.

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. 

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.

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. 

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 

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  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 

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 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 

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