Good night! First time posting a question on the forum, in the past it has helped me a lot to clarify several doubts, so I think it's appropriate to ask for help or advice here.
I have a spreadsheet that I use as follows:
The ABC and EFG tables serve the same function, manually entering data containing the product ranking, name and volume (more volume equals higher ranking).
The IJKL and MOPQ tables have the same function, the I and N column generate random numbers that I then use in the J and O columns to generate a list of 30 random products. In columns K and P, I order the numbers obtained from lowest to highest ranking (highest to lowest volume) and in columns L and Q I look for the name of the corresponding product.
The formulas I use are the following:
Columns I and N) =rand()
Columns J and O) =INDEX($A$2:$A$51,RANK.EQ(I2,$I$2:$I$51)+COUNTIF($I$2:I2,I2)-1,1)
Columns K and P) =SMALL($J$2:$J$31.1)
Columns L and Q) =VLOOKUP(K2,$A$2:$C$51,2)
Basically now I need to generate the same random list (30 items), but using the following volume criteria:
100,000,000 to 999,999,999 (1 item)
10,000,000 to 99,999,999 (1 item)
1,000,000 to 9,999,999 (2 items)
100,000 to 999,999 (4 items)
10,000 to 99,999 (4 items)
1,000 to 9,999 (8 items)
100 to 999 (8 items)
10 to 99 (1 item)
1-9 (1 item)
I mean:
1 random item from all those with volume between 100,000,000 to 999,999,999
1 random item from all those with volume between 10,000,000 to 99,999,999
2 random items from all those with volume between 1,000,000 to 9,999,999
etc.
A friend told me that I could create dynamic tables to do the filtering, but I don't have enough technical knowledge.
Could you guys help me out? Thanks in advance!
I have a spreadsheet that I use as follows:
The ABC and EFG tables serve the same function, manually entering data containing the product ranking, name and volume (more volume equals higher ranking).
The IJKL and MOPQ tables have the same function, the I and N column generate random numbers that I then use in the J and O columns to generate a list of 30 random products. In columns K and P, I order the numbers obtained from lowest to highest ranking (highest to lowest volume) and in columns L and Q I look for the name of the corresponding product.
The formulas I use are the following:
Columns I and N) =rand()
Columns J and O) =INDEX($A$2:$A$51,RANK.EQ(I2,$I$2:$I$51)+COUNTIF($I$2:I2,I2)-1,1)
Columns K and P) =SMALL($J$2:$J$31.1)
Columns L and Q) =VLOOKUP(K2,$A$2:$C$51,2)
Basically now I need to generate the same random list (30 items), but using the following volume criteria:
100,000,000 to 999,999,999 (1 item)
10,000,000 to 99,999,999 (1 item)
1,000,000 to 9,999,999 (2 items)
100,000 to 999,999 (4 items)
10,000 to 99,999 (4 items)
1,000 to 9,999 (8 items)
100 to 999 (8 items)
10 to 99 (1 item)
1-9 (1 item)
I mean:
1 random item from all those with volume between 100,000,000 to 999,999,999
1 random item from all those with volume between 10,000,000 to 99,999,999
2 random items from all those with volume between 1,000,000 to 9,999,999
etc.
A friend told me that I could create dynamic tables to do the filtering, but I don't have enough technical knowledge.
Could you guys help me out? Thanks in advance!