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

[DocAElstein]

How about this….

What is wanted….
….. I have two workbooks, STEP1U.xlsb and 1.xls
There is a data range in the first worksheet of both workbooks, from rows 2. ( I have a header range in rows 1 )

Compare/ Consider column A in the first worksheet of STEP1U.xlsb with column B in the first worksheet of 1.xls. ( Ignore the first row – this is the header row )

If the value in column B in the first worksheet of 1.xls is not in column A in the first worksheet of STEP1U.xlsb , then the entire row , ( in 1.xls ), containing that value is to be deleted.
( STEP1U.xlsb is not changed )

Alternative short explanation: Delete all the rows in 1.xls whose value in column B is not to be found in column A in STEP1U.xlsb. ( STEP1U.xlsb remains unchanged )


Before:-
_____ Workbook: STEP1U.xlsb ( Using Excel 2007 32 bit )

Row\Col	A	B	C
1
2	ACC	100
3	DLF	150
4

Worksheet: Sheet1

_____ 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	1014	1030	955.5	998.45	957.4
3	NSE	ADANIPOWER	EQ	27.35	27.75	25.65	25.65	25.85
4	NSE	DLF	EQ	390	419.7	350.05	387.25	352.4
5	NSE	AMBUJACEM	EQ	145.1	155.8	142.55	145.85	145
6	NSE	APOLLOHOSP	EQ	1250	1265	1124.15	1158.65	1127.05
7

Worksheet: 1-Sheet1


After :-

_____ Workbook: STEP1U.xlsb ( Using Excel 2007 32 bit )

Row\Col	A	B	C
1
2	ACC	100
3	DLF	150
4

Worksheet: Sheet1

_____ 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	1014	1030	955.5	998.45	957.4
3	NSE	DLF	EQ	390	419.7	350.05	387.25	352.4
4
5

Worksheet: 1-Sheet1

---

Solution:-

See here:
http://www.excelfox.com/forum/showth...ll=1#post12896

---

See also some Important notes on looping backwards when deleting rows
http://www.excelfox.com/forum/showth...ll=1#post12902