07-09-2020 11:55 AM
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
08-10-2020 02:30 PM
@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.
08-10-2020 03:07 PM
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.
08-10-2020 07:35 PM
@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
08-15-2020 05:51 PM
08-20-2020 10:27 AM
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.
08-20-2020 11:29 AM
@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?
08-20-2020 01:04 PM
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. ;(
08-20-2020 01:15 PM
@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)),"")
08-21-2020 07:18 AM