delete 8000 rows based on what I write items in helper column

[Omran Y]

Hi
I want فخ delete entire row based on helper column (G) .should match the whole item for each cell in column G with column B then should delete entire row , but be careful you will see many similar items may be you think should delete it but in reality not . should match the whole item in column G with column B. if you see the part of item is existed in column B but not whole as in column G then should not delete it.
I have about 8000 rows and every time I will add new items in column G.

OM1.xlsm
	A	B	C	D	E	F	G
1	DATE	OPERATION NAME	DEBIT	CREDIT	BALANCE		ITEMS
2	01/03/2023	BB IN TPUT TTR120 CASH PREPAID	10,000.00		10,000.00		CASH PREPAID
3	02/03/2023	PREPAID CASH BBI-60 IN TPUT MM200	2,000.00		12,000.00		BANK SWIFT
4	03/03/2023	BANK SWIFT FG-1005	30,000.00		42,000.00		INVOICE NUMBER SS
5	04/03/2023	MS.9888485 BANK SWIFT FG-1001	60,000.00		102,000.00
6	05/03/2023	PAID BANK MTSWF90001		2,000.00	100,000.00
7	06/03/2023	SWIFT BANK FGS-100101	10,000.00		110,000.00
8	07/03/2023	CASH PREPAID BBFG IN TPUT LM7040	30,000.00		140,000.00
9	08/03/2023	INN702000 CASH PIAD		6,000.00	134,000.00
10	09/03/2023	CASH PIAD MN90400 UY600M		1,000.00	133,000.00
11	10/03/2023	CASH FROM CURS	1200		134,200.00
12	11/03/2023	CSDF SWIFT REF678999	2000		136,200.00
13	12/03/2023	CRTM RT500 CVF 789/7788	1300		137,500.00
14	10/03/2023	INVOICE NUMBER SS OMM-1000	2300		139,800.00
15	11/03/2023	INVOICE NUMBER SS OMM-1001	2200		142,000.00
16	12/03/2023	INVOICE NUMBER SS OMM-1002	2500		144,500.00
17	13/03/2023	INVOICE NUMBER RR OMM-1003	2800		147,300.00
18	14/03/2023	INVOICE NUMBER RR OMM-1004	3100		150,400.00
ACS

the output should be like this

OM1.xlsm
	A	B	C	D	E
1	DATE	OPERATION NAME	DEBIT	CREDIT	BALANCE
2	02/03/2023	PREPAID CASH BBI-60 IN TPUT MM200	2,000.00		12,000.00
3	05/03/2023	PAID BANK MTSWF90001		2,000.00	100,000.00
4	06/03/2023	SWIFT BANK FGS-100101	10,000.00		110,000.00
5	08/03/2023	INN702000 CASH PIAD		6,000.00	134,000.00
6	09/03/2023	CASH PIAD MN90400 UY600M		1,000.00	133,000.00
7	10/03/2023	CASH FROM CURS	1200		134,200.00
8	11/03/2023	CSDF SWIFT REF678999	2000		136,200.00
9	12/03/2023	CRTM RT500 CVF 789/7788	1300		137,500.00
10	13/03/2023	INVOICE NUMBER RR OMM-1003	2800		147,300.00
11	14/03/2023	INVOICE NUMBER RR OMM-1004	3100		150,400.00
OUTPUT

 

[kevin9999]

Do you simply want the entire rows deleted from the "ACS" sheet (which could also delete the values in column G) or do you want the rows copied to another sheet ("OUTPUT"?) as you have indicated in your second image?

 

[Omran Y]

Hi
I want showing result in OUTPUT sheet. I need keep the original data in first sheet.
Thanks .

 

[Anonymous1378]

Try this?

