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

**fixer** · 08-31-2019, 07:42 AM

Delete rows based on match criteria in two excel files, 1 might be .csv file .Opened in Excel=Fail Chaos
Moderator notice.
This is the start of duplicate cross posting chaos.
When the OP, Avinash, gets caught, he post one of his canned replies , like Sorry Sir, it won’t happen again.
Eventually he starts again a duplicate cross posting chaos.
When he gets caught , he post one of his canned replies , like Sorry Sir, it won’t happen again.
Eventually he starts again a duplicate cross posting chaos.
When he gets caught , he post one of his canned replies , like Sorry Sir, it won’t happen again.
Eventually he starts again a duplicate cross posting chaos.
When he gets caught , he post one of his canned replies , like Sorry Sir, it won’t happen again.
Eventually he starts again a duplicate cross posting chaos.
When he gets caught , he post one of his canned replies , like Sorry Sir, it won’t happen again.
_….. and so on
I am not sure if he does it on purpose or is insane or he is just a total dim pig shit for brains. I expect a bit of all of those….

it is all part of wasting his and lots of peoples times going around in circles making a total mess in posts everywhere because he refuses to understand anything at all about Text files.
The biggest problem is in using an Excel object to open a .csv File, which is usually not a good idea.
Sometimes you might get the impression he is understanding at least a small part of the problem, but wither it is just co incidence that what he has pasted infers that and he has no idea what he is writing, or two seconds later he forgets and we are back to the starting point and he starts again duplicating cross posting the same question… - I think there is a good chance the OP is insane, and certainly a total dim pig shit for brains. Physical violence is I think the only hope for him, and if all else fails then he should be put to death.,

**fixer** · 09-01-2019, 09:45 AM

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

**DocAElstein** · 09-01-2019, 01:30 PM

Thanks for letting us know

Thanks for posting (your)** solution
Alan

P.S: You can use code tags here, just as at other Forums

Add code tags before posting :
_1 Select the code
SelectCode.JPG : https://imgur.com/shzUcmN
SelectCode.JPG

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

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

_ 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
FinalPostWith BB Code Code Tags.JPG

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

**fixer** · 09-01-2019, 09:48 PM

Sorry Doc Sir this mistake will not be repeated Again

**pike** · 09-02-2019, 10:59 AM

https://www.ozgrid.com/forum/forum/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/

**fixer** · 05-24-2020, 03:55 PM

Macro will be placed in a seperate file macro.xlsm
If column H of 1.xls is greater than column D of 1.xls then calculate 1% of column D of 1.xls & add it to column D of 1.xls and compare column D of 1.xls with column I of 1.xls & if column D of 1.xls is greater than column I of 1.xls then see column I and match column I of of 1.xls with column B of Alert..csv & if it matches then delete that entire row of Alert..csv
If column H of 1.xls is lower than column D of 1.xls then calculate 1% of column D of 1.xls & subtract it to column D of 1.xls and compare column D of 1.xls with column I of 1.xls & if column D of 1.xls is lower than column I then see column I of 1.xls and match column I of of 1.xls with column B of Alert..csv & if it matches then delete that entire row of Alert..csv
all files are located in different place
sheet name can be anything in all files

Download file link

https://drive.google.com/open?id=1Md...ZGBD_OqBn5ZNzf
https://drive.google.com/open?id=1Mc...jHzz1ll3jecEP1

**DocAElstein** · 05-25-2020, 03:16 PM

Hi

Originally Posted by fixer

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:

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

**DocAElstein** · 05-25-2020, 03:16 PM

Hi

Originally Posted by fixer

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:

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

**fixer** · 05-25-2020, 03:43 PM

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

**fixer** · 05-25-2020, 03:43 PM

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

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

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

Thread Tools

Search Thread

Display

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

Conditionally delete entire row with calculation within files

Similar Threads

Delete One Row From A 2D Variant Array

Delete Filtered Result, Sort From Specific Row And Delete Entire Row Having No Pairs

Delete Entire row if cell value does not have either two of specific word

Delte a specific column and does not delete the top row

Need to Delete Multiple Row based on Conditions

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions