...

In Excel, split each row of text in each group and calculate intersection

 

Column A is the grouping column. Each group corresponds multiple rows that contain comma-separated texts in column B:


A

B

1

1

4,9,3,1,7

2

1

7,5,2,9,4,

3

1

8,5,7,9

4

1

4,1,3,8,9,7

5

2

4,8,2

6

2

1,2,7,8

7

2

3,1,8,6

8

2

6,3,4,9,2,5,8

9

2

0,4,8,6,9

10

3

8,3,4,7,2

11

3

2,4,6,9,3

12

4

1,8,2,5

The task is to split each row of text in each group, calculate intersection of row of texts in each group and concatenate the result, and insert it to the first row in each group under column D:

A

B

C

D

1

1

4,9,3,1,7

9,7

2

1

7,5,2,9,4,

3

1

8,5,7,9

4

1

4,1,3,8,9,7

5

2

4,8,2

8

6

2

1,2,7,8

7

2

3,1,8,6

8

2

6,3,4,9,2,5,8

9

2

0,4,8,6,9

10

3

8,3,4,7,2

3,4,2

11

3

2,4,6,9,3

12

4

1,8,2,5

1,8,2,5

Use SPL XLL to enter the following formula:

=spl("=?.group(~(1)).conj([~.isect(~(2).split@c()).concat@c()].pad(null,~.len()))",A1:B12)

Picture1png

group(~(1)) groups rows by column A; split()split each value of column B in each group; isect() calculates the intersection; concat()concatenates the result as a string; and pad() patches null values to align with column A.

Source:https://stackoverflow.com/questions/78400799/how-to-apply-a-formula-to-only-certain-functions


 
 •  192 View  •