Book5
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	DATE	OPERATION NAME	DEBIT	CREDIT	BALANCE		ITEMS		DATE	OPERATION NAME	DEBIT	CREDIT	BALANCE
2	01-Mar-23	BB IN TPUT TTR120 CASH PREPAID	10,000.00		10,000.00		CASH PREPAID		02-Mar-23	PREPAID CASH BBI-60 IN TPUT MM200	2,000.00	0.00	12,000.00
3	02-Mar-23	PREPAID CASH BBI-60 IN TPUT MM200	2,000.00		12,000.00		BANK SWIFT		05-Mar-23	PAID BANK MTSWF90001	0.00	2,000.00	100,000.00
4	03-Mar-23	BANK SWIFT FG-1005	30,000.00		42,000.00		INVOICE NUMBER SS		06-Mar-23	SWIFT BANK FGS-100101	10,000.00	0.00	110,000.00
5	04-Mar-23	MS.9888485 BANK SWIFT FG-1001	60,000.00		102,000.00				08-Mar-23	INN702000 CASH PIAD	0.00	6,000.00	134,000.00
6	05-Mar-23	PAID BANK MTSWF90001		2,000.00	100,000.00				09-Mar-23	CASH PIAD MN90400 UY600M	0.00	1,000.00	133,000.00
7	06-Mar-23	SWIFT BANK FGS-100101	10,000.00		110,000.00				10-Mar-23	CASH FROM CURS	1,200.00	0.00	134,200.00
8	07-Mar-23	CASH PREPAID BBFG IN TPUT LM7040	30,000.00		140,000.00				11-Mar-23	CSDF SWIFT REF678999	2,000.00	0.00	136,200.00
9	08-Mar-23	INN702000 CASH PIAD		6,000.00	134,000.00				12-Mar-23	CRTM RT500 CVF 789/7788	1,300.00	0.00	137,500.00
10	09-Mar-23	CASH PIAD MN90400 UY600M		1,000.00	133,000.00				13-Mar-23	INVOICE NUMBER RR OMM-1003	2,800.00	0.00	147,300.00
11	10-Mar-23	CASH FROM CURS	1,200.00		134,200.00				14-Mar-23	INVOICE NUMBER RR OMM-1004	3,100.00	0.00	150,400.00
12	11-Mar-23	CSDF SWIFT REF678999	2,000.00		136,200.00
13	12-Mar-23	CRTM RT500 CVF 789/7788	1,300.00		137,500.00
14	10-Mar-23	INVOICE NUMBER SS OMM-1000	2,300.00		139,800.00
15	11-Mar-23	INVOICE NUMBER SS OMM-1001	2,200.00		142,000.00
16	12-Mar-23	INVOICE NUMBER SS OMM-1002	2,500.00		144,500.00
17	13-Mar-23	INVOICE NUMBER RR OMM-1003	2,800.00		147,300.00
18	14-Mar-23	INVOICE NUMBER RR OMM-1004	3,100.00		150,400.00
Sheet1

Cell Formulas
Range	Formula
I2:M11	I2	=FILTER(A2:E18,NOT(MMULT(--ISNUMBER(SEARCH(TRANSPOSE(G2:G4),B2:B18)),SEQUENCE(ROWS(G2:G4),,,0))))
Dynamic array formulas.

 

[kevin9999]

I like @Anonymous1378 's suggestion, however if you were after a VBA solution, please try this on a copy of your workbook:

