Delete rows based on match criteria in two excel files, 1 might be .csv file .Opened in Excel=Fail Chaos

**DocAElstein** · 05-28-2020, 02:56 PM

Sub jindonsTesties()
' PART 1 ================================
This is mainly concerned with the excel file, “1.xls”
Its final purpose, its final result, is a text string , txt, which ADO will recognise to select/ filter a text file based on field information. The second field, F2 , ( which we can approximately regard as column B of alert.csv ) will be used to select the rows we want. Those we don’t take, will be then effectively the deleted records ( rows ) of the text file.

The original given logic of here
https://excelfox.com/forum/showthread.php/2500-Conditionally-delete-entire-row-with-calculation-within-files/page2#post13440
https://excelfox.com/forum/showthrea...age2#post13460

is this
If column J of 1.xls has buy & column H of 1.xls is not greater than column D of 1.xls then match column I data of 1.xls with second field, F2 ( column B ) of text file , alert.csv and if it matches then delete that entire record (row) of alert.csv
If column J of 1.xls has a blank cell then match column I data of 1.xls with second field ( column B ) of of text file , alert.csv and if it matches then delete that entire record (row) of alert.csv
If column J of 1.xls has short & column H of 1.xls is Greater than than column D of 1.xls then match column I data of 1.xls with second field, F2 ( column B ) of of text file , alert.csv and if it matches then delete that entire record (row) of alert.csv
We can re write that logic thus:
(If column J of 1.xls has buy & column H of 1.xls is not greater than column D of 1.xls
OR
If column J of 1.xls has short & column H of 1.xls is Greater than column D of 1.xls
OR
If column J of 1.xls has a blank)
Then
then match column I data of 1.xls with second field ( column B ) of of text file , alert.csv and if it matches then delete that entire record (row) of alert.csv

Sub jindonsTesties() is concerned with the excel file, “1.xls”, and solves this part of the re written criteria:
(If column J of 1.xls has buy & column H of 1.xls is not greater than column D of 1.xls
OR
If column J of 1.xls has short & column H of 1.xls is Greater than column D of 1.xls
OR
If column J of 1.xls has a blank)

Finally it produces a string that is in the form of a “SQL” command that is recognised by ADO stuff to select/filter records(rows) from a text file based on values in a field(column). We finally want a text line, txt, like this
__ (Not F2 = 15083) And (Not F2 = 17388) And (Not F2 = 100)

Rem 1 Worksheets info:
This only opens the Excel File, and does the usual worksheet characteristic info…

Rem 2
The purpose of this section is to build 3 1 dimensional arrays, each for the values of I that meet the criteria for being considered further for a match.

This uses worksheet array type formulas within VBA via Evaluate(“ “) to give arrays of the column I values meeting the criteria to be further used in the Match part
finally have a text line, txt, like this
__ (Not F2 = 15083) And (Not F2 = 17388) And (Not F2 = 100)

Code:

'    Alert 29May excelforum..csv         https://www.excelforum.com/excel-programming-vba-macros/1317589-conditionally-compare-the-data-and-delete-entire-row.html
'If column J of 1.xls has buy & column H of 1.xls is not greater than column D of 1.xls
'  then match column I data of 1.xls with column B of alert.csv and
'    if it matches then delete that entire row of alert.csv
'If column J of 1.xls has a blank cell
'  then match column I data of 1.xls with column B of alert.csv and
'    if it matches then delete that entire row of alert.csv
'If column J of 1.xls has short & column H of 1.xls is Greater than than column D of 1.xls
'  then match column I data of 1.xls with column B of alert.csv and
'    if it matches then delete that entire row of alert.csv

' With Sheets(1)
'           Lr = .Range("a" & Rows.Count).End(xlUp).Row

' Missed 3 dots.
'    With GetObject(fn)
'        With .Sheets(1)
'            Lr = .Range("a" & .Rows.Count).End(xlUp).Row
Sub OpenAlert29Mayexcelforum__csv()
 Workbooks.Open Filename:=ThisWorkbook.Path & "\Alert 29May excelforum..csv"
End Sub

Sub JindonsTesties()  '    Conditionally compare the data & delete entire row - https://www.excelforum.com/excel-programming-vba-macros/1317589-conditionally-compare-the-data-and-delete-entire-row.html#post5340103
' PART 1 ================================
    Dim LR As Long, e, fn As String, myCSV As String, txt As String, vTemp As Variant, arrTemp() As Variant
