HELP WITH VBA OR FORMULA

[msm66]

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

[#2]

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

[#3]

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

[#4]

@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

[#5]

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.
I might have more questions in the future that will bother you soon again. lol
Thanks again.

[#6]

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

[#7]

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

[#8]

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

[#9]

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

[#10]

lovely and beautiful
thank you for everything
it works ;)