RaqForum 41 No.
137 View  • 
     ...

Get a random row from each group

 

In the Excel table below, column A is the grouping field and column B contains detail data.

A

B

1

Group

Name

2

A

John

3

A

Joe

4

A

Ann

5

A

Susan

6

A

James

7

A

Mary

8

A

L .orraine

9

B

Joseph

10

B

Sinead

11

B

Michelle

12

B

Breege

13

B

Tom

14

B

Francis

15

B

Conan

16

B

Cait

17

B

Ronan

18

B

Deirdre

19

B

Aoife

20

B

Sile

21

B

Sarah

22

C

Lisa

23

C

Micky

24

C

Pat

25

D

Miles

26

D

Olivia

27

D

Avril

28

D

Conor

29

D

Jane

30

D

Robyn

31

D

June

We want to get a row from each group randomly.

E

F

1

Group

Name

2

A

Mary

3

B

Sarah

4

C

Micky

5

D

Miles

Use SPL XLL to do this:

=spl("=E(?).sort(rand()).group@1(Group)",A1:B31)

Picture1png

E()function parses the range as a table. sort(rand()) arranges rows randomly. group@1 groups rows and gets the 1st record from each group.

Source:https://www.reddit.com/r/excel/comments/1cn10qm/randomly_selecting_one_name_from_each_group_in/


 
 •  137 View  •