Excel: Random select within table that meets with a condition

My issue is the following:

I have a list of references, addresses of the reference and the city of the reference (the sample is shared below).

My table has roughly 7000 lines, the references are all unique, some of them are in the same city and share the same address.

I will like to know if it is possible to select 700 or more references randomly but for every address create a limit 5 references per address.

I don´t mind having random duplicates but I cannot have on a specific city 55 out of 700 references of the same address / street.

Can some one please give me some pointers?

Sample:

https://drive.google.com/file/d/1IhcVWtPqu2TiGH1xDFGBUauBm6w1OEyM/view?usp=sharing

+---------------------------+-----------------------------------+---------------+
|   Reference               |   Address                         |     City      |
+---------------------------+-----------------------------------+---------------+
|   140120.00001.01.01.01   |   EN 2-4                          |   ABRANTES    |
+---------------------------+-----------------------------------+---------------+
|   140120.00004.01.02.01   |   EN 2-4                          |   ABRANTES    |
+---------------------------+-----------------------------------+---------------+
|   140120.00006.01.01.01   |   R. NOSSA SENHORA DA CONCEIÇÃO   |   ABRANTES    |
+---------------------------+-----------------------------------+---------------+
|   140120.00010.01.01.01   |   AV. DAS FORÇAS ARMADAS          |   ABRANTES    |
+---------------------------+-----------------------------------+---------------+
|   140120.00010.01.02.01   |   AV. DAS FORÇAS ARMADAS          |   ABRANTES    |
+---------------------------+-----------------------------------+---------------+
|   140120.00012.01.01.01   |   AV. 25 DE ABRIL                 |   ABRANTES    |
+---------------------------+-----------------------------------+---------------+
|   140120.00013.01.02.01   |   AV. DAS FORÇAS ARMADAS          |   ABRANTES    |
+---------------------------+-----------------------------------+---------------+
|   140120.00015.01.01.01   |   AV. 25 DE ABRIL                 |   ABRANTES    |
+---------------------------+-----------------------------------+---------------+
|   140120.00016.01.01.01   |   AV. DAS FORÇAS ARMADAS          |   ABRANTES    |
+---------------------------+-----------------------------------+---------------+
|   140120.00016.01.02.01   |   AV. DAS FORÇAS ARMADAS          |   ABRANTES    |
+---------------------------+-----------------------------------+---------------+
|   140120.00018.01.01.01   |   R. SOLANO DE ABREU              |   ABRANTES    |
+---------------------------+-----------------------------------+---------------+
|   140120.00019.01.01.01   |   AV. 25 DE ABRIL                 |   ABRANTES    |
+---------------------------+-----------------------------------+---------------+
|   140120.00020.01.01.01   |   AV. 25 DE ABRIL                 |   ABRANTES    |
+---------------------------+-----------------------------------+---------------+
|   140120.00025.01.01.01   |   EN 3                            |   ABRANTES    |
+---------------------------+-----------------------------------+---------------+
|   010121.00038.01.01.01   |   R. JOAQUIM VALENTE DE ALMEIDA   |   AGUEDA      |
+---------------------------+-----------------------------------+---------------+
|   010121.00038.01.02.01   |   R. JOAQUIM VALENTE DE ALMEIDA   |   AGUEDA      |
+---------------------------+-----------------------------------+---------------+
|   010121.00042.01.01.01   |   R. 12 DE DEZEMBRO               |   AGUEDA      |
+---------------------------+-----------------------------------+---------------+
|   010121.00057.01.01.01   |   R. 15 DE AGOSTO                 |   AGUEDA      |
+---------------------------+-----------------------------------+---------------+
|   010121.00058.01.01.01   |   R. MANUEL SOUSA CARNEIRO        |   AGUEDA      |
+---------------------------+-----------------------------------+---------------+
|   010121.00060.01.01.01   |   AV. 25 DE ABRI              L   |   AGUEDA      |
+---------------------------+-----------------------------------+---------------+
|   010121.00060.01.02.01   |   AV. 25 DE ABRIL                 |   AGUEDA      |
+---------------------------+-----------------------------------+---------------+
|   010121.00061.01.01.01   |   R. TEN.-COR. ALBANO MELO        |   AGUEDA      |
+---------------------------+-----------------------------------+---------------+
|   010121.00063.01.01.01   |   AV. 25 DE ABRIL                 |   AGUEDA      |
+---------------------------+-----------------------------------+---------------+
|   010121.00066.01.01.01   |   AV. 25 DE ABRIL                 |   AGUEDA      |
+---------------------------+-----------------------------------+---------------+
|   010121.00069.01.01.01   |   R. GUSTAVO PIMENTA              |   AGUEDA      |
+---------------------------+-----------------------------------+---------------+
|   010121.00071.01.01.01   |   R. JOSÉ DE SUCENA               |   AGUEDA      |
+---------------------------+-----------------------------------+---------------+
|   010124.00062.01.02.01   |   R. CABEDO E LENCASTRE           |   AGUEDA      |
+---------------------------+-----------------------------------+---------------+
|   000150.11559.01.01.01   |   PQ. DE ESTACIONAMENTO           |   ALGARVE SHOPPING    |
+---------------------------+-----------------------------------+---------------+
|   080106.00085.01.02.01   |   AV. DOS DESCOBRIMENTOS          |   ALBUFEIRA   |
+---------------------------+-----------------------------------+---------------+
|   080106.00089.01.01.01   |   AV. DOS DESCOBRIMENTOS          |   ALBUFEIRA   |
+---------------------------+-----------------------------------+---------------+
|   080106.00089.01.02.01   |   AV. DOS DESCOBRIMENTOS          |   ALBUFEIRA   |
+---------------------------+-----------------------------------+---------------+
|   080106.00091.01.02.01   |   AV. DOS DESCOBRIMENTOS          |   ALBUFEIRA   |
+---------------------------+-----------------------------------+---------------+
|   080106.00092.01.01.01   |   AV. DOS DESCOBRIMENTOS          |   ALBUFEIRA   |
+---------------------------+-----------------------------------+---------------+
|   080106.00092.01.02.01   |   AV. DOS DESCOBRIMENTOS          |   ALBUFEIRA   |
+---------------------------+-----------------------------------+---------------+

