Need vba to code to split data depends upon col I:I and col J:J

[jacktc]

Dear experts

I have set of data download from portal, which is clumsy data i want split into two sheet with set of criteria from two columns I & J

Ist criteria :In column I:I Description have unsorted data i want select only on which data meets approximately to defined key words
key words are might be prefix or suffix or in middle


ZPAC




REDO

_RE

REDO(1)
















JAP2




SCO3

GIFTS2






for e.g

CBT # 20462 CBT TRANSFER Tanjim Alam Choudhury _REDO(1)
all data move to sheet Delimited!!sheet(*data should be deleted once copy in Ledger sheet)



2nd criteria from col source J:J met with only 2 criteria filter Web Adi and Web Adi MC_MT.

all data move to sheet WEB_ADI!! Sheet (*data should be deleted once copy in Ledger sheet)

[p45cal]

I'm short on time right now so this is only to give you a start.
The most difficult aspect of this seems to be to devise vba to autofilter the table on sheet LEDGER for column 9 (I) containing one of several different strings, so I've addressed this first.
There's code on the internet which shows how to do this in several places but there are many instances of the same code, seemingly by Rory, at https://stackoverflow.com/questions/32882661/excel-vba-autofilter-contains-with-multiple-criteria
I've tweaked it a bit to suit your worksheet and to make it easier for you to change it for various lists of strings to find:

Sub MultiContainsAutofilter2()
Dim vData
Dim shData As Worksheet
Dim d As Object
Dim i As Long

Set shData = Sheets("LEDGER")
vData = shData.Cells(1).CurrentRegion.Columns(9)
ContainsStrings = Array("REDO", "_RE", "ZPAC", "JAP2", "SCO3", "GIFTS2") 'use upper case only to keep this case insensitive
Set d = CreateObject("Scripting.Dictionary")

For i = LBound(vData, 1) To UBound(vData, 1)
For Each ContainsString In ContainsStrings
If UCase$(vData(i, 1)) Like "*" & ContainsString & "*" Then
d(vData(i, 1)) = Empty
Exit For
End If
Next ContainsString
Next i

If d.Count > 0 Then shData.Cells(1).CurrentRegion.AutoFilter Field:=9, Criteria1:=d.keys, Operator:=xlFilterValues
End Sub
Put this in a standard code-module.
You'll need code to copy the visible cells to another sheet and delete them from the LEDGER sheet, but I'm out of time.
I note that some rows (rows 377,986,987,5397,5603) also contain Web Adi in column J. How do you want to deal with these? Copy only to the WEB_ADI sheet, copy only to the Delimited sheet, copy to both?
That's it for the moment.
Am I on the right track?

[snb]

I didn't know California and Australia are so closely intertwined.

[Aussiebear]

I didn't know California and Australia are so closely intertwined.
@snb We are not. so stop your rubbish

[jacktc]

why mate spill your nazi theory on knowledge forum! be cool