Delete rows based on match criteria in two excel files or single Excel File

**DocAElstein** · 07-18-2020, 01:08 PM

From last post
Problem 2
You have not given me an After, and once again the explanation is bad. - you did not yet answer maniacb at excelforum.... https://www.excelforum.com/excel-pro...ml#post5365612

I will have to assume that this:
If column B of DEF.xlsx matches with Column I of ABC.xls then delete that entire row
Is supposed to mean this
If column B of DEF.xlsx matches with Column I of ABC.xls then delete that entire row of ABC.xls
In other words, something we have done many times already….
We consider the data in column I of ABC.xls from row 2. We try to find ( match ) that data to any row of data in column B of DEF.xlsx
If we do have a match then we delete the entire row containing the considered data from Column I of ABC.xls

I will do the first solution, Problem2a) slightly differently ,because I am getting bored answering almost the same question over and over again…
I am using vba arrays because for your values work that is the best approach in Excel ( https://excelfox.com/forum/showthrea...ll=1#post14628 )
I do not delete rows, so I do no backward looping
Instead I collect indices of the rows I want to have = rows which are not deleted. For you test data, the rows I want are 1 4 5 6 7 8 9 ( rows 2 and 3 ) are not wanted – you want to delete them )
Finally the macro replaces the original range with a new one containing just the rows you want

I also did a more conventional solution Problem2b like the ones you have seen a lot of in the last few days. It deletes the rows ( rows 2 and 3 )

Before
_____ Workbook: DEF PROBLEM 2.xlsx ( Using Excel 2007 32 bit )

_Row\^Col	A	B	C	D	E	F	G	H	I	J	K
1	NSE	22	6	>	12755	A					GTT
2	NSE	25	6	<	13448	A					GTT

Worksheet: DEF

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

_Row\^Col	A	B	C	D	E	F	G	H	I	J	K
1	Exchange	Symbol	Series/Expiry	Open	High	Low	Prev Close	LTP
2	NSE	ACC	EQ	1265	1282.7	1246.5	1275.3	1247	22	BUY	14104.65
3	NSE	ADANIENT	EQ	151.85	165.45	151.4	151.85	152.35	25	BUY	3235.03
4	NSE	ADANIPORTS	EQ	348	348	348	346.55	338.85	15083	BUY	3539.747
5	NSE	APOLLOHOSP	EQ	1359	1391	1339	1355.65	1346.8	157	BUY	2994.65
6	NSE	APOLLOPIPE	EQ	351.95	351.95	340	350.8	347.2	14361	BUY	4339.566
7	NSE	ASHOKLEY	EQ	52.1	52.35	51	52.7	51	212	BUY	7051.82
8	NSE	AUROPHARMA	EQ	789	805.45	775.35	796.95	782.4	275	BUY	3608.73
9	NSE	AXISBANK	EQ	426.5	435.55	408.7	427.45	413.5	5900	BUY	255575.45

Worksheet: 1-Sheet1 Problem 2

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

_Row\^Col	A	B	C	D	E	F	G	H	I	J	K
1	Exchange	Symbol	Series/Expiry	Open	High	Low	Prev Close	LTP
2	NSE	ADANIPORTS	EQ	348	348	348	346.55	338.85	15083	BUY	3539.747
3	NSE	APOLLOHOSP	EQ	1359	1391	1339	1355.65	1346.8	157	BUY	2994.65
4	NSE	APOLLOPIPE	EQ	351.95	351.95	340	350.8	347.2	14361	BUY	4339.566
5	NSE	ASHOKLEY	EQ	52.1	52.35	51	52.7	51	212	BUY	7051.82
6	NSE	AUROPHARMA	EQ	789	805.45	775.35	796.95	782.4	275	BUY	3608.73
7	NSE	AXISBANK	EQ	426.5	435.55	408.7	427.45	413.5	5900	BUY	255575.45
8
9