I have 25000 data in Sheet 1 which is application of an exam like as follows:
A B C D
E F
Reg. No. Roll Name Address
City Pin
002848 5623 RAKESH AGARWAL DE-105/2 NARAYAN TALA, RANCHI 834005
002284 5257 ANJU KUMAR SARVESHWARI NIKETAN, PATNA 800001
005090 5721 SULATHA KUMARI 82/A BONDEL ROAD GHATAL 110020
001506 5120 RAJAT PARASRAM 18/B BIPRADAS STREET S. NAGAR 743127
001501 5019 SIDHARTHA KHER 25B, J. BASAK ROAD KULTI
831012
005092 5818 SUSANTA SAHA BH-6, SECTOR II C. NAGAR 712136
003346 5517 GOBINDI DEVI 730, BLOCK-A JALEM 744101
Continued .....
I have 2500 data in Sheet 2 which is exam result (fail) like as follows:
A B
Reg. No. Roll
002561 5257
002624 5481
000045 5019
000926 5116
000954 5062
001618 5263
002757 5454
Continued .....
after that I insert two column in between B & C column of sheet 1 to put the
whole number of sheet 2 in the insert column as above in sheet 1. Now I want
to match the number of sheet 1 with sheet 2. If sheet 1 number is match with
sheet 2 then all corospondence data (like Address, City, Pin, etc.) of the
match number in sheet 1 will be delete.
is there any formula not macro in excel. Please help me.

Formulas cannot clear cells, but for your intents you could try a formula
col, apply an autofilter on it, then manually select/clear the filtered rows.

Try this play on a spare copy of your book

It presumes that the Reg. Nos in col A in both Sheet1 and Sheet2 alone
suffices to establish the uniqueness of the data

Insert a new col C in Sheet1
Put in C2:
=IF(A2="","",ISNUMBER(MATCH(A2,Sheet2!A:A,0)))
Copy C2 down to the last row of data in col A

Apply autofilter on col C, filter out: TRUE

The filtered rows will be the result lines that you seek, ie lines where Reg
Nos in col A is found in Sheet2's col A. You could then easily select the
filtered data in the cols to the right, and clear these with the Delete key.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Montu" wrote:
I have 25000 data in Sheet 1 which is application of an exam like as follows:
A B C D
E F
Reg. No. Roll Name Address
City Pin
002848 5623 RAKESH AGARWAL DE-105/2 NARAYAN TALA, RANCHI 834005
002284 5257 ANJU KUMAR SARVESHWARI NIKETAN, PATNA 800001
005090 5721 SULATHA KUMARI 82/A BONDEL ROAD GHATAL 110020
001506 5120 RAJAT PARASRAM 18/B BIPRADAS STREET S. NAGAR 743127
001501 5019 SIDHARTHA KHER 25B, J. BASAK ROAD KULTI
831012
005092 5818 SUSANTA SAHA BH-6, SECTOR II C. NAGAR 712136
003346 5517 GOBINDI DEVI 730, BLOCK-A JALEM 744101
Continued .....
I have 2500 data in Sheet 2 which is exam result (fail) like as follows:
A B
Reg. No. Roll
002561 5257
002624 5481
000045 5019
000926 5116
000954 5062
001618 5263
002757 5454
Continued .....
after that I insert two column in between B & C column of sheet 1 to put the
whole number of sheet 2 in the insert column as above in sheet 1. Now I want
to match the number of sheet 1 with sheet 2. If sheet 1 number is match with
sheet 2 then all corospondence data (like Address, City, Pin, etc.) of the
match number in sheet 1 will be delete.
is there any formula not macro in excel. Please help me.