PDA

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



fixer
08-31-2019, 07:42 AM
Delete rows based on match criteria in two excel files, 1 might be .csv file .Opened in Excel=Fail Chaos
Moderator notice.
This is the start of duplicate cross posting chaos.
When the OP, Avinash, gets caught, he post one of his canned replies , like Sorry Sir, it won’t happen again.
Eventually he starts again a duplicate cross posting chaos.
When he gets caught , he post one of his canned replies , like Sorry Sir, it won’t happen again.
Eventually he starts again a duplicate cross posting chaos.
When he gets caught , he post one of his canned replies , like Sorry Sir, it won’t happen again.
Eventually he starts again a duplicate cross posting chaos.
When he gets caught , he post one of his canned replies , like Sorry Sir, it won’t happen again.
Eventually he starts again a duplicate cross posting chaos.
When he gets caught , he post one of his canned replies , like Sorry Sir, it won’t happen again.
_….. and so on
I am not sure if he does it on purpose or is insane or he is just a total dim pig shit for brains. I expect a bit of all of those….

it is all part of wasting his and lots of peoples times going around in circles making a total mess in posts everywhere because he refuses to understand anything at all about Text files.
The biggest problem is in using an Excel object to open a .csv File, which is usually not a good idea.
Sometimes you might get the impression he is understanding at least a small part of the problem, but wither it is just co incidence that what he has pasted infers that and he has no idea what he is writing, or two seconds later he forgets and we are back to the starting point and he starts again duplicating cross posting the same question… - I think there is a good chance the OP is insane, and certainly a total dim pig shit for brains. Physical violence is I think the only hope for him, and if all else fails then he should be put to death.,










I will place the vba code in sanju.xlsm
my all files are located in same place
Sheet name can be anything in both the files
I need the vba code that will open both the file and do the process and save the file

If cells of column C of sanju.csv matches with cells of column B of sanju.xlsx then delete the entire row of sanju.xlsx(here entire row means the cells which matches delete that entire row)

and after the process close and save the file so that changes should be saved

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://www.youtube.com/@alanelston2330 (https://www.youtube.com/@alanelston2330)
https://www.youtube.com/watch?v=yXaYszT11CA&lc=UgxEjo0Di9-9cnl8UnZ4AaABAg.9XYLEH1OwDIA35HNIei0z- (https://www.youtube.com/watch?v=yXaYszT11CA&lc=UgxEjo0Di9-9cnl8UnZ4AaABAg.9XYLEH1OwDIA35HNIei0z-)
https://eileenslounge.com/viewtopic.php?p=316154#p316154 (https://eileenslounge.com/viewtopic.php?p=316154#p316154)
https://www.youtube.com/watch?v=TW3l7PkSPD4&lc=UgwAL_Jrv7yg7WWC8x14AaABAg (https://www.youtube.com/watch?v=TW3l7PkSPD4&lc=UgwAL_Jrv7yg7WWC8x14AaABAg)
https://teylyn.com/2017/03/21/dollarsigns/#comment-191 (https://teylyn.com/2017/03/21/dollarsigns/#comment-191)
https://eileenslounge.com/viewtopic.php?p=317050#p317050 (https://eileenslounge.com/viewtopic.php?p=317050#p317050)
https://eileenslounge.com/viewtopic.php?f=27&t=40953&p=316854#p316854 (https://eileenslounge.com/viewtopic.php?f=27&t=40953&p=316854#p316854)
https://www.eileenslounge.com/viewtopic.php?v=27&t=40953&p=316875#p316875 (https://www.eileenslounge.com/viewtopic.php?v=27&t=40953&p=316875#p316875)
https://eileenslounge.com/viewtopic.php?p=316057#p316057 (https://eileenslounge.com/viewtopic.php?p=316057#p316057)
https://eileenslounge.com/viewtopic.php?p=316705#p316705 (https://eileenslounge.com/viewtopic.php?p=316705#p316705)
https://eileenslounge.com/viewtopic.php?p=316704#p316704 (https://eileenslounge.com/viewtopic.php?p=316704#p316704)
https://eileenslounge.com/viewtopic.php?p=176255#p176255 (https://eileenslounge.com/viewtopic.php?p=176255#p176255)
https://eileenslounge.com/viewtopic.php?f=27&t=40919&p=316597#p316597 (https://eileenslounge.com/viewtopic.php?f=27&t=40919&p=316597#p316597)
https://eileenslounge.com/viewtopic.php?p=316412#p316412 (https://eileenslounge.com/viewtopic.php?p=316412#p316412)
https://eileenslounge.com/viewtopic.php?p=316254#p316254 (https://eileenslounge.com/viewtopic.php?p=316254#p316254)
https://eileenslounge.com/viewtopic.php?p=316280#p316280 (https://eileenslounge.com/viewtopic.php?p=316280#p316280)
https://eileenslounge.com/viewtopic.php?p=315915#p315915 (https://eileenslounge.com/viewtopic.php?p=315915#p315915)
https://eileenslounge.com/viewtopic.php?p=315512#p315512 (https://eileenslounge.com/viewtopic.php?p=315512#p315512)
https://eileenslounge.com/viewtopic.php?p=315744#p315744 (https://eileenslounge.com/viewtopic.php?p=315744#p315744)
https://www.eileenslounge.com/viewtopic.php?p=315512#p315512 (https://www.eileenslounge.com/viewtopic.php?p=315512#p315512)
https://eileenslounge.com/viewtopic.php?p=315680#p315680 (https://eileenslounge.com/viewtopic.php?p=315680#p315680)
https://eileenslounge.com/viewtopic.php?p=315743#p315743 (https://eileenslounge.com/viewtopic.php?p=315743#p315743)
https://www.eileenslounge.com/viewtopic.php?p=315326#p315326 (https://www.eileenslounge.com/viewtopic.php?p=315326#p315326)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40752 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40752)
https://eileenslounge.com/viewtopic.php?p=314950#p314950 (https://eileenslounge.com/viewtopic.php?p=314950#p314950)
https://www.eileenslounge.com/viewtopic.php?p=314940#p314940 (https://www.eileenslounge.com/viewtopic.php?p=314940#p314940)
https://www.eileenslounge.com/viewtopic.php?p=314926#p314926 (https://www.eileenslounge.com/viewtopic.php?p=314926#p314926)
https://www.eileenslounge.com/viewtopic.php?p=314920#p314920 (https://www.eileenslounge.com/viewtopic.php?p=314920#p314920)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=314837#p314837 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=314837#p314837)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

fixer
09-01-2019, 09:45 AM
Sub test2()
Dim cars As Range
Dim csv As Range

Set cars = Workbooks.Open(ThisWorkbook.Path & "\1.xls") _
.Sheets(1).Cells(1).CurrentRegion

Set csv = Workbooks.Open(ThisWorkbook.Path & "\BasketOrder..csv") _
.Sheets(1).Cells(1).CurrentRegion.Columns("C")

cars.Cells(2).Copy
csv.Cells(1).Insert xlDown
Set csv = csv.Offset(-1).Resize(csv.Cells.Count + 1)

cars.AdvancedFilter xlFilterInPlace, csv
cars.Offset(1).EntireRow.Delete
cars.Parent.ShowAllData

cars.Parent.Parent.Close True
csv.Parent.Parent.Close False

End Sub


Problem Solved



** Cross Post.. mrexcel.com _ https://www.mrexcel.com/forum/excel-questions/1108504-delete-entire-row.html

DocAElstein
09-01-2019, 01:30 PM
Thanks for letting us know :)
Thanks for posting (your)** solution
Alan


P.S: You can use code tags here, just as at other Forums


Add code tags before posting :
_1 Select the code
SelectCode.JPG : https://imgur.com/shzUcmN
2390


_ 2 add tags manually, or use the #
Click on #.JPG : https://imgur.com/4h2wx5G
2391


_ Code tags are added
Code Tags are added.JPG . https://imgur.com/xMfcAKQ
2392


_ Final post now has code window:
http://www.excelfox.com/forum/showthread.php/2359-Delete-row-by-vba?p=11449&viewfull=1#post11449
FinalPostWith BB Code Code Tags.JPG : https://imgur.com/TbXzXJU
2393


**Please also tell us about any cross post, for example;
https://www.mrexcel.com/forum/excel-questions/1108504-delete-entire-row.html

**Please tell us if you have posted same question elsewhere.. Please tell us about your cross posts:
Cross Posts: _
http://www.excelfox.com/forum/showthread.php/1172-Message-To-Cross-Posters
https://www.teachexcel.com/talk/3794/copy-paste-csv-to-xlsx

fixer
09-01-2019, 09:48 PM
Sorry Doc Sir this mistake will not be repeated Again

pike
09-02-2019, 10:59 AM
https://www.ozgrid.com/forum/forum/help-forums/excel-vba-macros/1222571-delete-row-by-vba
http://www.vbforums.com/showthread.php?878151-Delete-row-by-vba
https://www.mrexcel.com/forum/excel-questions/1108504-delete-entire-row.html?highlight=Delete+row+vba
http://www.vbaexpress.com/forum/showthread.php?65786-Copy-paste
http://www.excelfox.com/forum/showthread.php/2359-Delete-row-by-vba
https://chandoo.org/forum/threads/copy-and-paste.42454/

fixer
05-24-2020, 03:55 PM
Macro will be placed in a seperate file macro.xlsm
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 then see column I and match column I of of 1.xls with column B of Alert..csv & if it matches then delete that entire row of Alert..csv
If column H of 1.xls is lower than column D of 1.xls then calculate 1% of column D of 1.xls & subtract 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 lower than column I then see column I of 1.xls and match column I of of 1.xls with column B of Alert..csv & if it matches then delete that entire row of Alert..csv
all files are located in different place
sheet name can be anything in all files


