Delete rows based on match criteria in two excel files, 1 might be .csv file .Opened in Excel=Fail Chaos
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/@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
Conditionally delete entire row with calculation within files
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=1Md...ZGBD_OqBn5ZNzf
https://drive.google.com/open?id=1Mc...jHzz1ll3jecEP1
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
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
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
1 Attachment(s)
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 |
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;;;;;
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 |
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 |
_____ 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) |
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 |
_____ 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) |
| 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)
Conditionally compare the data & delete entire row
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)
Code:
' 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…
If condition match then delete entire row by vba else do nothing
Here we go again recycle another cycle
https://www.excelforum.com/excel-pro...o-nothing.html
Quote:
Originally Posted by MollyBread
Hi Experts,
I am looking for a macro details are mentioned below & plz see the sample file
Thnx For the Help
If column J has BUY then compare column K with column with column H & if column K is Greater than column H then do nothing else delete entire row
If column J has SELL then compare column K with column with column H & if column K is smaller than column H then do nothing else delete entire row
If column J has DELETE then delete entire row by vba
Conditionally compare the data & delete entire row
Conditionally compare the data & delete entire row
Conditionally compare the data & delete entire row
