Omit rows which do not match criteria

shili12 · Sep 6, 2023

Posted under here, but nevertheless, power query, formula or conditional formatting solutions, or LET are acceptable as well.
i have a 36MB file with over 171000 rows. Am using office 365.

My expected result is the last column narration or coloured, the final column is not part of original database:-

Unit manager	Agency	policy nos(unique)	Date	Type	amount	Action needed on row
EVANS smithson	SUN-Asean INSURANCE AGENCY	200/070/1/763154/2023/	4-Aug	DRN	11880.8	keep as its august transaction
EVANS smithson	SUN-Asean INSURANCE AGENCY	200/070/1/763154/2023/	4-Aug	REC	-11881.7	keep as its august transaction
EVANS smithson	SUN-Asean INSURANCE AGENCY	200/070/1/763817/2023/	17-Jul	DRN	3881	delete as its july transaction
EVANS smithson	SUN-Asean INSURANCE AGENCY	200/070/1/763817/2023/	17-Jul	REC	-3881	delete as its july transaction
MONICAH jones	MONICAH jones	050/088/1/002610/2023/	11-Jul	DRN	7366.5	delete as its july transaction
MONICAH jones	MONICAH jones	050/088/1/002610/2023/	11-Jul	REC	-9790	delete as its july transaction
MONICAH jones	PENA INSURANCE AGENCY	120/070/1/500188/2022/	31-Jul	DRN	2341	keep as it has july aug
MONICAH jones	PENA INSURANCE AGENCY	120/070/1/500188/2022/	7-Aug	REC	-2381	keep as it has july aug
MONICAH jones	PENA INSURANCE AGENCY	120/070/1/747345/2023/	11-Aug	REC	-181	delete as it is solo transaction
MONICAH jones	PENA INSURANCE AGENCY	120/080/1/189302/2023/	1-Aug	DRN	899	delete as it is solo transaction
EVERLYN cookson MAINA	PAC INSURANCE AGENCY	040/070/1/758959/2023/	8-Jul	DRN	1370.6	delete as its solo transaction
EMMAH nightingale	PHELP INSURANCE AGENCY	140/070/1/780879/2023/	11-Aug	DRN	9999.9	delete as it has credit note
EMMAH nightingale	PHELP INSURANCE AGENCY	140/070/1/780879/2023/	11-Aug	REC	-10000	delete as it has credit note
EMMAH nightingale	PHELP INSURANCE AGENCY	140/070/1/780879/2023/	21-Aug	CRN	-9959.9	delete as it has credit note
EVANS smithson	SPRINT INSURANCE AGENCY LTD	010/070/1/674210/2023/	13-Jul	CRN	-19292.4	delete as its july transaction
EVERLYN cookson MAINA	NERITA INSURANCE AGENCY	050/070/1/750194/2023/	25-Jul	CRN	-2380.7	delete as its july transaction
EVERLYN cookson MAINA	NERITA INSURANCE AGENCY	050/070/1/750194/2023/	25-Jul	DRN	2380.7	delete as its july transaction
VIRGINIA NJOROGE	EBC INSURANCE AGENCY	050/084/1/026101/2023/	19-Aug	CRN	-33492.6	delete as it has solo credit note

vletm · Sep 6, 2023

shili12

Your which do not match criteria
Where have You named criteria which match?
keep as its august transaction
keep as it has july aug

shili12 · Sep 6, 2023

vletm said:
shili12
Your which do not match criteria Where have You named criteria which match? keep as its august transaction keep as it has july aug

Yes those you mentioned are rows to keep or retain.
July and aug debit plus receipt, aug debit plus receipt, rest are not relevant and are desired to be omit from xlsx file.

vletm · Sep 6, 2023

shili12

Your July and aug debit plus receipt, aug debit plus receipt - okay ...
... but why Your expected results has also two minus receipts?
as well as there are many of those which aren't marked as expected?

shili12 · Sep 6, 2023

vletm said:
shili12
Your which do not match criteria Where have You named criteria which match? keep as its august transaction keep as it has july aug

