Filter specific rows from a table

vladimiratanasiu · Dec 27, 2024

Hello!

I have a larger .csv table, with quotes of cereals. The data are structured based on the weeks of a year (week 51, 50, 49, 48 etc./2023), type of cereals (wheat/feed barely) and regions (south, west, east, FOB Constanta). As it's seen, the reference period is common for the two products (rows 1, 12, 23, 34 etc), but the regions are repeated separately, for each type of cereals. I need a solution, to filter and show only the rows with the period of reference (rows 1, 12, 23, 34 etc.), and the specific values for the WHEAT from the SOUTH region ( rows 5, 16, 27, 38 etc.).
Thank you in advance!

Cotatii-Cereale-2023-1(1).csv

A

B

C

1

WEEK 51/2023 (WEEK 21 - 27 DECEMBER 2023)

#colspan#

2

DELIVERY AREA/PRODUCT

WHEAT (RON/tone)

Var. %

3

WEST

885

3.39

4

EAST

-

0

5

SOUTH

760

-18.1

6

FOB CONSTANTA

1101

1.47

7

DELIVERY AREA/PRODUCT

FEED BARELY (RON/tone)

Var. %

8

WEST

-

0

9

EAST

-

0

10

SOUTH

-

0

11

FOB CONSTANTA

-

0

12

WEEK 50/2023 (WEEK 14 - 20 DECEMBER 2023)

#colspan#

13

DELIVERY AREA/PRODUCT

WHEAT (RON/tone)

Var. %

14

WEST

856

4.01

15

EAST

1018

3.98

16

SOUTH

928

2.09

17

FOB CONSTANTA

1085

-4.66

18

DELIVERY AREA/PRODUCT

FEED BARELY (RON/tone)

Var. %

19

WEST

650

-11.2

20

EAST

833

0.12

21

SOUTH

800

12.2

22

FOB CONSTANTA

-

0

23

WEEK 49/2023 (WEEK 07 - 13 DECEMBER 2023)

#colspan#

24

DELIVERY AREA/PRODUCT

WHEAT (RON/tone)

Var. %

25

WEST

823

-10.05

26

EAST

979

-2.59

27

SOUTH

909

0.22

28

FOB CONSTANTA

1138

0.09

29

DELIVERY AREA/PRODUCT

FEED BARELY (RON/tone)

Var. %

30

WEST

732

4.57

31

EAST

832

2.09

32

SOUTH

713

-8.59

33

FOB CONSTANTA

-

0

34

WEEK 48/2023 (WEEK 30 NOVEMBER - 06 DECEMBER 2023)

#colspan#

35

DELIVERY AREA/PRODUCT

WHEAT (RON/tone)

Var. %

36

WEST

915

0.55

37

EAST

1005

2.24

38

SOUTH

907

1.23

39

FOB CONSTANTA

1137

-0.52

40

DELIVERY AREA/PRODUCT

FEED BARELY (RON/tone)

Var. %

41

WEST

-

0

42

EAST

815

-5.89

43

SOUTH

780

-8.45

44

FOB CONSTANTA

-

0

Cotatii-Cereale-2023-1(1)

hagia_sofia · Dec 27, 2024

Hello, provided that the data has the same structure as depicted above you could test the following:

Excel Formula:

=LET(
a,A1:A44,
b,B1:B44,
c,A1:C44,
d,TOCOL(IF(SEQUENCE(,8),FILTER(a,ISNUMBER(SEARCH("WEEK",a))))),
e,TOCOL(IF(SEQUENCE(,4),FILTER(b,ISNUMBER(SEARCH("RON/tone",b))))),
f,FILTER(c,NOT(ISNUMBER(SEARCH("<strong>",a)))),
g,HSTACK(d,e,f),
FILTER(g,(CHOOSECOLS(g,2)="<strong><em>WHEAT (RON/tone)</em></strong>")*(CHOOSECOLS(g,3)="SOUTH")))

vladimiratanasiu · Dec 28, 2024

hagia_sofia said:
Hello, provided that the data has the same structure as depicted above you could test the following:

Excel Formula:

=LET( a,A1:A44, b,B1:B44, c,A1:C44, d,TOCOL(IF(SEQUENCE(,8),FILTER(a,ISNUMBER(SEARCH("WEEK",a))))), e,TOCOL(IF(SEQUENCE(,4),FILTER(b,ISNUMBER(SEARCH("RON/tone",b))))), f,FILTER(c,NOT(ISNUMBER(SEARCH("",a)))), g,HSTACK(d,e,f), FILTER(g,(CHOOSECOLS(g,2)="WHEAT (RON/tone)")*(CHOOSECOLS(g,3)="SOUTH")))

