Filter or any other function for filter the data

[Vishaal]

Hi, we want the folloing data to be filtered, along with the desired results. Please help us filter for tasks that are either completed or in progress?

Data Sheet

Book1
	A	B	C	D	E	F
1	Data
2	List	Stone	Labour	Start Date	Due Date	Completion Date
3	M7-2.test	M7-test T	John	10/22/24	10/22/24	10/22/24
4	M7-2.test	M7-test T	Rock	10/22/24	10/22/24	-
5	M6-2.POSITIVE	M6-POSITIVE	Ruby	10/22/24	10/22/24	10/22/24
6	M6-2.POSITIVE	M6-POSITIVE	John	10/22/24	10/22/24	10/22/24
7	M6-2.POSITIVE	M6-POSITIVE	wick	10/22/24	10/22/24	-
8	M5-2.HOLI	M5- HOLD	Jai	10/15/24	10/18/24	10/22/24
9	M3-2.RUGGED	M3- RUGGED	John	10/22/24	10/22/24	10/22/24
10	M1- PASSED	M1- PASSED	User	10/17/24	10/17/24	10/17/24
11	M1- PASSED	M1- PASSED	User	10/17/24	10/17/24
12	M1- PASSED	M1- PASSED	Van D	10/17/24	10/17/24	10/17/24
13	M1- PASSED	M1- PASSED	Ruby	10/17/24	10/17/24
14	M1- PASSED	M1- PASSED AG	Van D	10/17/24	10/17/24	10/17/24
15	M3- 1.KAR	M3- KAR	John	10/19/24	10/19/24	10/22/24
16	M9-STAR	M9-STAR
17	M9-STAR	M9-STAR
Sheet1

Result Sheet

Book1
	I	J	K	L	M	N
1	Data
2	List	Stone	Labour	Start Date	Due Date	Completion Date
3	M7-2.test	M7-test T	Rock	10/22/24	10/22/24	-
4	M6-2.POSITIVE	M6-POSITIVE	wick	10/22/24	10/22/24	-
5	M5-2.HOLI	M5- HOLD	Jai	10/15/24	10/18/24	10/22/24
6	M3-2.RUGGED	M3- RUGGED	John	10/22/24	10/22/24	10/22/24
7	M1- PASSED	M1- PASSED	User	10/17/24	10/17/24
8	M1- PASSED	M1- PASSED	Ruby	10/17/24	10/17/24
9	M3- 1.KAR	M3- KAR	John	10/19/24	10/19/24	10/22/24
10	M9-STAR	M9-STAR
Sheet1

 

[Logit]

Row 7 & 8 should not be included in the filtered data ?

 

[Vishaal]

Row 7 & 8 should not be included in the filtered data ?

Sorry, we want only one row, row 8

 

[Logit]

Your instructions are confusing. Rows 7 & 8 column N are missing any indicator like the other cells in the column. It appears that
N7 & N8 should be omitted from the filtering.

 

[Vishaal]

Your instructions are confusing. Rows 7 & 8 column N are missing any indicator like the other cells in the column. It appears that
N7 & N8 should be omitted from the filtering.

if you will check then find that we are tracking as per following
Heading Stone are main task
Heading List are under the main Task
now we want all the heading work done or not, if done then what is last task done and if not done then on which stage only

 

[Vishaal]

we have given the work in N7 & N8 but its not done, so we want in list

 

[Fuji]

Sub test()
    Dim r As Range
    With [a1].CurrentRegion.Offset(1)
        Set r = .Offset(, .Columns.Count + 2).Range("a1:a2")
        r(2).Formula = "=or(countif(" & .Columns(1).Address & "," & .Cells(2, 1).Address(0, 0) & _
        ")=1,and(" & .Cells(2, 1).Address(0, 0) & "=" & .Cells(1, 1).Address(0, 0) & ",or(" & _
        .Cells(2, "f").Address(0, 0) & "={""-"",""""})))"
        .AdvancedFilter 1, r
    End With
    r=""
End Sub

 

[Vishaal]

just checked, not working, kindly check

 

[Fuji]

1) "not working" is the wrost response.
How is it not working?
Error? Does't do anything?

Not working as you expect?
If so, you need to explain about it.

2) your desire result in #1.

qqq.xlsm
	A	B	C	D	E	F
1	Data
2	List	Stone	Labour	Start Date	Due Date	Completion Date
3	M7-2.test	M7-test T	Rock	10/22/24	10/22/24	-
4	M6-2.POSITIVE	M6-POSITIVE	wick	10/22/24	10/22/24	-
5	M5-2.HOLI	M5- HOLD	Jai	10/15/24	10/18/24	10/22/24
6	M3-2.RUGGED	M3- RUGGED	John	10/22/24	10/22/24	10/22/24
7	M1- PASSED	M1- PASSED	User	10/17/24	10/17/24
8	M1- PASSED	M1- PASSED	Ruby	10/17/24	10/17/24
9	M3- 1.KAR	M3- KAR	John	10/19/24	10/19/24	10/22/24
10	M9-STAR	M9-STAR
Sheet1

3) My result after the code.

qqq.xlsm
	A	B	C	D	E	F
1	Data
2	List	Stone	Labour	Start Date	Due Date	Completion Date
4	M7-2.test	M7-test T	Rock	10/22/24	10/22/24	-
7	M6-2.POSITIVE	M6-POSITIVE	wick	10/22/24	10/22/24	-
8	M5-2.HOLI	M5- HOLD	Jai	10/15/24	10/18/24	10/22/24
9	M3-2.RUGGED	M3- RUGGED	John	10/22/24	10/22/24	10/22/24
11	M1- PASSED	M1- PASSED	User	10/17/24	10/17/24
13	M1- PASSED	M1- PASSED	Ruby	10/17/24	10/17/24
15	M3- 1.KAR	M3- KAR	John	10/19/24	10/19/24	10/22/24
17	M9-STAR	M9-STAR
Sheet1