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

**fixer** · 05-25-2020, 05:36 PM

& I saw this

NSE,100,6,>,431555,A,,,,,GTT
,,,,,,,,,,
,,,,,,,,,,
,,,,,,,,,,
,,,,,,,,,,
,,,,,,,,,,
,,,,,,,,,,
,,,,,,,,,,
,,,,,

After runing macro this result is incorrect, after runing the macro only 3rd row and 4th row will be deleted, rest data will be there
Plz see the sample file which i have attached fr understanding purpose

**DocAElstein** · 05-26-2020, 03:54 AM

Try number 8947918 , attempting again to explain sommething to Avanash

Alert 24 Mai Out..csv does not have a Sheet1.
Alert 24 Mai Out..csv does not have any worksheets
Alert 24 Mai Out..csv is not an Excel file. It is a text file. A text file does not have sheets. It is text data only
Alert 24 Mai Out..csv is not stored as a Notepad – that is nonsense
Alert 24 Mai Out..csv is not in Notepad
Alert 24 Mai Out..csv is not Notepad – that is nonsense
Alert 24 Mai Out..csv is not a Notepad file

Your system doesn't support any files instead of csv
=
Your system doesn't support any files instead of Text files
=
Your system doesn't support any files instead of a long single Text string

Code:

Your system doesn't support any files instead of csv = Your system doesn't support any files instead of Text files = Your system doesn't support any files instead of a long single Text string

Because ( Bcoz )
csv File = Text File = single Text string

You still do not understood any of the game of csv file
OR
There are other issues, but you are totally incapable of explaining them to anyone.
It is not possible for you to explain because:
_a) You are very bad at comunicating in English
_b) You do not understood any of the game of csv file
_c) You understand almost no VBA

I try again……

You can open ( manually ) Alert 24 Mai Out..csv with a Text editor, ( for example, Notepad is a Text editor )
Like This : Alert 24 Mai OutDotDotcsv.JPG : https://imgur.com/XgoYIqc
Attachment 2957
NSE,236,6,>,431555,A,,,,,GTT
NSE,25,6,>,431555,A,,,,,GTT
NSE,100,6,>,431555,A,,,,,GTT
NSE,22,6,>,431555,A,,,,,GTT
,,,,,,,,,,
,,,,,,,,,,
,,,,,,,,,,
,,,,,,,,,,
,,,,,,,,,,
,,,,,,,,,,
,,,,,,,,,,
,,,,,Entire row of row 3 & row 4 both will be deleted after runing the macro,,,,,

you can open automatically Alert 24 Mai Out..csv with Excel VBA like this:

Code:

Sub OpenATextFileWithExcel()
Dim PathAndFileName As String
 Let PathAndFileName = ThisWorkbook.Path & "\csv Text file Chaos\" & "Alert 24 Mai Out..csv" ' CHANGE TO SUIT                  Share ‘Alert 24 Mai Out..csv’ : https://app.box.com/s/yseazrdyfloij4ktrhy4ejdpzl0cx02e

' Open a csv text file in Excel
Dim Wb As Workbook
 Set Wb = Workbooks.Open(PathAndFileName)
End Sub

_____ Workbook: Alert 24 Mai Out..csv ( Using Excel 2007 32 bit )

_Row\^Col	A	B	C	D	E	F	K
1	NSE	236	6	>	431555	A	GTT
2	NSE	25	6	>	431555	A	GTT
3	NSE	100	6	>	431555	A	GTT
4	NSE	22	6	>	431555	A	GTT
5
6
7
8
9
10
11
12						Entire row of row 3 & row 4 both will be deleted after runing the macro

Worksheet: Alert 24 Mai Out.

This is not a csv file. It is not a text file. It is not Alert 24 Mai Out..csv This is not a workbook. This is not csv Workbook. This is not a Excel csv File
It is Excel. Excel is showing values from the text file,Alert 24 Mai Out..csv ( https://chandoo.org/forum/threads/co...2/#post-263505 )
This is not a result !!!!!
If you try to open a text csv file in Excel, Excel will try to put the values into cells. It may work sometimes. It may not work sometimes. So if you open a .csv text file with Excel it will sometimes work. It will sometimes not work. So it is bad to do this.

You can open ( manually ) Alert 24 Mai Out..csv with Excel
Like This : Alert 24 Mai OutDotDotcsv ManuallyOpenWithExcel..JPG : https://imgur.com/7pAaLVx
Attachment 2956
_____ Workbook: Alert 24 Mai Out..csv ( Using Excel 2007 32 bit )

_Row\^Col	A	B
1	NSE,236,6,>,431555,A,,,,,GTT
2	NSE,25,6,>,431555,A,,,,,GTT
3	NSE,100,6,>,431555,A,,,,,GTT
4	NSE,22,6,>,431555,A,,,,,GTT
5	,,,,,,,,,,
6	,,,,,,,,,,
7	,,,,,,,,,,
8	,,,,,,,,,,
9	,,,,,,,,,,
10	,,,,,,,,,,
11	,,,,,,,,,,
12	,,,,,Entire row of row 3 & row 4 both will be deleted after runing the macro,,,,,
13

Worksheet: Alert 24 Mai Out.

This is not a csv file. It is not a text file. It is not Alert 24 Mai Out..csv
This is not a workbook. This is not csv Workbook. This is not a Excel csv File
It is Excel. Excel is showing all text from the text file,Alert 24 Mai Out..csv . It is showing it in the first column.
This is not a result:
If you try to open a text csv file in Excel, Excel will try to put the values into cells. It may work sometimes. It may not work sometimes. So if you open a .csv text file with Excel it will sometimes work. It will sometimes not work. So it is bad to do this.

If you try to open a text csv file in Excel, Excel will try to put the values into cells. It may work sometimes. It may not work sometimes.
Sometimes the values will be put into the cells. Sometimes the entire text , including the separating comma , will be put in the first column, ( https://excelfox.com/forum/showthrea...ll=1#post13369 )
So if you open a .csv text file with Excel it will sometimes work. It will sometimes not work. So it is bad to do this.

Share ‘Alert 24 Mai Out..csv’ : https://app.box.com/s/yseazrdyfloij4ktrhy4ejdpzl0cx02e

**DocAElstein** · 05-26-2020, 02:50 PM

Originally Posted by fixer

..After runing macro this result is incorrect, after runing the macro only 3rd row and 4th row will be deleted, rest ….see the sample file which i have attached fr understanding purpose

My result is perfect, it does exactly what you asked for. You screwed up again! – I already asked you to check – as always I was wasting my time – you rush in like a Bull in a China shop paste some crap nonsense and read nothing … I said check:

Originally Posted by DocAElstein

..

Are you sure??
Please check here: https://excelfox.com/forum/showthrea...ll=1#post13430

Hey Dude, you said :

Originally Posted by fixer

...
If column H of 1.xls is greater than column D of 1.xls then calculate 1% of column D of 1.xls & add it to column D of 1.xls and compare column D of 1.xls with column I of 1.xls & if column D of 1.xls is greater than column I of 1.xls ......

You wrote in post #1 column I

Now, , in you last uploaded file, Post #4 you write….. First we will check column D is greater than Column H or column D is lower than column H, here column D is lower then column H, so If column D is lower than column H then we will calculate the 1% of column D, so 1% of column D is 11.72 so we will add 11.72 with column D so the total value of column D is 1183.72, so now we will compare 1183.72 with column H,If column H is lower than 1183.72 …

Do you see?? – you asked for column I ….so I gave you that

Code:

            If arrWs(Cnt, 4) > arrWs(Cnt, 9) Then ' If column D of 1.xls is greater than column I of 1.xls

But you want Column H, like this:

Code:

            If arrWs(Cnt, 4) > arrWs(Cnt, 8) Then ' If column D of 1.xls is greater than column H of 1.xls

If you make that change and run the macro, you get this: - Entire row of row 3 & row 4 both is deleted after running the macro:
NSE,236,6,>,431555,A,,,,,GTT
NSE,25,6,>,431555,A,,,,,GTT
NSE,100,6,>,431555,A,,,,,GTT
NSE,22,6,>,431555,A,,,,,GTT
,,,,,,,,,,
,,,,,,,,,,
,,,,,,,,,,
,,,,,,,,,,
,,,,,,,,,,
,,,,,,,,,,
,,,,,,,,,,
,,,,,Entire row of row 3 & row 4 both will be deleted after runing the macro,,,,

But you have not made any attempt to look at the macro I gave, or check anything.
So I am wasting my time helping you further..

Originally Posted by fixer

Instead of this

Code:

Let PathAndFileName = ThisWorkbook.Path & "\csv Text file Chaos\" & "Alert 24 Mai..csv"

I need this type of opening of file

Code:

Set w2 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\2.csv")

This makes no sense, since you are comparing two different things. I have explained that to you in great detail many times already.

I think I can probably guess what you are wanting, and I could probably spend a lot of time, as I have done in the past, to give you the solution that you want. But you would probably never read it, or never understand it, or never even try out any macro I give you .
So I would be wasting my time.

The biggest problem you have is yourself

You are trying to go fast by making stressed out quick rubbish posts … because of this you are missing everything, making big mistakes and so going very very slow , often you go backwards , and will never get anywhere, ever…

Alan

**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

**DocAElstein** · 05-26-2020, 02:50 PM

_____ Workbook: 1 26May.xls ( Using Excel 2007 32 bit )

_Row\^Col	C	D	E	F	G	H	I
1	Series/Expiry	Open	High	Low	Prev Close	LTP
2	EQ	1172	1240	1161.6	1227.1	1227.1	22
3	EQ	138	141.2	136.6	138.1	140	25
4	EQ	315	315	306.55	310.6	312	15083
5	EQ	33.5	34.5	32.85	33	33.2	17388
6	EQ	600	613.5	586.9	592.55	592.55	100
7	EQ	1568.8	1625	1555.4	1617.9	1617.9	236

Current question above
……………………………First we will check column D is greater than Column H or column D is lower than column H, here column D is lower then column H, so If column D is lower than column H then we will calculate the 1% of column D, so 1% of column D is 11.72 so we will add 11.72 with column D so the total value of column D is 1183.72, so now we will compare 1183.72 with column H,If column H is lower than 1183.72 then look column I data & match that with column B of alert.csv and if it matches then delete that entire row

………………………………….First we will check column D is greater than Column H or column D is lower than column H, here column D is greater then column H, so If column D is lower than column H then we will calculate the 1% of column D, so 1% of column D is 0.33 so we will subtract 0.33 with column D so the total value of column D is 33.17, so now we will compare 33.17 with column H,If column H is greater than 33.17 then look column I data & match that with column B of alert.csv and if it matches then delete that entire row

From this post
https://eileenslounge.com/viewtopic.php?f=30&t=34671

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

From here: https://www.excelforum.com/excel-pro...ntire-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