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

[DocAElstein]

lkyjclkaskjslkahjfaskjsa

[DocAElstein]

Analysing jindons stuff at excelforum....
https://www.excelforum.com/excel-pro...ml#post5340103

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

Row\Col	A	B	C	D	E	F	G	H	I	J
1	Exchange	Symbol	Series/Expiry	Open	High	Low	Prev Close	LTP
2	NSE	ACC	EQ	1183.72	1240	1161.6	1227.1	1227.1	22	BUY
3	NSE	ADANIENT	EQ	139.38	141.2	136.6	138.1	140	25	BUY
4	NSE	ADANIPORTS	EQ	311.85	315	306.55	310.6	312	15083	SHORT
5	NSE	ADANIPOWER	EQ	33.165	34.5	32.85	33	33.2	17388	SHORT
6	NSE	AMARAJABAT	EQ	594	613.5	586.9	592.55	594	100
7	NSE	ASIANPAINT	EQ	1584.488	1625	1555.4	1617.9	1617.9	236	BUY

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-pro...ml#post5339877
Alert 29May excelforum..csv : https://app.box.com/s/t8c9p6a0lqulknofdb7a22046r0943uv
AlertDotexcelforum29May.JPG : https://imgur.com/kgtz7Nk
AlertDotexcelforum29May.jpg
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\Fields	F1	F2	F3	F4	F5	F6	F7	F8	F9	F10	F11
Record1	NSE	236	6	>	431555	A					GTT
Record2	NSE	25	6	>	431555	A					GTT
Record3	NSE	15083	6	>	431555	A					GTT
Record4	NSE	17388	6	>	431555	A					GTT
Record5	NSE	100	6	>	431555	A					GTT
Record6	NSE	22	6	>	431555	A					GTT
Record7
Record8
Record9					row 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]

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

Row\Col	A	B	C	D	E	F	G	H	I	J
1	Exchange	Symbol	Series/Expiry	Open	High	Low	Prev Close	LTP
2	NSE	ACC	EQ	1183.72	1240	1161.6	1227.1	1227.1	22	BUY
3	NSE	ADANIENT	EQ	139.38	141.2	136.6	138.1	140	25	BUY
4	NSE	ADANIPORTS	EQ	311.85	315	306.55	310.6	312	15083	SHORT
5	NSE	ADANIPOWER	EQ	33.165	34.5	32.85	33	33.2	17388	SHORT
6	NSE	AMARAJABAT	EQ	594	613.5	586.9	592.55	594	100
7	NSE	ASIANPAINT	EQ	1584.488	1625	1555.4	1617.9	1617.9	236	BUY

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-pro...ml#post5339877
Alert 29May excelforum..csv : https://app.box.com/s/t8c9p6a0lqulknofdb7a22046r0943uv
AlertDotexcelforum29May.JPG : https://imgur.com/kgtz7Nk
AlertDotexcelforum29May.jpg
File as seen by ADO stuff… ( )

Records\Fields	F1	F2	F3	F4	F5	F6	F7	F8	F9	F10	F11
Record1	NSE	236	6	>	431555	A					GTT
Record2	NSE	25	6	>	431555	A					GTT
Record3	NSE	15083	6	>	431555	A					GTT
Record4	NSE	17388	6	>	431555	A					GTT
Record5	NSE	100	6	>	431555	A					GTT
Record6	NSE	22	6	>	431555	A					GTT
Record7
Record8
Record9					row 3 & row 4 & row 5 will be deleted after runing the macro

---

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

Row\Col	D	H	I	J	K	L	M	N	O	P	Q	R
1	Open	LTP				If 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.xls			If column J of 1.xls has a blank cell
2	1183.72	1227.1	22	BUY		FALSE			FALSE			FALSE
3	139.38	140	25	BUY		FALSE			FALSE			FALSE
4	311.85	312	15083	SHORT		FALSE			15083			FALSE
5	33.165	33.2	17388	SHORT		FALSE			17388			FALSE
6	594	594	100			FALSE			FALSE			100
7	1584.488	1617.9	236	BUY		FALSE			FALSE			FALSE

Worksheet: 1-Sheet1 29May excelforum

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

