...

In Excel, get the last row of each group

 
excel(115)

In the Excel table below, the first two columns are two-layer grouping columns and the rest of the two columns contain detail data.

A

B

C

D

1

C

M1

1

11

2

C

M1

2

12

3

C

M1

3

13

4

C

M1

4

14

5

C

M2

5

15

6

C

M2

6

16

7

B

M1

1

21

8

B

M1

2

22

9

B

M2

3

23

10

A

M1

1

31

11

A

M3

2

32

12

A

M3

3

33

13

A

M3

4

34

The task is to get the last row of each subgroup:

A

B

C

D

1

C

M1

4

14

2

C

M2

6

16

3

B

M1

2

22

4

B

M2

3

23

5

A

M1

1

31

6

A

M3

4

34

Use SPL XLL:

=spl("=?.rvs().group@o1(~(1),~(2)).rvs()",A1:D13)

Picture1png

The rvs()function sorts rows in a reverse order. The group() function groups rows, during which @o option creates a new group whenever a new grouping column value appears and @1 option gets the first row of each subgroup; ~ represents the current member.

Source:https://stackoverflow.com/questions/78387059/using-office365-excel-array-formulas-how-to-remove-duplicates-keeping-the-last


excel(115)
 
 •  168 View  •