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

08-31-2019, 07:42 AM
fixer

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
09-01-2019, 09:45 AM
fixer

Code:

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-...ntire-row.html
09-01-2019, 01:30 PM
DocAElstein

4 Attachment(s)

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

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

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

_ Final post now has code window:
http://www.excelfox.com/forum/showth...ll=1#post11449
FinalPostWith BB Code Code Tags.JPG : https://imgur.com/TbXzXJU
Attachment 2393

**Please also tell us about any cross post, for example;
https://www.mrexcel.com/forum/excel-...ntire-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/showth...-Cross-Posters
https://www.teachexcel.com/talk/3794...te-csv-to-xlsx
09-01-2019, 09:48 PM
fixer

Sorry Doc Sir this mistake will not be repeated Again
09-02-2019, 10:59 AM
pike

https://www.ozgrid.com/forum/forum/h...ete-row-by-vba
http://www.vbforums.com/showthread.p...ete-row-by-vba
https://www.mrexcel.com/forum/excel-...Delete+row+vba
http://www.vbaexpress.com/forum/show...786-Copy-paste
http://www.excelfox.com/forum/showth...ete-row-by-vba
https://chandoo.org/forum/threads/copy-and-paste.42454/
05-24-2020, 03:55 PM
fixer

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

Hi

Quote:

Originally Posted by fixer

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

Code:

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 )

Code:

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

Code:

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

05-25-2020, 03:43 PM
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")
05-25-2020, 03:43 PM
fixer

https://excelfox.com/forum/showthrea...ll=1#post13486

Quote:

Originally Posted by fixer

Code:

Let PathAndFileName = ThisWorkbook.Path & Application.PathSeparator & "Sample2.csv"

Path is different, It is not in the same path when i put this

Code:

Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\sample2.csv")

I got error

_Row\^Col	A	B	C	D	E	F	G	H	I
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

_Row\^Col	A	B	C	D	E	F	G	H	I
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

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