Option Explicit
Sub OmranY()
    Application.ScreenUpdating = False
    Dim ws1 As Worksheet, ws2 As Worksheet
    Set ws1 = Worksheets("ACS"): Set ws2 = Worksheets("OUTPUT")
    
    Dim d As Object, c As Range, r As Range, tmp As String, a, b, LRow As Long, i As Long, j As Long
    LRow = ws1.Cells(Rows.Count, "G").End(xlUp).Row
    Set r = ws1.Range("G2:G" & Application.Max(LRow, 2))
    b = r.Value2
    If IsEmpty(b) Then MsgBox "No Items selected - exiting sub": Exit Sub
    
    If r.Cells.Count > 1 Then
        Set d = CreateObject("scripting.dictionary")
        a = Application.Transpose(ws1.Range("B2", ws1.Cells(Rows.Count, "B").End(xlUp)))
        For i = 1 To UBound(a, 1)
            d(a(i)) = 1
        Next i
        For Each c In ws1.Range("B2", ws1.Cells(Rows.Count, "B").End(xlUp))
            tmp = c.Value2
            For j = LBound(b) To UBound(b)
                If tmp Like "*" & b(j, 1) & "*" Then d.Remove (tmp)
            Next j
        Next c
    Else
        ReDim b(1 To 1, 1 To 1): b = r
    End If
    
    With ws1.Cells(1, 1).CurrentRegion
        If r.Cells.Count = 1 Then
            .AutoFilter 2, "<>" & "*" & b & "*"
        Else
            .AutoFilter 2, Array(d.keys), 7
        End If
        If ws1.Cells(Rows.Count, "A").End(xlUp).Row > 1 Then
            .Offset(1).Resize(.Rows.Count - 1, 5).Copy ws2.Cells(Rows.Count, 1).End(xlUp).Offset(1)
        End If
        .AutoFilter
    End With
    Application.ScreenUpdating = True
End Sub

 

[Omran Y]

@Anonymous1378
did you see my profile about office version?
I suppose your solution just work with 365 version .

 

[Omran Y]

however if you were after a VBA solution, please try this on a copy of your workbook:
VBA Code:

yes, thanks .
I will test for simple data today , tomorrow at work for big data and come back to inform you how goes .

 

[Peter_SSs]

I have about 8000 rows

If there are quite a few disjoint rows to delete in that range then I think that you may find this faster.

Sub Del_Rows()
  Dim RX As Object
  Dim a As Variant, b As Variant
  Dim nc As Long, i As Long, k As Long
  
  Set RX = CreateObject("VBScript.regExp")
  RX.Pattern = "\b(" & Join(Application.Transpose(Range("G2", Range("G" & Rows.Count).End(xlUp)).Value), "|") & ")\b"
  nc = 6
  a = Range("B2", Range("B" & Rows.Count).End(xlUp)).Value
  ReDim b(1 To UBound(a), 1 To 1)
  For i = 1 To UBound(a)
    If RX.test(a(i, 1)) Then
      b(i, 1) = 1
      k = k + 1
    End If
  Next i
  If k > 0 Then
    Application.ScreenUpdating = False
    With Range("A2").Resize(UBound(a), nc)
      .Columns(nc).Value = b
      .Sort Key1:=.Columns(nc), Order1:=xlAscending, Header:=xlNo
      .Resize(k).EntireRow.Resize(, 6).Delete shift:=xlUp
    End With
    Application.ScreenUpdating = True
  End If
End Sub

 

[Alex Blakenburg]

@Peter_SSs - I think you missed seeing Post #3

I want showing result in OUTPUT sheet. I need keep the original data in first sheet.

 

[Peter_SSs]

@Peter_SSs - I think you missed seeing Post #3

Thanks Alex, yes I did miss that - just saw the "delete rows" & fixed on that.

I don't have Excel 2013 to test this with but assuming OUTPUT exists and any data on it can be removed, this could be tried instead.

Sub Remove_Rows()
  Dim rCrit As Range
  
  Sheets("OUTPUT").UsedRange.Clear
  With Sheets("ACS")
    Set rCrit = .Range("F1:F2")
    rCrit.Cells(2).Formula = "=COUNT(FIND(G$2:G$" & .Range("G" & Rows.Count).End(xlUp).Row & ",B2))=0"
    .Range("A1").CurrentRegion.Resize(, 5).AdvancedFilter Action:=xlFilterCopy, _
            CriteriaRange:=rCrit, CopyToRange:=Sheets("OUTPUT").Range("A1"), Unique:=False
    rCrit.ClearContents
  End With
End Sub