Row\Col	D	H	I	J	K	L	M	N	O	P	Q	R
1	Open	LTP				If 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.xls			If column J of 1.xls has a blank cell
2	1183.72	1227.1	22	BUY		=IF((J2:J7="buy")*(H2:H7),I2:I7)			=IF((J2:J7="short")*(H2:H7>D2:D7),I2:I7)			=IF(J2:J7="",I2:I7)
3	139.38	140	25	BUY		=IF((J2:J7="buy")*(H2:H7),I2:I7)			=IF((J2:J7="short")*(H2:H7>D2:D7),I2:I7)			=IF(J2:J7="",I2:I7)
4	311.85	312	15083	SHORT		=IF((J2:J7="buy")*(H2:H7),I2:I7)			=IF((J2:J7="short")*(H2:H7>D2:D7),I2:I7)			=IF(J2:J7="",I2:I7)
5	33.165	33.2	17388	SHORT		=IF((J2:J7="buy")*(H2:H7),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),I2:I7)			=IF((J2:J7="short")*(H2:H7>D2:D7),I2:I7)			=IF(J2:J7="",I2:I7)
7	1584.488	1617.9	236	BUY		=IF((J2:J7="buy")*(H2:H7),I2:I7)			=IF((J2:J7="short")*(H2:H7>D2:D7),I2:I7)			=IF(J2:J7="",I2:I7)

Worksheet: 1-Sheet1 29May excelforum

[DocAElstein]

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

Row\Col	D	H	I	J	K	L	M	N	O	P	Q	R
1	Open	LTP				If 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.xls			If column J of 1.xls has a blank cell
2	1183.72	1227.1	22	BUY		FALSE			FALSE			FALSE
3	139.38	140	25	BUY		FALSE			FALSE			FALSE
4	311.85	312	15083	SHORT		FALSE			15083			FALSE
5	33.165	33.2	17388	SHORT		FALSE			17388			FALSE
6	594	594	100			FALSE			FALSE			100
7	1584.488	1617.9	236	BUY		FALSE			FALSE			FALSE

Worksheet: 1-Sheet1 29May excelforum

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

Row\Col	D	H	I	J	K	L	M	N	O	P	Q	R
1	Open	LTP				If 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.xls			If column J of 1.xls has a blank cell
2	1183.72	1227.1	22	BUY		=IF((J2:J7="buy")*(H2:H7),I2:I7)			=IF((J2:J7="short")*(H2:H7>D2:D7),I2:I7)			=IF(J2:J7="",I2:I7)
3	139.38	140	25	BUY		=IF((J2:J7="buy")*(H2:H7),I2:I7)			=IF((J2:J7="short")*(H2:H7>D2:D7),I2:I7)			=IF(J2:J7="",I2:I7)
4	311.85	312	15083	SHORT		=IF((J2:J7="buy")*(H2:H7),I2:I7)			=IF((J2:J7="short")*(H2:H7>D2:D7),I2:I7)			=IF(J2:J7="",I2:I7)
5	33.165	33.2	17388	SHORT		=IF((J2:J7="buy")*(H2:H7),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),I2:I7)			=IF((J2:J7="short")*(H2:H7>D2:D7),I2:I7)			=IF(J2:J7="",I2:I7)
7	1584.488	1617.9	236	BUY		=IF((J2:J7="buy")*(H2:H7),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

FALSE

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

FALSE

FALSE

FALSE

FALSE

100

FALSE

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

FALSE

FALSE

15083

17388

FALSE

FALSE

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)

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),I2:I7))

=TRANSPOSE(IF((J2:J7="buy")*(H2:H7),I2:I7))

=TRANSPOSE(IF((J2:J7="buy")*(H2:H7),I2:I7))

=TRANSPOSE(IF((J2:J7="buy")*(H2:H7),I2:I7))

=TRANSPOSE(IF((J2:J7="buy")*(H2:H7),I2:I7))

=TRANSPOSE(IF((J2:J7="buy")*(H2:H7),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)

[fixer]

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

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.youtube.com/@alanelston2330
https://www.youtube.com/watch?v=yXaYszT11CA&lc=UgxEjo0Di9-9cnl8UnZ4AaABAg.9XYLEH1OwDIA35HNIei0z-
https://eileenslounge.com/viewtopic.php?p=316154#p316154
https://www.youtube.com/watch?v=TW3l7PkSPD4&lc=UgwAL_Jrv7yg7WWC8x14AaABAg
https://teylyn.com/2017/03/21/dollarsigns/#comment-191
https://eileenslounge.com/viewtopic.php?p=317050#p317050
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://eileenslounge.com/viewtopic.php?p=316057#p316057
https://eileenslounge.com/viewtopic.php?p=316705#p316705
https://eileenslounge.com/viewtopic.php?p=316704#p316704
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?p=316412#p316412
https://eileenslounge.com/viewtopic.php?p=316254#p316254
https://eileenslounge.com/viewtopic.php?p=316280#p316280
https://eileenslounge.com/viewtopic.php?p=315915#p315915
https://eileenslounge.com/viewtopic.php?p=315512#p315512
https://eileenslounge.com/viewtopic.php?p=315744#p315744
https://www.eileenslounge.com/viewtopic.php?p=315512#p315512
https://eileenslounge.com/viewtopic.php?p=315680#p315680
https://eileenslounge.com/viewtopic.php?p=315743#p315743
https://www.eileenslounge.com/viewtopic.php?p=315326#p315326
https://www.eileenslounge.com/viewtopic.php?f=30&t=40752
https://eileenslounge.com/viewtopic.php?p=314950#p314950
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=314920#p314920
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=314837#p314837
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

[DocAElstein]

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

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

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

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

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

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

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


https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg.8xzeMdC8IOGADdPM65i9 PG
https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg.8xzeMdC8IOGADdPQHFk_ zm
http://www.eileenslounge.com/viewtopic.php?p=324457#p324457
http://www.eileenslounge.com/viewtopic.php?p=324064#p324064
http://www.eileenslounge.com/viewtopic.php?p=323960#p323960
https://www.youtube.com/watch?v=7VwD9KuyMk4&lc=UgyZCnNfnZRfgwzDlQF4AaABAg
https://www.youtube.com/watch?v=7VwD9KuyMk4&lc=UgyZCnNfnZRfgwzDlQF4AaABAg.ADd4m2zp_xDADd6Nnotj 1C
s://www.youtube.com/watch?v=7VwD9KuyMk4&lc=UgySdtXqcaA27wQLd1t4AaABAg
http://www.eileenslounge.com/viewtopic.php?p=323959#p323959
http://www.eileenslounge.com/viewtopic.php?f=30&t=41784
http://www.eileenslounge.com/viewtopic.php?p=323966#p323966
http://www.eileenslounge.com/viewtopic.php?p=323959#p323959
http://www.eileenslounge.com/viewtopic.php?p=323960#p323960
http://www.eileenslounge.com/viewtopic.php?p=323894#p323894
http://www.eileenslounge.com/viewtopic.php?p=323843#p323843
https://www.youtube.com/watch?v=fEHKPhJxgBA&lc=Ugxx8_MjhC9FDaQgcHN4AaABAg
https://www.youtube.com/watch?v=jpjYm4UvyWk&lc=Ugx_Qd4rfAN_ZYcJbo94AaABAg.ACGbG9c76OWACGbjKa7H 8k
https://www.youtube.com/watch?v=jpjYm4UvyWk&lc=Ugx_Qd4rfAN_ZYcJbo94AaABAg
https://www.youtube.com/watch?v=GyPHaydeng0&lc=UgzE4a4f_e_y9Rk5OR94AaABAg
https://www.youtube.com/watch?v=I5FkNG94BcQ&lc=UgxXnkEHqulXSR5tXwh4AaABAg
https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg.8xzeMdC8IOGABa6BSa17 3Z
https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg.8xzeMdC8IOGABa6-64Xpgl
https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg.8xzeMdC8IOGABa5ms39y jd
https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg.8xzeMdC8IOGABa5ZXJwR CM
https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg.8xzeMdC8IOGABa4Pr15N Ut
https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg.8xzeMdC8IOGABa4I83Je lY
https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg.8xzeMdC8IOGADdMo2n-hyF
https://www.youtube.com/watch?v=C43btudYyzA&lc=Ugyf349Ue6_4umFfNUB4AaABAg.8mjgPNoTt_HABa3tnAjh ZU
http://www.eileenslounge.com/viewtopic.php?p=323547#p323547
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

[fixer]

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

[DocAElstein]

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

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

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

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

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

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

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

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

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

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

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

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

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

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

See next post…

[DocAElstein]

Hi

Entire row of row 3 & row 4 both will be deleted after runing the macro

Are you sure??
Please check here: https://excelfox.com/forum/showthrea...ll=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/showthrea...ll=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
1	Exchange	Symbol	Series/Expiry	Open	High	Low	Prev Close	LTP
2	NSE	ACC	EQ	1172	1240	1161.6	1227.1	1227.1	22
3	NSE	ADANIENT	EQ	138	141.2	136.6	138.1	140	25
4	NSE	ADANIPORTS	EQ	315	315	306.55	310.6	312	15083
5	NSE	ADANIPOWER	EQ	33.5	34.5	32.85	33	33.2	17388
6	NSE	AMARAJABAT	EQ	600	613.5	586.9	592.55	592.55	100
7	NSE	ASIANPAINT	EQ	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/showthrea...ll=1#post13431





Alan

[fixer]

https://excelfox.com/forum/showthrea...ll=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