Delete rows based on match criteria in two excel files, 1 might be .csv file .Opened in Excel=Fail Chaos [Archive] - Excel, Access, PowerPoint and Word VBA Macro Automation Help

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

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

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