mercredi 31 mai 2017

Randomly select rows under conditions

On my spreadsheet I have the following dataset

ID         pack
1          a
1          b
1          c
2          a
3          c
4          a
4          c

I would like to use a function in excel that selects randomly one of the rows where the ID is not unique (it's not important which one, but I need it to be the only one per ID), otherwise has to repeat the same row.

The result according to example should be as below:

ID         pack
1          b
2          a
3          c
4          c

I tried to add a third column called count (counts the number of times the ID is repeated in the db) and calculating a new field as

IF(**count**=1,1,RANDBETWEEN(0,1))

but in some cases an ID (with multiple packs) gets always 0, in other cases gets always 1.

ID         pack    count    check
1          a       3        1
1          b       3        0       
1          c       3        0
2          a       1        1
3          c       1        1
4          a       2        0
4          c       2        0

Of course, last step of this is a new column with

IF(**check**=1,**pack_name**,0)




Aucun commentaire:

Enregistrer un commentaire