Download file link

https://drive.google.com/open?id=1MdNWXg5cttuuWC_AcFZGBD_OqBn5ZNzf
https://drive.google.com/open?id=1Mc4iObgeJeA9oga-t7jHzz1ll3jecEP1

DocAElstein
05-25-2020, 03:16 PM
Hi

Entire row of row 3 & row 4 both will be deleted after runing the macro :confused:
Are you sure??
Please check here: https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=13430&viewfull=1#post13430






You have an Excel File, 1.xls
You have a text file, Alert..csv ( I have renamed it Alert 24 Mai..csv ). We can analyse your text file, as we have done before : https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=13273&viewfull=1#post13273
Your .csv text file is using commas , for the value separator, and for the line separate it has the typical convention of vbCr & vbLf
It has 14 rows , the first 6 have your data in them : Alert 24 MaiDotDotcsv.jpg : https://imgur.com/0HsAOLj
Your .csv text file is not an Excel File. Your .csv text file is not a Notepad File.
It is just a long text string, like this:

NSE,236,6,>,431555,A,,,,,GTTNSE,25,6,>,431555,A,,,,,GTTNSE,15083,6,>,431555,A,,,,,GTTNSE,17388,6,>,431555,A,,,,,GTTNSE,100,6,>,431555,A,,,,,GTTNSE,22,6,>,431555,A,,,,,GTT,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,, ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,Entire row of row 3 & row 4 both will be deleted after running the macro,,,,,,

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 then see column I and match column I of of 1.xls with second column value data of text file Alert..csv & if it matches then delete that entire line of Alert..csv
If column H of 1.xls is lower than column D of 1.xls then calculate 1% of column D of 1.xls & subtract 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 lower than column I then see column I of 1.xls and match column I of of 1.xls with second column value data of Alert..csv & if it matches then delete that entire line of text file Alert..csv
all files are located in different place. sheet name can be anything in Excel File, 1.xls

Before
Excel File
_____ Workbook: 1.xls ( Using Excel 2007 32 bit )
Row\Col
A
B
C
D
E
F
G
H
I
J

1ExchangeSymbolSeries/ExpiryOpenHighLow Prev CloseLTP


2NSEACCEQ
1172
1240
1161.6
1227.1
1227.1
22


3NSEADANIENTEQ
138
141.2
136.6
138.1
140
25


4NSEADANIPORTSEQ
315
315
306.55
310.6
312
15083


5NSEADANIPOWEREQ
33.5
34.5
32.85
33
33.2
17388


6NSEAMARAJABATEQ
600
613.5
586.9
592.55
592.55
100


7NSEASIANPAINTEQ
1568.8
1625
1555.4
1617.9
1617.9
236


8
Worksheet: 1-Sheet1 24Mai