Rem 1 Workbooks, Worksheets info
'    fn = ThisWorkbook.Path & "\1.xls"                          '"C:\Users\WolfieeeStyle\Desktop\1.xls"
'    myCSV = ThisWorkbook.Path & "\Alert 29May excelforum..csv" ' "C:\Users\WolfieeeStyle\Desktop\Alert..csv"
'    If (Dir(fn) = "") + (Dir(myCSV) = "") Then MsgBox "Invalid file Path/Name": Exit Sub
Dim Wb1 As Workbook
 Set Wb1 = Workbooks("1.xls")                                         '   CHANGE TO SUIT
' Set Wb1 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\1.xls")    '   CHANGE TO SUIT
    'With GetObject(fn)
        'With .Worksheets.Item(1)
Dim Ws1 As Worksheet
 Set Ws1 = Wb1.Worksheets.Item(1)
 Let LR = Ws1.Range("a" & Ws1.Rows.Count).End(xlUp).Row ' 1.xls last row of data
Rem 2 Make 1 Dimensional arrays for values
'2a) If column J of 1.xls has buy & column H of 1.xls is not greater than column D of 1.xls
 'Let vTemp = .Evaluate("transpose(if((j2:j" & LR & "=""buy"")*(h2:h" & LR & "
 Let arrTemp() = Ws1.Evaluate("transpose(if((j2:j" & LR & "=""buy"")*(h2:h" & LR & "For Each e In Filter(arrTemp(), False, 0) ' Filter(arrTemp(), False, 0) is empty
     Let txt = txt & " And (Not F2 = " & e & ")"
    Next
'2b) If column J of 1.xls has short & column H of 1.xls is Greater than  column D of 1.xls
' Let vTemp = .Evaluate("transpose(if((j2:j" & LR & "=""short"")*(h2:h" & LR & ">d2:d" & LR & "),i2:i" & LR & "))")
 Let arrTemp() = Ws1.Evaluate("transpose(if((j2:j" & LR & "=""short"")*(h2:h" & LR & ">d2:d" & LR & "),i2:i" & LR & "))")
    For Each e In Filter(arrTemp(), False, 0) ' Filter(arrTemp(), False, 0) is {100}
     Let txt = txt & " And (Not F2 = " & e & ")"
    Next
'2c) If column J of 1.xls has a blank
' Let vTemp = .Evaluate("transpose(if(j2:j" & LR & "="""",i2:i" & LR & "))")
 Let arrTemp() = Ws1.Evaluate("transpose(if(j2:j" & LR & "="""",i2:i" & LR & "))")
    For Each e In Filter(arrTemp(), False, 0) '  Filter(arrTemp(), False, 0) is {15083, 17388}
     Let txt = txt & " And (Not F2 = " & e & ")"
    Next
        'End With ' final txt is   And (Not F2 = 15083) And (Not F2 = 17388) And (Not F2 = 100)
        '.Close
    'End With
'    CreateNew myCSV, Mid$(txt, 5)
' Let txt = Mid$(txt, 6) ' take off the first  " AND "

' Part 2 ===============================================================================
'End Sub
'Sub MyTests_CreateNew()

' Part 2 ==================================

See next post…

**DocAElstein** · 05-28-2020, 02:56 PM

Sub jindonsTesties()
' PART 2 ================================

This part of jindon's offering is concerned with manipulating the text file. It has very little to do with Excel or VBA. The VBA coding is using ADO / ADODB techniques, which are like "SQL" stuff, that is universal and not native to Excel or VBA
https://en.wikipedia.org/wiki/ActiveX_Data_Objects
https://en.wikipedia.org/wiki/SQL
https://www.excelforum.com/excel-pro...liarities.html
https://www.excelforum.com/excel-pro...ml#post4664487

Rem 3
'3b) This makes a copy of the original text file. I am not sure why.

Rem 4 ADODB stuff
ADODB stuff allows us to use universal language to look at text files, and other simple data values files

'4a) this does something similar to out VBA Open ___ As ___ , and basically sets up a route, in this case to a folder.

'4b) This builds an object which can approximately be thought of as a text file, except we can get at the data in a number of ways, which includes selecting based on criteria. The criteria string developed in Part 1 _…
[color=code](Not F2 = 15083) And (Not F2 = 17388) And (Not F2 = 100) [/code]
_… is a syntax recognised to select all records ( rows ) based on Not having the values in the second filed ( column B )

I think this line, Rs.GetString(, , ",", vbCrLf) , appears to return our record ( row ) set in our familiar text file single long string format.

Rem 6 will make a text file from the long string in the way in which we are familiar.

( I am not sure why jindon is making various copies with different names, other than that such behaviour is sensible when developing such coding so that there are plenty of back up copies )

Code:

' Part 2 ===============================================================================
'End Sub
'Sub MyTests_CreateNew()
Rem 3 source text file
'3a) source text file
Dim myCSV As String ' , txt As String
 Let myCSV = ThisWorkbook.Path & "\Alert 29May excelforum..csv" ' "C:\Users\WolfieeeStyle\Desktop\Alert..csv"
' Call CreateNew(myCSV, Mid$(txt, 5))
'End Sub
'Private Sub CreateNew(myCSV As String, txt As String)
    Dim fn As String ' , cn As Object, rs As Object, x
' 3b Make copy of test file , make temporary file
    fn = Left$(myCSV, InStrRev(myCSV, "\")) & "tempComma.csv"
Dim PathAndFileName As String: Let PathAndFileName = fn
 FileCopy myCSV, fn ' FileCopy source, destination         https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/filecopy-statement

Rem 4 ADODB stuff
'4a)
Dim Cn As Object: Set Cn = CreateObject("ADODB.Connection")
    With Cn
     .Provider = "Microsoft.Ace.OLEDB.12.0"
     .Properties("Extended Properties") = "Text;HDR=No;"
        '.Open Left(fn, InStrRev(fn, "\"))
Dim PathOnly As String: Let PathOnly = Left(fn, InStrRev(fn, "\"))
     .Open PathOnly
    End With
'4b)
 Let txt = Mid$(txt, 6)  '  (Not F2 = 15083) And (Not F2 = 17388) And (Not F2 = 100)
Dim Rs As Object: Set Rs = CreateObject("ADODB.Recordset")
 Rs.Open "Select * From [tempComma.csv] Where " & txt, Cn, 3
Dim x As String
 Let x = Rs.GetString(, , ",", vbCrLf): Debug.Print x

 Set Cn = Nothing: Set Rs = Nothing
Rem 5
 Kill fn
Rem 6
 Open Replace(myCSV, ".csv", "_Filtered.csv") For Output As #1
 Print #1, x;
 Close #1
End Sub

Full macro here:

https://excelfox.com/forum/showthrea...ll=1#post13592

Typical resiults:

NSE,236,6,>,431555,A,,,,,GTT
NSE,25,6,>,431555,A,,,,,GTT
NSE,15083,6,>,431555,A,,,,,GTT
NSE,22,6,>,431555,A,,,,,GTT

**DocAElstein** · 05-28-2020, 02:56 PM

Here we go again recycle another cycle

https://www.excelforum.com/excel-pro...o-nothing.html

Originally Posted by MollyBread

Hi Experts,

I am looking for a macro details are mentioned below & plz see the sample file

Thnx For the Help

If column J has BUY then compare column K with column with column H & if column K is Greater than column H then do nothing else delete entire row
If column J has SELL then compare column K with column with column H & if column K is smaller than column H then do nothing else delete entire row
If column J has DELETE then delete entire row by vba

**DocAElstein** · 05-28-2020, 02:56 PM

gjhhjhg

**DocAElstein** · 05-28-2020, 02:56 PM

hdAHKJDD

**DocAElstein** · 05-28-2020, 02:56 PM

hdAHKJDD

**fixer** · 06-05-2020, 08:42 AM

**DocAElstein** · 06-06-2020, 05:12 PM

Originally Posted by fixer

I cant replace the csv with xlsx file,bcoz i cant do anything in that ,my system accepts csv file only so i have to put data in csv files only bcoz system doesn't accept xlsx files

This looks like your usual characteristic of pasting any quick rubbish in a reply to try to get attention.

I am probably wasting my time again in replying to anything you write….
All this I told you many many times…

“csv files” has no real meaning. It is like nonsense.

These are all file extensions:-
.csv
.xlsx
.txt
.xls
.xlsm
.xlsb

.xlsx .xls .xlsb .xlsm are usually used on Excel Files. We open them with Excel

.csv .txt are usually used on simple text files. We open them typically with a text editor, such as Notepad.
( we can try to open text files with Excel. It might work sometimes as we want. Sometimes it might not work as we want. It may give us unexpected problems and errors)

**fixer** · 06-06-2020, 05:18 PM

Doc Sir let it be
I agree what u said but since u provided the info to me that it can make error's sometime's
So I have a game plan for that also Relax
I am making one more excel file with VBA macro that when some error occur then after runing that macro everything will be in controlled ,Give me some time,No Doubt error will occur but I will be prepared for that also Doc Sir, Give me some time