Yes those you mentioned are rows to keep or retain.
July and aug debit plus receipt, aug debit plus receipt, rest are not relevant and are desired to be omit from xlsx file.

vletm said:
shili12
Your July and aug debit plus receipt, aug debit plus receipt - okay ...
... but why Your expected results has also two minus receipts?
as well as there are many of those which aren't marked as expected?

Also anything which is solo, ie the receipt has no corresponding debit, only one policy no. is said to be of no relevance. If one solo debit is seen with one policy no. then it's to be omitted as of no relevance

vletm · Sep 6, 2023

shili12

I asked two questions ( #4 reply ) - You seems to skipped those.
... to find out clear rules which match.

shili12 · Sep 7, 2023

Umm.....
Let me just show the worksheet before and after, showing desired results, Thanks.
see below is the results i desire, rest are to be omitted, rule of thumb: only aug transactions debit rec, July aug deb receipt , rest like crn ,single debit, single receipt, single crn in July and aug not needed.

Unit manager	Agency	policy nos(unique)	Date	Type	amount
EVANS smithson	SUN-Asean INSURANCE AGENCY	200/070/1/763154/2023/	04-Aug	DRN	11880.8
EVANS smithson	SUN-Asean INSURANCE AGENCY	200/070/1/763154/2023/	04-Aug	REC	-11881.7
MONICAH jones	PENA INSURANCE AGENCY	120/070/1/500188/2022/	31-Jul	DRN	2341
MONICAH jones	PENA INSURANCE AGENCY	120/070/1/500188/2022/	07-Aug	REC	-2381

vletm · Sep 7, 2023

shili12

Your July and aug debit plus receipt, aug debit plus receipt,
Could You try to explain Your above rule with Your sent snapshot?
-11881.7 and -2381 ... for Your eyes ... are those plus receipt?
... to find out clear rules which match.

shili12 · Sep 7, 2023

Ok, i get you now, what you mean is, i am using plus in arithmetic way +, No, i am using it as a conjunction as below:=
"And also; in addition to" not as an arithmetic sign.

vletm · Sep 7, 2023

shili12

... Your plus means ... that
Your July and aug debit plus receipt, aug debit plus receipt
could be written as
July and aug debit also receipt, aug debit also receipt
> Note > All Your lines dates are July or Aug ...
# What is different between J & A debit and A debit ?
# What about next Your term debit? How to know - when Your something is ... debit?
> Still, You seems to would like to get only few lines left ( = matched ) then that 'group' rules should be clear. <
If You're trying to create rules for both 'show' and 'hide' ... what to do with lines, which won't match with those rules?

shili12 · Sep 7, 2023

DRN means debit, REC means receipt, CRN means credit note.
All with one policy no. need to be deleted whether in july or aug

If there is DRN in july and REC in Aug, we need that info, same policy no. If there is DRN in aug and REC in Aug, we need that info.

Rest to be deleted,
Apologies if i used plus to mean +, instead of Also, Usually here everybody is used to saying :"give me that file, plus the red pen"

vletm · Sep 7, 2023

shili12

For me, many of those Your sample Action needed on row -writings (#1) no match with Your (#11) 'rules'.

shili12 · Sep 7, 2023

would this table help ??, as i had removed totals, the difficulty herein lies when there is july and aug debit and receipt together.
focal point is policy nos

Unit manager	Agency	policy nos(unique)	Date	Type	amount	Action needed on row
EVANS smithson	SUN-Asean INSURANCE AGENCY	200/070/1/763154/2023/	04-Aug	DRN	11880.8	keep as its august transaction
EVANS smithson	SUN-Asean INSURANCE AGENCY	200/070/1/763154/2023/	04-Aug	REC	-11881.7	keep as its august transaction
				total	-0.9
EVANS smithson	SUN-Asean INSURANCE AGENCY	200/070/1/763817/2023/	17-Jul	DRN	3881	delete as its july transaction
EVANS smithson	SUN-Asean INSURANCE AGENCY	200/070/1/763817/2023/	17-Jul	REC	-3881	delete as its july transaction
				total	0
MONICAH jones	MONICAH jones	050/088/1/002610/2023/	11-Jul	DRN	7366.5	delete as its july transaction
MONICAH jones	MONICAH jones	050/088/1/002610/2023/	11-Jul	REC	-9790	delete as its july transaction
				total	-2423.5
MONICAH jones	PENA INSURANCE AGENCY	120/070/1/500188/2022/	31-Jul	DRN	2341	keep as it has july aug
MONICAH jones	PENA INSURANCE AGENCY	120/070/1/500188/2022/	07-Aug	REC	-2381	keep as it has july aug
				total	-40
MONICAH jones	PENA INSURANCE AGENCY	120/070/1/747345/2023/	11-Aug	REC	-181	delete it as its solo transaction
MONICAH jones	PENA INSURANCE AGENCY	120/080/1/189302/2023/	01-Aug	DRN	899	delete it as its solo transaction
EVERLYN cookson MAINA	PAC INSURANCE AGENCY	040/070/1/758959/2023/	08-Jul	DRN	1370.6	delete as its solo transaction
EMMAH nightingale	PHELP INSURANCE AGENCY	140/070/1/780879/2023/	11-Aug	DRN	9999.9	delete as it has credit note
EMMAH nightingale	PHELP INSURANCE AGENCY	140/070/1/780879/2023/	11-Aug	REC	-10000	delete as it has credit note
EMMAH nightingale	PHELP INSURANCE AGENCY	140/070/1/780879/2023/	21-Aug	CRN	-9959.9	delete as it has credit note
				total	-9960
EVANS smithson	SPRINT INSURANCE AGENCY LTD	010/070/1/674210/2023/	13-Jul	CRN	-19292.4	delete as its july transaction
EVERLYN cookson MAINA	NERITA INSURANCE AGENCY	050/070/1/750194/2023/	25-Jul	CRN	-2380.7	delete as its july transaction
EVERLYN cookson MAINA	NERITA INSURANCE AGENCY	050/070/1/750194/2023/	25-Jul	DRN	2380.7	delete as its july transaction
				total	0
VIRGINIA NJOROGE	EBC INSURANCE AGENCY	050/084/1/026101/2023/	19-Aug	CRN	-33492.6	delete as it has credit note

vletm · Sep 7, 2023

Your original rule was July and aug debit plus receipt, aug debit plus receipt
with #11 You wrote ... something
... and next You added something ... else ... two times?
How would that make more clear?
... so far, You've not been interesting about Amounts at all.

Your original expected results are marked with light blue ... both DRN & REC
There are also three other yellow marked lines which has both DRN & REC ... why not those?

... and with those new Amounts ... hmm?

Do You really have clear idea ... what?

shili12 · Sep 8, 2023

Those you shaded in YELLOW above are to be deleted,
Rule of thumb: if policy no. is for DRN and REC is same and for aug and aug, keep, if policy no. is same for DRN is july and REC is aug, keep.
Rest delete any with policy no is same but has DRN,REC and CRN (to delete). If policy no. is only one row delete DRn, CRN or REC.

vletm · Sep 8, 2023

I asked: There are also three other yellow marked lines which has both DRN & REC ... why not those?
You replied: Those you shaded in YELLOW above are to be deleted,
... based Your reply ... I didn't find answer to why-question. I can code that if You've marked those Yellow.
based Your: would this table help ??
I showed a snapshot with Your amounts too ... I didn't find any comments.
... if there will show CRN too, then could got all data.
based #10 the last line: If You're trying to create rules for both 'show' and 'hide' ... what to do with lines, which won't match with those rules?
You're still offering both - show & hide.
I asked too: Do You really have clear idea ... what?
I'm still missing that too.
Rule of thumb: If no clear rule then ... a challenge.

Marc L · Sep 9, 2023

According to the initial post an 'one shot' VBA demonstration - so for starters - to better paste to Ark1 worksheet module :

Code:

Sub Demo1()
  Const D = #8/1/2023#
    Dim V, S%(), R&, P, F&, B&, C&
   With [A1].CurrentRegion.Columns
        V = .Item("C:E").Offset(1)
        ReDim S(1 To .Rows.Count - 1, 0)
    For R = 1 To .Rows.Count
     If V(R, 1) <> P Then
        If F And (R - F = 1 Or B = 0 Or C > 0) Then For F = F To R - 1: S(F, 0) = 1: Next
        B = 0
        C = 0
        F = R
        P = V(R, 1)
     End If
        B = B - (V(R, 2) >= D)
        C = C - (V(R, 3) = "CRN")
    Next
        C = Application.Sum(S)
     If C Then
        Application.ScreenUpdating = False
       .Item(.Count + 1).Rows("2:" & .Rows.Count) = S
       .Resize(, .Count + 1).Sort .Cells(1, .Count + 1), 1, Header:=1
       .Item(.Count + 1).Clear
       .Rows(.Rows.Count + 1 - C & ":" & .Rows.Count).Clear
        Application.ScreenUpdating = True
     End If
   End With
End Sub

Do you like it ? So thanks to click on bottom right Like !

shili12 · Sep 9, 2023

I mean @Marc L that's just excellent, it works like a charm, now the only thing will be to test it on large file and hope it does not take up much time.
I was just about to cross post to the other forum when your reply came thru.
At least you got the part of policy no. to be both on DRN and REC.

Marc L · Sep 9, 2023

My VBA procedure does not care about 'DRN' and 'REC' …

It's one of both fastest ways I know for those confusing Excel with a database software as yes, Excel is slow,

an expert Excel VBA procedure can be 100 times slower than a beginner database code !

shili12 · Sep 9, 2023

exactly,
the july are not being deleted. i checked it out.
the rule of thumb is keep drn and rec for aug same policy , the july (drn) and aug (rec) same policy, but delete rest.

MARTHA MAKWATA	MARTIS INSURANCE AGENCY	140/084/1/027307/2023/	27-Jul	DRN	6899
MARTHA MAKWATA	MARTIS INSURANCE AGENCY	140/084/1/027307/2023/	27-Jul	REC	-7574
MARTHA MAKWATA	MARTIS INSURANCE AGENCY	140/084/1/028364/2023/	17-Aug	DRN	3200.5
MARTHA MAKWATA	MARTIS INSURANCE AGENCY	140/084/1/028364/2023/	17-Aug	REC	-3200
MARTHA MAKWATA	MARTIS INSURANCE AGENCY	140/084/1/028569/2023/	25-Aug	DRN	11271
MARTHA MAKWATA	MARTIS INSURANCE AGENCY	140/084/1/028569/2023/	25-Aug	REC	-11270
MARTHA MAKWATA	MARTIS INSURANCE AGENCY	140/084/1/028646/2023/	31-Aug	DRN	6899
MARTHA MAKWATA	MARTIS INSURANCE AGENCY	140/084/1/028646/2023/	31-Aug	REC	-7574

Marc L · Sep 10, 2023

As on my side adding your previous post data, policy # 140/084/1/027307/2023/ is deleted as expected

according to exactly the same case in your initial post :

shili12 said:
Unit manager Agency policy nos(unique) Date Type amount Action needed on row
MONICAH jones MONICAH jones 050/088/1/002610/2023/ 11-Jul DRN
7366.5
delete as its july transaction
MONICAH jones MONICAH jones 050/088/1/002610/2023/ 11-Jul REC
-9790
delete as its july transaction

shili12 said:
that's just excellent, it works like a charm

Omit rows which do not match criteria

Member

Attachments

Excel Ninja

shili12​

Member

shili12​

Excel Ninja

shili12​

Member

shili12​

shili12​

Excel Ninja

shili12​

Member

Attachments

Excel Ninja

shili12​

Member

Excel Ninja

shili12​

Member

Excel Ninja

shili12​

Member

Attachments

Excel Ninja

Member

Excel Ninja

Excel Ninja

Member

Attachments

Excel Ninja

Member

Excel Ninja

shili12

shili12

shili12

shili12

shili12

shili12

shili12

shili12

shili12