Your text file as uploaded ( Alert..csv https://drive.google.com/open?id=1Mc...jHzz1ll3jecEP1 )

NSE,236,6,>,431555,A,,,,,GTT
NSE,25,6,>,431555,A,,,,,GTT
NSE,15083,6,>,431555,A,,,,,GTT
NSE,17388,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,,,,,



After running macro, new text file

NSE,100,6,>,431555,A,,,,,GTT
,,,,,,,,,,
,,,,,,,,,,
,,,,,,,,,,
,,,,,,,,,,
,,,,,,,,,,
,,,,,,,,,,
,,,,,,,,,,
,,,,,Entire row of row 3 & row 4 both will be deleted after runing the macro,,,,,


Macro here: https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=13431&viewfull=1#post13431





Alan

DocAElstein
05-25-2020, 03:16 PM
Hi

Entire row of row 3 & row 4 both will be deleted after runing the macro :confused:
Are you sure??
Please check here: https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=13430&viewfull=1#post13430






You have an Excel File, 1.xls
You have a text file, Alert..csv ( I have renamed it Alert 24 Mai..csv ). We can analyse your text file, as we have done before : https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=13273&viewfull=1#post13273
Your .csv text file is using commas , for the value separator, and for the line separate it has the typical convention of vbCr & vbLf
It has 14 rows , the first 6 have your data in them : Alert 24 MaiDotDotcsv.jpg : https://imgur.com/0HsAOLj
Your .csv text file is not an Excel File. Your .csv text file is not a Notepad File.
It is just a long text string, like this:

NSE,236,6,>,431555,A,,,,,GTTNSE,25,6,>,431555,A,,,,,GTTNSE,15083,6,>,431555,A,,,,,GTTNSE,17388,6,>,431555,A,,,,,GTTNSE,100,6,>,431555,A,,,,,GTTNSE,22,6,>,431555,A,,,,,GTT,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,, ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,Entire row of row 3 & row 4 both will be deleted after running the macro,,,,,,

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 then see column I and match column I of of 1.xls with second column value data of text file Alert..csv & if it matches then delete that entire line of Alert..csv
If column H of 1.xls is lower than column D of 1.xls then calculate 1% of column D of 1.xls & subtract 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 lower than column I then see column I of 1.xls and match column I of of 1.xls with second column value data of Alert..csv & if it matches then delete that entire line of text file Alert..csv
all files are located in different place. sheet name can be anything in Excel File, 1.xls

Before
Excel File
_____ Workbook: 1.xls ( Using Excel 2007 32 bit )
Row\Col
A
B
C
D
E
F
G
H
I
J

1ExchangeSymbolSeries/ExpiryOpenHighLow Prev CloseLTP


2NSEACCEQ
1172
1240
1161.6
1227.1
1227.1
22


3NSEADANIENTEQ
138
141.2
136.6
138.1
140
25


4NSEADANIPORTSEQ
315
315
306.55
310.6
312
15083


5NSEADANIPOWEREQ
33.5
34.5
32.85
33
33.2
17388


6NSEAMARAJABATEQ
600
613.5
586.9
592.55
592.55
100


7NSEASIANPAINTEQ
1568.8
1625
1555.4
1617.9
1617.9
236


8
Worksheet: 1-Sheet1 24Mai

Your text file as uploaded ( Alert..csv https://drive.google.com/open?id=1Mc...jHzz1ll3jecEP1 )

NSE,236,6,>,431555,A,,,,,GTT
NSE,25,6,>,431555,A,,,,,GTT
NSE,15083,6,>,431555,A,,,,,GTT
NSE,17388,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,,,,,



After running macro, new text file

NSE,100,6,>,431555,A,,,,,GTT
,,,,,,,,,,
,,,,,,,,,,
,,,,,,,,,,
,,,,,,,,,,
,,,,,,,,,,
,,,,,,,,,,
,,,,,,,,,,
,,,,,Entire row of row 3 & row 4 both will be deleted after runing the macro,,,,,


Macro here: https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=13431&viewfull=1#post13431





Alan

fixer
05-25-2020, 03:43 PM
Instead of this
Let PathAndFileName = ThisWorkbook.Path & "\csv Text file Chaos\" & "Alert 24 Mai..csv"

I need this type of opening of file

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

fixer
05-25-2020, 03:43 PM
https://excelfox.com/forum/showthread.php/2505-copy-paste-the-data-if-condition-matches?p=13486&viewfull=1#post13486





Let PathAndFileName = ThisWorkbook.Path & Application.PathSeparator & "Sample2.csv"
Path is different, It is not in the same path when i put this
Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\sample2.csv")
I got error

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

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
OK , so you didn’t bother to read most of what I wrote.
A lot of time is being spent by people trying to help you, but you aren’t reading most of what is being done for you and you are posting any quick rubbish, and are expecting us to guess what you want. You don’t even check that you post correctly your question.
Wake up Bro! Or go and see a Psychologist and get your brain sorted out

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

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

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
G
H
I
J
K
L

1NSE
236
6>
431555AGTT


2NSE
25
6>
431555AGTT


3NSE
100
6>
431555AGTT


4NSE
22
6>
431555AGTT


5


6


7


8


9


10


11


12Entire 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/copy-and-paste.44182/#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
2956
_____ Workbook: Alert 24 Mai Out..csv ( Using Excel 2007 32 bit )
Row\Col
A
B

1NSE,236,6,>,431555,A,,,,,GTT


2NSE,25,6,>,431555,A,,,,,GTT


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


4NSE,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/showthread.php/2467-VBA-To-Copy-Rows-From-One-Workbook-To-text-csv-File-Based-On-Count-In-A-Different-Workbook?p=13369&viewfull=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
lkyjclkaskjslkahjfaskjsa

DocAElstein
05-26-2020, 02:50 PM
..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 purposeMy 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:
..
:confused:
Are you sure??
Please check here: https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=13430&viewfull=1#post13430
Hey Dude, you said :
...
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 (https://excelfox.com/forum/showthread.php/2500-Conditionally-delete-entire-row-with-calculation-within-files?p=13427&viewfull=1#post13427) column I

Now, , in you last uploaded file, Post #4 (https://excelfox.com/forum/showthread.php/2500-Conditionally-delete-entire-row-with-calculation-within-files?p=13434&viewfull=1#post13434) 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

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:
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..





Instead of this
Let PathAndFileName = ThisWorkbook.Path & "\csv Text file Chaos\" & "Alert 24 Mai..csv"
I need this type of opening of file

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-26-2020, 02:50 PM
_____ Workbook: 1 26May.xls ( Using Excel 2007 32 bit )
Row\Col
C
D
E
F
G
H
I

1Series/ExpiryOpenHighLow Prev CloseLTP


2EQ
1172
1240
1161.6
1227.1
1227.1
22


3EQ
138
141.2
136.6
138.1
140
25


4EQ
315
315
306.55
310.6
312
15083


5EQ
33.5
34.5
32.85
33
33.2
17388


6EQ
600
613.5
586.9
592.55
592.55
100


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

DocAElstein
05-28-2020, 02:56 PM
Analysing jindons stuff at excelforum....
https://www.excelforum.com/excel-programming-vba-macros/1317589-conditionally-compare-the-data-and-delete-entire-row.html#post5340103


_____ Workbook: 1.xls ( Using Excel 2007 32 bit )
Row\Col
A
B
C
D
E
F
G
H
I
J

1ExchangeSymbolSeries/ExpiryOpenHighLow Prev CloseLTP


2NSEACCEQ
1183.72
1240
1161.6
1227.1
1227.1
22BUY


3NSEADANIENTEQ
139.38
141.2
136.6
138.1
140
25BUY


4NSEADANIPORTSEQ
311.85
315
306.55
310.6
312
15083SHORT


5NSEADANIPOWEREQ
33.165
34.5
32.85
33
33.2
17388SHORT


6NSEAMARAJABATEQ
594
613.5
586.9
592.55
594
100


7NSEASIANPAINTEQ
1584.488
1625
1555.4
1617.9
1617.9
236BUY
Worksheet: 1-Sheet1 29May excelforum

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 ( 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 ( column B ) of of text file , alert.csv and if it matches then delete that entire record (row) of alert.csv


Text file from https://www.excelforum.com/excel-programming-vba-macros/1317589-conditionally-compare-the-data-and-delete-entire-row.html#post5339877
Alert 29May excelforum..csv : https://app.box.com/s/t8c9p6a0lqulknofdb7a22046r0943uv
AlertDotexcelforum29May.JPG : https://imgur.com/kgtz7Nk
2967
NSE,236,6,>,431555,A,,,,,GTT
NSE,25,6,>,431555,A,,,,,GTT
NSE,15083,6,>,431555,A,,,,,GTT
NSE,17388,6,>,431555,A,,,,,GTT
NSE,100,6,>,431555,A,,,,,GTT
NSE,22,6,>,431555,A,,,,,GTT
,,,,,,,,,,
,,,,,,,,,,
,,,,,,,,,,
,,,,,,,,,,
,,,,,,,,,,
,,,,,,,,,,
,,,,,,,,,,
,,,,,row 3 & row 4 & row 5 will be deleted after runing the macro,,,,,

File as seen by ADO stuff… ( )

Records\FieldsF1F2F3F4F5F6F7F8F9F10F11

Record1NSE2366>431555AGTT

Record2NSE256>431555AGTT

Record3NSE150836>431555AGTT

Record4NSE173886>431555AGTT

Record5NSE1006>431555AGTT

Record6NSE226>431555AGTT

Record7

Record8

Record9row 3 & row 4 & row 5 will be deleted after runing the macro


Sometimes we may have this
NSE;236;6;>;431555;A;;;;;GTT
NSE;25;6;>;431555;A;;;;;GTT
NSE;15083;6;>;431555;A;;;;;GTT
NSE;17388;6;>;431555;A;;;;;GTT
NSE;100;6;>;431555;A;;;;;GTT
NSE;22;6;>;431555;A;;;;;GTT
;;;;;;;;;;
;;;;;;;;;;
;;;;;;;;;;
;;;;;;;;;;
;;;;;;;;;;
;;;;;;;;;;
;;;;;;;;;;
;;;;;row 3 & row 4 & row 5 will be deleted after runing the macro;;;;;

DocAElstein
05-28-2020, 02:56 PM
_____ Workbook: 1.xls ( Using Excel 2007 32 bit )
Row\Col
A
B
C
D
E
F
G
H
I
J

1ExchangeSymbolSeries/ExpiryOpenHighLow Prev CloseLTP


2NSEACCEQ
1183.72
1240
1161.6
1227.1
1227.1
22BUY


3NSEADANIENTEQ
139.38
141.2
136.6
138.1
140
25BUY


4NSEADANIPORTSEQ
311.85
315
306.55
310.6
312
15083SHORT


5NSEADANIPOWEREQ
33.165
34.5
32.85
33
33.2
17388SHORT


6NSEAMARAJABATEQ
594
613.5
586.9
592.55
594
100


7NSEASIANPAINTEQ
1584.488
1625
1555.4
1617.9
1617.9
236BUY
Worksheet: 1-Sheet1 29May excelforum

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 ( 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 ( column B ) of of text file , alert.csv and if it matches then delete that entire record (row) of alert.csv


Text file from https://www.excelforum.com/excel-programming-vba-macros/1317589-conditionally-compare-the-data-and-delete-entire-row.html#post5339877
Alert 29May excelforum..csv : https://app.box.com/s/t8c9p6a0lqulknofdb7a22046r0943uv
AlertDotexcelforum29May.JPG : https://imgur.com/kgtz7Nk
2967
File as seen by ADO stuff… ( )

Records\FieldsF1F2F3F4F5F6F7F8F9F10F11

Record1NSE2366>431555AGTT

Record2NSE256>431555AGTT

Record3NSE150836>431555AGTT

Record4NSE173886>431555AGTT

Record5NSE1006>431555AGTT

Record6NSE226>431555AGTT

Record7

Record8

Record9row 3 & row 4 & row 5 will be deleted after runing the macro




_____ Workbook: 1.xls ( Using Excel 2007 32 bit )
Row\ColDHIJKLMNOPQR
1OpenLTPIf column J of 1.xls has buy & column H of 1.xls is not greater than column D of 1.xls If column J of 1.xls has short & column H of 1.xls is Greater than than column D of 1.xlsIf column J of 1.xls has a blank cell

2
1183.72
1227.1
22BUYFALSEFALSEFALSE

3
139.38
140
25BUYFALSEFALSEFALSE

4
311.85
312
15083SHORTFALSE
15083FALSE

5
33.165
33.2
17388SHORTFALSE
17388FALSE

6
594
594
100FALSEFALSE
100

7
1584.488
1617.9
236BUYFALSEFALSEFALSE
Worksheet: 1-Sheet1 29May excelforum

_____ Workbook: 1.xls ( Using Excel 2007 32 bit )
Row\ColDHIJKLMNOPQR
1OpenLTPIf column J of 1.xls has buy & column H of 1.xls is not greater than column D of 1.xls If column J of 1.xls has short & column H of 1.xls is Greater than than column D of 1.xlsIf column J of 1.xls has a blank cell

2
1183.72
1227.1
22BUY=IF((J2:J7="buy")*(H2:H7<D2:D7),I2:I7)=IF((J2:J7="short")*(H2:H7>D2:D7),I2:I7)=IF(J2:J7="",I2:I7)

3
139.38
140
25BUY=IF((J2:J7="buy")*(H2:H7<D2:D7),I2:I7)=IF((J2:J7="short")*(H2:H7>D2:D7),I2:I7)=IF(J2:J7="",I2:I7)

4
311.85
312
15083SHORT=IF((J2:J7="buy")*(H2:H7<D2:D7),I2:I7)
=IF((J2:J7="short")*(H2:H7>D2:D7),I2:I7)=IF(J2:J7="",I2:I7)

5
33.165
33.2
17388SHORT=IF((J2:J7="buy")*(H2:H7<D2:D7),I2:I7)
=IF((J2:J7="short")*(H2:H7>D2:D7),I2:I7)=IF(J2:J7="",I2:I7)

6
594
594
100=IF((J2:J7="buy")*(H2:H7<D2:D7),I2:I7)=IF((J2:J7="short")*(H2:H7>D2:D7),I2:I7)
=IF(J2:J7="",I2:I7)

7
1584.488
1617.9
236BUY=IF((J2:J7="buy")*(H2:H7<D2:D7),I2:I7)=IF((J2:J7="short")*(H2:H7>D2:D7),I2:I7)=IF(J2:J7="",I2:I7)
Worksheet: 1-Sheet1 29May excelforum

DocAElstein
05-28-2020, 02:56 PM
_____ Workbook: 1.xls ( Using Excel 2007 32 bit )
Row\ColDHIJKLMNOPQR
1OpenLTPIf column J of 1.xls has buy & column H of 1.xls is not greater than column D of 1.xls If column J of 1.xls has short & column H of 1.xls is Greater than than column D of 1.xlsIf column J of 1.xls has a blank cell

2
1183.72
1227.1
22BUYFALSEFALSEFALSE

3
139.38
140
25BUYFALSEFALSEFALSE

4
311.85
312
15083SHORTFALSE
15083FALSE

5
33.165
33.2
17388SHORTFALSE
17388FALSE

6
594
594
100FALSEFALSE
100

7
1584.488
1617.9
236BUYFALSEFALSEFALSE
Worksheet: 1-Sheet1 29May excelforum

_____ Workbook: 1.xls ( Using Excel 2007 32 bit )
Row\ColDHIJKLMNOPQR
1OpenLTPIf column J of 1.xls has buy & column H of 1.xls is not greater than column D of 1.xls If column J of 1.xls has short & column H of 1.xls is Greater than than column D of 1.xlsIf column J of 1.xls has a blank cell

2
1183.72
1227.1
22BUY=IF((J2:J7="buy")*(H2:H7<D2:D7),I2:I7)=IF((J2:J7="short")*(H2:H7>D2:D7),I2:I7)=IF(J2:J7="",I2:I7)

3
139.38
140
25BUY=IF((J2:J7="buy")*(H2:H7<D2:D7),I2:I7)=IF((J2:J7="short")*(H2:H7>D2:D7),I2:I7)=IF(J2:J7="",I2:I7)

4
311.85
312
15083SHORT=IF((J2:J7="buy")*(H2:H7<D2:D7),I2:I7)
=IF((J2:J7="short")*(H2:H7>D2:D7),I2:I7)=IF(J2:J7="",I2:I7)

5
33.165
33.2
17388SHORT=IF((J2:J7="buy")*(H2:H7<D2:D7),I2:I7)
=IF((J2:J7="short")*(H2:H7>D2:D7),I2:I7)=IF(J2:J7="",I2:I7)

6
594
594
100=IF((J2:J7="buy")*(H2:H7<D2:D7),I2:I7)=IF((J2:J7="short")*(H2:H7>D2:D7),I2:I7)
=IF(J2:J7="",I2:I7)

7
1584.488
1617.9
236BUY=IF((J2:J7="buy")*(H2:H7<D2:D7),I2:I7)=IF((J2:J7="short")*(H2:H7>D2:D7),I2:I7)=IF(J2:J7="",I2:I7)
Worksheet: 1-Sheet1 29May excelforum



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 buy & column H of 1.xls is not greater than column D of 1.xls
FALSE
FALSE
FALSE
FALSE
FALSE
FALSEFilter to match to not false = Empty



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 a blank cell
FALSE
FALSE
FALSE
FALSE
100
FALSEFilter to match to not false = {100}



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

If column J of 1.xls has short & column H of 1.xls is Greater than than column D of 1.xls
FALSE
FALSE
15083
17388
FALSE
FALSEFilter to match to not false = {15083, 17388}



final txt , after running Sub jindonsTesties() , is And (Not F2 = 15083) And (Not F2 = 17388) And (Not F2 = 100)



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 buy & column H of 1.xls is not greater than column D of 1.xls
=TRANSPOSE(IF((J2:J7="buy")*(H2:H7<D2:D7),I2:I7))
=TRANSPOSE(IF((J2:J7="buy")*(H2:H7<D2:D7),I2:I7))
=TRANSPOSE(IF((J2:J7="buy")*(H2:H7<D2:D7),I2:I7))
=TRANSPOSE(IF((J2:J7="buy")*(H2:H7<D2:D7),I2:I7))
=TRANSPOSE(IF((J2:J7="buy")*(H2:H7<D2:D7),I2:I7))
=TRANSPOSE(IF((J2:J7="buy")*(H2:H7<D2:D7),I2:I7))Filter to match to not false = Empty



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 a blank cell
=TRANSPOSE(IF(J2:J7="",I2:I7))
=TRANSPOSE(IF(J2:J7="",I2:I7))
=TRANSPOSE(IF(J2:J7="",I2:I7))
=TRANSPOSE(IF(J2:J7="",I2:I7))
=TRANSPOSE(IF(J2:J7="",I2:I7))
=TRANSPOSE(IF(J2:J7="",I2:I7))Filter to match to not false = {100}



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

If column J of 1.xls has short & column H of 1.xls is Greater than than column D of 1.xls
=TRANSPOSE(IF((J2:J7="short")*(H2:H7>D2:D7),I2:I7))
=TRANSPOSE(IF((J2:J7="short")*(H2:H7>D2:D7),I2:I7))
=TRANSPOSE(IF((J2:J7="short")*(H2:H7>D2:D7),I2:I7))
=TRANSPOSE(IF((J2:J7="short")*(H2:H7>D2:D7),I2:I7))
=TRANSPOSE(IF((J2:J7="short")*(H2:H7>D2:D7),I2:I7))
=TRANSPOSE(IF((J2:J7="short")*(H2:H7>D2:D7),I2:I7))Filter to match to not false = {15083, 17388}



final txt , after running Sub jindonsTesties() , is And (Not F2 = 15083) And (Not F2 = 17388) And (Not F2 = 100)



final txt , after running Sub jindonsTesties() , Part 1== , is
And (Not F2 = 15083) And (Not F2 = 17388) And (Not F2 = 100)

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/showthread.php/2500-Conditionally-delete-entire-row-with-calculation-within-files/page2#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)



' 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 & "<d2:d" & LR & "),i2:i" & LR & "))")
Let arrTemp() = Ws1.Evaluate("transpose(if((j2:j" & LR & "=""buy"")*(h2:h" & LR & "<d2:d" & LR & "),i2:i" & 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-programming-vba-macros/1182627-help-understanding-ado-codes-and-similar-to-get-data-quick-ace-jet-peculiarities.html
https://www.excelforum.com/excel-programming-vba-macros/1186683-excel-ado-messing-about-with-vblf-vbcr-and-vbcrlf-text-file-reading-ado-screws-up.html#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 _…
(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 )


' 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 [color=blue]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/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=13592&viewfull=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-programming-vba-macros/1321463-if-condition-match-then-delete-entire-row-by-vba-else-do-nothing.html


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

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://www.youtube.com/@alanelston2330 (https://www.youtube.com/@alanelston2330)
https://www.youtube.com/watch?v=yXaYszT11CA&lc=UgxEjo0Di9-9cnl8UnZ4AaABAg.9XYLEH1OwDIA35HNIei0z- (https://www.youtube.com/watch?v=yXaYszT11CA&lc=UgxEjo0Di9-9cnl8UnZ4AaABAg.9XYLEH1OwDIA35HNIei0z-)
https://eileenslounge.com/viewtopic.php?p=316154#p316154 (https://eileenslounge.com/viewtopic.php?p=316154#p316154)
https://www.youtube.com/watch?v=TW3l7PkSPD4&lc=UgwAL_Jrv7yg7WWC8x14AaABAg (https://www.youtube.com/watch?v=TW3l7PkSPD4&lc=UgwAL_Jrv7yg7WWC8x14AaABAg)
https://teylyn.com/2017/03/21/dollarsigns/#comment-191 (https://teylyn.com/2017/03/21/dollarsigns/#comment-191)
https://eileenslounge.com/viewtopic.php?p=317050#p317050 (https://eileenslounge.com/viewtopic.php?p=317050#p317050)
https://eileenslounge.com/viewtopic.php?f=27&t=40953&p=316854#p316854 (https://eileenslounge.com/viewtopic.php?f=27&t=40953&p=316854#p316854)
https://www.eileenslounge.com/viewtopic.php?v=27&t=40953&p=316875#p316875 (https://www.eileenslounge.com/viewtopic.php?v=27&t=40953&p=316875#p316875)
https://eileenslounge.com/viewtopic.php?p=316057#p316057 (https://eileenslounge.com/viewtopic.php?p=316057#p316057)
https://eileenslounge.com/viewtopic.php?p=316705#p316705 (https://eileenslounge.com/viewtopic.php?p=316705#p316705)
https://eileenslounge.com/viewtopic.php?p=316704#p316704 (https://eileenslounge.com/viewtopic.php?p=316704#p316704)
https://eileenslounge.com/viewtopic.php?p=176255#p176255 (https://eileenslounge.com/viewtopic.php?p=176255#p176255)
https://eileenslounge.com/viewtopic.php?f=27&t=40919&p=316597#p316597 (https://eileenslounge.com/viewtopic.php?f=27&t=40919&p=316597#p316597)
https://eileenslounge.com/viewtopic.php?p=316412#p316412 (https://eileenslounge.com/viewtopic.php?p=316412#p316412)
https://eileenslounge.com/viewtopic.php?p=316254#p316254 (https://eileenslounge.com/viewtopic.php?p=316254#p316254)
https://eileenslounge.com/viewtopic.php?p=316280#p316280 (https://eileenslounge.com/viewtopic.php?p=316280#p316280)
https://eileenslounge.com/viewtopic.php?p=315915#p315915 (https://eileenslounge.com/viewtopic.php?p=315915#p315915)
https://eileenslounge.com/viewtopic.php?p=315512#p315512 (https://eileenslounge.com/viewtopic.php?p=315512#p315512)
https://eileenslounge.com/viewtopic.php?p=315744#p315744 (https://eileenslounge.com/viewtopic.php?p=315744#p315744)
https://www.eileenslounge.com/viewtopic.php?p=315512#p315512 (https://www.eileenslounge.com/viewtopic.php?p=315512#p315512)
https://eileenslounge.com/viewtopic.php?p=315680#p315680 (https://eileenslounge.com/viewtopic.php?p=315680#p315680)
https://eileenslounge.com/viewtopic.php?p=315743#p315743 (https://eileenslounge.com/viewtopic.php?p=315743#p315743)
https://www.eileenslounge.com/viewtopic.php?p=315326#p315326 (https://www.eileenslounge.com/viewtopic.php?p=315326#p315326)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40752 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40752)
https://eileenslounge.com/viewtopic.php?p=314950#p314950 (https://eileenslounge.com/viewtopic.php?p=314950#p314950)
https://www.eileenslounge.com/viewtopic.php?p=314940#p314940 (https://www.eileenslounge.com/viewtopic.php?p=314940#p314940)
https://www.eileenslounge.com/viewtopic.php?p=314926#p314926 (https://www.eileenslounge.com/viewtopic.php?p=314926#p314926)
https://www.eileenslounge.com/viewtopic.php?p=314920#p314920 (https://www.eileenslounge.com/viewtopic.php?p=314920#p314920)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=314837#p314837 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=314837#p314837)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

DocAElstein
06-06-2020, 05:12 PM
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 filesThis 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

fixer
06-09-2020, 06:00 PM
Problem Solved