Example of final selection of 17 references out of 35 of the sample:

In this sample of 34 lines, i might want 20 references i dont mind have 2 duplicates but 5 out of 20 "AV. DOS DESCOBRIMENTOS" thats is an issue. so in this case i would like to have a contidtion of maximum 2 identical addresses per city

+---------------------------+-----------------------------------+---------------+
|   Reference               |   Address                         |     City      |
+---------------------------+-----------------------------------+---------------+
|   140120.00001.01.01.01   |   EN 2-4                          |   ABRANTES    |
+---------------------------+-----------------------------------+---------------+
|   140120.00006.01.01.01   |   R. NOSSA SENHORA DA CONCEIÇÃO   |   ABRANTES    |
+---------------------------+-----------------------------------+---------------+
|   140120.00010.01.01.01   |   AV. DAS FORÇAS ARMADAS          |   ABRANTES    |
+---------------------------+-----------------------------------+---------------+
|   140120.00012.01.01.01   |   AV. 25 DE ABRIL                 |   ABRANTES    |
+---------------------------+-----------------------------------+---------------+
|   140120.00018.01.01.01   |   R. SOLANO DE ABREU              |   ABRANTES    |
+---------------------------+-----------------------------------+---------------+
|   140120.00025.01.01.01   |   EN 3                            |   ABRANTES    |
+---------------------------+-----------------------------------+---------------+
|   010121.00038.01.02.01   |   R. JOAQUIM VALENTE DE ALMEIDA   |   AGUEDA      |
+---------------------------+-----------------------------------+---------------+
|   010121.00042.01.01.01   |   R. 12 DE DEZEMBRO               |   AGUEDA      |
+---------------------------+-----------------------------------+---------------+
|   010121.00057.01.01.01   |   R. 15 DE AGOSTO                 |   AGUEDA      |
+---------------------------+-----------------------------------+---------------+
|   010121.00058.01.01.01   |   R. MANUEL SOUSA CARNEIRO        |   AGUEDA      |
+---------------------------+-----------------------------------+---------------+
|   010121.00060.01.01.01   |   AV. 25 DE ABRIL                 |   AGUEDA      |
+---------------------------+-----------------------------------+---------------+
|   010121.00069.01.01.01   |   R. GUSTAVO PIMENTA              |   AGUEDA      |
+---------------------------+-----------------------------------+---------------+
|   010121.00071.01.01.01   |   R. JOSÉ DE SUCENA               |   AGUEDA      |
+---------------------------+-----------------------------------+---------------+
|   010124.00062.01.02.01   |   R. CABEDO E LENCASTRE           |   AGUEDA      |
+---------------------------+-----------------------------------+---------------+
|   000150.11559.01.01.01   |   PQ. DE ESTACIONAMENTO           |   ALGARVE     |
+---------------------------+-----------------------------------+---------------+
|   080106.00085.01.02.01   |   AV. DOS DESCOBRIMENTOS          |   ALBUFEIRA   |
+---------------------------+-----------------------------------+---------------+
|   080106.00089.01.01.01   |   AV. DOS DESCOBRIMENTOS          |   ALBUFEIRA   |
+---------------------------+-----------------------------------+---------------+

Likwit

Posted 2020-02-05T18:00:48.377

Reputation: 11

I know that i can use RAND to generate a random number per line, am lost when it come to create a condition on the address. – Likwit – 2020-02-05T18:02:18.650

Welcome to Super User! Some diagrams of your data would help us understand your question. Please [edit] your question to include some example (mock) data (before and after). See Format Text as a Table for a web utility that will help you to create a nice data table you can paste into your question.

– DavidPostill – 2020-02-05T18:06:24.987

In this sample of 34 lines, i might want 20 references i dont mind have 2 duplicates but 5 out of 20 "AV. DOS DESCOBRIMENTOS" thats is and is and issue. so in this case i would like to have a contidtion of maximum 2 identical addresses per city – Likwit – 2020-02-05T18:18:47.983

Any sample data should be [edit]ed into your question. I already told you how to do that. – DavidPostill – 2020-02-05T18:25:30.223

It's ok likwit, just proceed, to add the last comment info into the original question.. | If you (likwit) don't mind.. please also shared what you had tried and the research you had done. It'll motivate others to assist. | IMHO, your question is much clearer now than the original one. – p._phidot_ – 2020-02-05T18:32:06.657

I cannot post as answer as the question is still locked. | the proposed idea : just use RANDBETWEEN(1,7000) to generate a list of 700 numbers, if there is duplicate redo or generate more. Then use it with index match to extract the 'shortlisted' list. With that list.. do countif() for every line, with the condition you specified above. I think that shall do it. – p._phidot_ – 2020-02-05T19:10:28.930

Excel: Random select within table that meets with a condition

No answers