Thank you for the answer. I applied your formula, but it generates only partially my necessary results. I need to be included the whole content from the marked rows with South region (e.g A5:C5), and also the rows with the periods (e.g. A1:C1). I attach the filtered content from the above table, exactly as I need it to be shown. Could you adjust / change your formula, for it to lead to the necessary data? Thank you!

Book1

A

B

C

1

WEEK 51/2023 (WEEK 21 - 27 DECEMBER 2023)

#colspan#

5

SOUTH

760

-18.1

12

WEEK 50/2023 (WEEK 14 - 20 DECEMBER 2023)

#colspan#

16

SOUTH

928

2.09

23

WEEK 49/2023 (WEEK 07 - 13 DECEMBER 2023)

#colspan#

27

SOUTH

909

0.22

34

WEEK 48/2023 (WEEK 30 NOVEMBER - 06 DECEMBER 2023)

#colspan#

38

SOUTH

907

1.23

Sheet1

hagia_sofia · Dec 28, 2024

Many thanks for the feedback. If the data follow the same pattern as shown above would this work then:

Excel Formula:

=LET(
a,A1:C44,
b,ROWS(a),
FILTER(a,(ISNUMBER(XLOOKUP(SEQUENCE(b),SEQUENCE(4,,1,11),SEQUENCE(4,,1,11))))+(ISNUMBER(XLOOKUP(SEQUENCE(b),SEQUENCE(4,,5,11),SEQUENCE(4,,5,11))))))

djclements · Dec 28, 2024

Another couple of possibilities you could try:

Excel Formula:

=FILTER(A1:C1000,(B1:B1000="#colspan#")+((A1:A1000="SOUTH")*ISNUMBER(SEARCH("WHEAT",SCAN("",B1:B1000,LAMBDA(a,v,IF(LEFT(v)="<",v,a)))))))

-OR-

Excel Formula:

=LET(rng,A1:C44,seq,SEQUENCE(ROWS(rng)/11,,,11),CHOOSEROWS(rng,TOCOL(HSTACK(seq,seq+4))))

Adjust the range references as needed.

Cubist · Dec 29, 2024

Another option:

Excel Formula:

=CHOOSEROWS(A1:C44,1,SCAN(1,4+3*MOD(SEQUENCE(ROWS(A1:A44)/6)-1,2),SUM))

vladimiratanasiu · Dec 29, 2024

Thank

hagia_sofia said:
Many thanks for the feedback. If the data follow the same pattern as shown above would this work then:

Excel Formula:

=LET( a,A1:C44, b,ROWS(a), FILTER(a,(ISNUMBER(XLOOKUP(SEQUENCE(b),SEQUENCE(4,,1,11),SEQUENCE(4,,1,11))))+(ISNUMBER(XLOOKUP(SEQUENCE(b),SEQUENCE(4,,5,11),SEQUENCE(4,,5,11))))))

Thank you, hagia_sofia, for your effort ! Unfortunately, your formula can cover only the weeks 48-51, mentioned in the posted table. If I try to extend it to the weeks 1-47/2023, it can't generate results for the rest of them.

Cotatii-Cereale-2022.csv

A

B

C

D

E

F

1

WEEK 51/2023 (WEEK 21 - 27 DECEMBER 2023)

#colspan#

WEEK 51/2023 (WEEK 21 - 27 DECEMBER 2023)

#colspan#

2

DELIVERY AREA/PRODUCT

WHEAT (RON/tone)

Var. %

SOUTH

760

-18.1

3

WEST

885

3.39

WEEK 50/2023 (WEEK 14 - 20 DECEMBER 2023)

#colspan#

4

EAST

-

0

SOUTH

928

2.09

5

SOUTH

760

-18.1

WEEK 49/2023 (WEEK 07 - 13 DECEMBER 2023)

#colspan#

6

FOB CONSTANTA

1101

1.47

SOUTH

909

0.22

7

DELIVERY AREA/PRODUCT

FEED BARELY (RON/tone)

Var. %

WEEK 48/2023 (WEEK 30 NOVEMBER - 06 DECEMBER 2023)

#colspan#

8

WEST

-

0

SOUTH

907

1.23

9

EAST

-

0

10

SOUTH

-

0

11

FOB CONSTANTA

-

0

12

WEEK 50/2023 (WEEK 14 - 20 DECEMBER 2023)

#colspan#

13

DELIVERY AREA/PRODUCT

