...

Get same rows from a table

 

The Excel table below has 4 columns. If we regard columns from 2nd to the 4th as a whole, there are duplicates, as shown below:

A

B

C

D

1

#

Product

Non Conf

Date

2

1

Apple

Worms

4-Mar-24

3

3

Apple

Worms

4-Mar-24

4

2

Pear

Worms

4-Mar-24

5

7

Pear

Worms

5-Mar-24

6

11

Pear

Worms

6-Mar-24

7

10

Orange

Worms

6-Mar-24

8

12

Orange

Worms

6-Mar-24

9

9

Apple

Rotten

6-Mar-24

10

13

Apple

Rotten

7-Mar-24

11

4

Pear

Rotten

4-Mar-24

12

5

Orange

Rotten

5-Mar-24

13

6

Orange

Rotten

5-Mar-24

14

8

Orange

Rotten

6-Mar-24

15

14

Orange

Rotten

7-Mar-24

We want to select the same rows to form a new table while retaining the original order.

F

G

H

I

1

#

Product

Non Conf

Date

2

1

Apple

Worms

4-Mar-24

3

3

Apple

Worms

4-Mar-24

4

10

Orange

Worms

6-Mar-24

5

12

Orange

Worms

6-Mar-24

6

5

Orange

Rotten

5-Mar-24

7

6

Orange

Rotten

5-Mar-24

Enter the following formula in SPL XLL:

=spl("=?.group@u(~.to(2,)).select(~.len()>1).conj()",A2:D15)

Picture1png

group()function groups the table rows; @u option enables keeping the original order; ~ is the current member; and to(2,) gets child members of the current member from the 2nd to the last.

Source:https://stackoverflow.com/questions/78105588/vba-collect-consecutive-similar-cells-in-the-row


 
 •  66 View  •