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

Printable View

Show 40 post(s) from this thread on one page

05-25-2020, 05:36 PM
fixer

1 Attachment(s)

& 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
05-25-2020, 05:36 PM
fixer

1 Attachment(s)

& 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
05-26-2020, 03:54 AM
DocAElstein

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

Try number 8947918 , attempting again to explain sommething to Avanash

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

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

Code:

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

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

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

I try again……

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

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

Code:

Sub OpenATextFileWithExcel() Dim PathAndFileName As String Let PathAndFileName = ThisWorkbook.Path & "\csv Text file Chaos\" & "Alert 24 Mai Out..csv" ' CHANGE TO SUIT Share ‘Alert 24 Mai Out..csv’ : https://app.box.com/s/yseazrdyfloij4ktrhy4ejdpzl0cx02e ' Open a csv text file in Excel Dim Wb As Workbook Set Wb = Workbooks.Open(PathAndFileName) End Sub

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

_Row\^Col
A

B

C

D

E

F

G

H

I

J

K

L

1
NSE
236

6
>
431555
A GTT

2
NSE
25

6
>
431555
A GTT

3
NSE
100

6
>
431555
A GTT

4
NSE
22

6
>
431555
A GTT

5

6

7

8

9

10

11

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

Worksheet: Alert 24 Mai Out.

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

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

_Row\^Col
A

B

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

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

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

4
NSE,22,6,>,431555,A,,,,,GTT

5
,,,,,,,,,,

6
,,,,,,,,,,

7
,,,,,,,,,,

8
,,,,,,,,,,

9
,,,,,,,,,,

10
,,,,,,,,,,

11
,,,,,,,,,,

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

13

Worksheet: Alert 24 Mai Out.

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

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

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

05-26-2020, 02:50 PM
DocAElstein

lkyjclkaskjslkahjfaskjsa
05-26-2020, 02:50 PM
DocAElstein

Conditionally compare the data & delete entire row

Quote:

Originally Posted by fixer

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

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

Quote:

Originally Posted by DocAElstein

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

Hey Dude, you said :

Quote:

Originally Posted by fixer

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

You wrote in post #1 column I

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

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

Code:

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

But you want Column H, like this:

Code:

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

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

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

Quote:

Originally Posted by fixer

Instead of this

Code:

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

I need this type of opening of file

Code:

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

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

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

The biggest problem you have is yourself

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

Alan

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

Conditionally compare the data & delete entire row

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

_Row\^Col
C

D

E

F

G

H

I

1
Series/Expiry Open High Low Prev Close LTP

2
EQ
1172

1240

1161.6

1227.1

1227.1

22

3
EQ
138

141.2

136.6

138.1

140

25

4
EQ
315

315

306.55

310.6

312

15083

5
EQ
33.5

34.5

32.85

33

33.2

17388

6
EQ
600

613.5

586.9

592.55

592.55

100

7
EQ
1568.8

1625

1555.4

1617.9

1617.9

236

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

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

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

If column J of 1.xls has buy & column H of 1.xls is not greater than column D of 1.xls then match column I data of 1.xls with column B of alert.csv and if it matches then delete that entire row of alert.csv
If column J of 1.xls has a blank cell then match column I data of 1.xls with column B of alert.csv and if it matches then delete that entire row of alert.csv
If column J of 1.xls has short & column H of 1.xls is Greater than than column D of 1.xls then match column I data of 1.xls with column B of alert.csv and if it matches then delete that entire row of alert.csv

From here: https://www.excelforum.com/excel-pro...ntire-row.html
If column J of 1.xls has buy & column H of 1.xls is not greater than column D of 1.xls then match column I data of 1.xls with column B of alert.csv and if it matches then delete that entire row of alert.csv
If column J of 1.xls has a blank cell then match column I data of 1.xls with column B of alert.csv and if it matches then delete that entire row of alert.csv
If column J of 1.xls has short & column H of 1.xls is Greater than than column D of 1.xls then match column I data of 1.xls with column B of alert.csv and if it matches then delete that entire row of alert.csv

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

1 Attachment(s)

Conditionally compare the data & delete entire row

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
Attachment 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\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;;;;;

_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

Conditionally compare the data & delete entire row

_____ 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
Attachment 2967
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<D2:D7),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<D2:D7),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<D2:D7),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<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

236
BUY =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

_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

_Row\^Col	D	H	I	J	L	O	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

_Row\^Col	D	H	I	J	L	O	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<D2:D7),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<D2:D7),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<D2:D7),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<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	236	BUY	=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)

Conditionally compare the data & delete entire row

_____ 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<D2:D7),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<D2:D7),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<D2:D7),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<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

236
BUY =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

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

_Row\^Col	D	H	I	J	L	O	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

_Row\^Col	D	H	I	J	L	O	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<D2:D7),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<D2:D7),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<D2:D7),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<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	236	BUY	=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)

Show 40 post(s) from this thread on one page

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

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