WHEAT (RON/tone)

Var. %

14

WEST

856

4.01

15

EAST

1018

3.98

16

SOUTH

928

2.09

17

FOB CONSTANTA

1085

-4.66

18

DELIVERY AREA/PRODUCT

FEED BARELY (RON/tone)

Var. %

19

WEST

650

-11.2

20

EAST

833

0.12

21

SOUTH

800

12.2

22

FOB CONSTANTA

-

0

23

WEEK 49/2023 (WEEK 07 - 13 DECEMBER 2023)

#colspan#

24

DELIVERY AREA/PRODUCT

WHEAT (RON/tone)

Var. %

25

WEST

823

-10.05

26

EAST

979

-2.59

27

SOUTH

909

0.22

28

FOB CONSTANTA

1138

0.09

29

DELIVERY AREA/PRODUCT

FEED BARELY (RON/tone)

Var. %

30

WEST

732

4.57

31

EAST

832

2.09

32

SOUTH

713

-8.59

33

FOB CONSTANTA

-

0

34

WEEK 48/2023 (WEEK 30 NOVEMBER - 06 DECEMBER 2023)

#colspan#

35

DELIVERY AREA/PRODUCT

WHEAT (RON/tone)

Var. %

36

WEST

915

0.55

37

EAST

1005

2.24

38

SOUTH

907

1.23

39

FOB CONSTANTA

1137

-0.52

40

DELIVERY AREA/PRODUCT

FEED BARELY (RON/tone)

Var. %

41

WEST

-

0

42

EAST

815

-5.89

43

SOUTH

780

-8.45

44

FOB CONSTANTA

-

0

45

WEEK 47/2023 (WEEK 23 NOVEMBER - 29 NOVEMBER 2023)

#colspan#

46

DELIVERY AREA/PRODUCT

WHEAT (RON/tone)

Var. %

47

WEST

1415

-11.4

48

EAST

1478

-2.64

49

SOUTH

1508

0.07

50

FOB CONSTANTA

-

0

51

DELIVERY AREA/PRODUCT

FEED BARELY (RON/tone)

Var. %

52

WEST

-

0

53

EAST

1324

-4.06

54

SOUTH

1300

0

55

FOB CONSTANTA

-

0

Sheet2

Cell Formulas
Range	Formula
D1:F8	D1	=LET( a,A1:C55, b,ROWS(a), FILTER(a,(ISNUMBER(XLOOKUP(SEQUENCE(b),SEQUENCE(4,,1,11),SEQUENCE(4,,1,11))))+(ISNUMBER(XLOOKUP(SEQUENCE(b),SEQUENCE(4,,5,11),SEQUENCE(4,,5,11))))))
Dynamic array formulas.

vladimiratanasiu · Dec 29, 2024

Cubist said:
Another option:

Excel Formula:

=CHOOSEROWS(A1:C44,1,SCAN(1,4+3*MOD(SEQUENCE(ROWS(A1:A44)/6)-1,2),SUM))

Thank you, Cubist , for your solution! It works almost perfectly for the whole year 2023. Anyway, it strangely works until to the week 5 only. The weeks 1-4 are not included in the final results.

vladimiratanasiu · Dec 29, 2024

djclements said:
Another couple of possibilities you could try:

Excel Formula:

=FILTER(A1:C1000,(B1:B1000="#colspan#")+((A1:A1000="SOUTH")*ISNUMBER(SEARCH("WHEAT",SCAN("",B1:B1000,LAMBDA(a,v,IF(LEFT(v)="<",v,a)))))))

-OR-

Excel Formula:

=LET(rng,A1:C44,seq,SEQUENCE(ROWS(rng)/11,,,11),CHOOSEROWS(rng,TOCOL(HSTACK(seq,seq+4))))

Adjust the range references as needed.

Thank you, djclements ! Your two formulas work perfectly!

djclements · Dec 29, 2024

Glad to hear it. You're welcome!

Filter specific rows from a table

vladimiratanasiu

Active Member

Excel Facts

hagia_sofia

Well-known Member

vladimiratanasiu

Active Member

hagia_sofia

Well-known Member

djclements

Board Regular

Cubist

Well-known Member

vladimiratanasiu

Active Member

vladimiratanasiu

Active Member

vladimiratanasiu

Active Member

djclements

Board Regular

Similar threads

Forum statistics

Share this page

Filter specific rows from a table

Active Member

Excel Facts

Well-known Member

Active Member

Well-known Member

Board Regular

Well-known Member

Active Member

Active Member

Active Member

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock