...

Split & group text and perform distinct on each group

 

Below is an irregular categorized detail table. Column A and column B are categories and both have duplicate values. Column C contains detail data consisting of strings separated by "comma+space", and there are duplicates among the string values.

A

B

C

1

Project #

Project Step

Participant(s)

2

100

101

John J

3

100

102

Dave M, Phil X

4

100

102

Dave M, Lisa P, John J

5

100

103

Phil X, Lisa P

6

100

104

Dave M

7

200

201

John J, Lisa P, Alice T

8

200

201

Lisa P, Alice T

9

200

202

Dave M, Lisa P, John J

10

200

203

Phil X, Lisa P

11

200

204

Dave M, Phil X

12

200

204

Dave M, Lisa P, John J

Task: Split detail data in each category, group them by category, get unique values of each group and concatenate them using "comma+space".


E

F

G

1

Project #

Project Step

List Participant(s)

2

100

101

John J

3

100

102

Dave M, John J, Lisa P, Phil X

4

100

103

Lisa P, Phil X

5

100

104

Dave M

6

200

201

Alice T, John J, Lisa P

7

200

202

Dave M, John J, Lisa P

8

200

203

Lisa P, Phil X

9

200

204

Dave M, John J, Lisa P, Phil X

Use SPL XLL to enter the following formula:

=spl("=E@b(?.group(~1,~2;~.conj(~3.split@ct()).id().concat("","")))",A2:C12)

Picture1png

group()function groups rows and handles data in each group; ~1 represents the first sub-member of the current member; split@ct splits each string by comma and performs trim operation to remove spaces at both sides; id() removes duplicate members. E@b converts the Excel table to a sequence without titles.

Source:https://www.reddit.com/r/excel/comments/1cu1ik3/generate_a_duplicatefree_list_of_names_separated/


 
 •  112 View  •