Search code examples
excelexcel-formulaexcel-2007vba

Reporting Cell based on number from another cell

I have a group of 400 proxies in column C. I would like a formula that would report a random list of the proxies (from C) to column A and B based on the number in cell A1 and B1

A1  B1 C1:C400

10  20 Proxy List

So based on the number in Row 1 I would like a list to pop up of that many proxies and in a random order.

So if A1 is 10 I would like 10 proxies from the proxy like to pop up starting in A2. Also I would like it so that these proxies are not used more than one time so there will not be the same proxies in Row A and B.

In each column I would like the proxies from AF to be placed there based on the amounts in row 1 and I would like these proxies to only be used one time.

Solution

  • One way to accomplish this without VBA is to use the formula you have an conditional formatting.

    Since RANDBETWEEN is a volatile function you can use conditional formatting to show any duplicate values that show up and then just re-calculate the list until you do not see duplicates.

    See below:

    enter image description here

    A2 and B2 have the formula: =IF(ROW()<=A$1,INDEX($C:$C,RANDBETWEEN(1,COUNTA($C:$C)),1),"") change to B$1 for column B

    Drag these formulas down for as many rows as you think you may need to cover for a given value in A1 and / or B1.

    Then apply the following rules for Duplicate Values in Conditional Formatting Wizard. First rule is for cells that contain blanks (make sure to step Stop If True - this will stop blank cells from showing as duplicates`.

    enter image description here

    Then if duplicates exist in column A and/or B, you will see them easily:

    enter image description here

    CAVEAT - this works well with smaller numbers in A1 and B1, but fails a bit with larger numbers of IP addresses, since the randomness of the formula creates more duplicates.