Menu

Excel - Removing different rows in 2 sheets [Solved/Closed]

Report
-
venkat1926
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
 -
Hello,

I have two worksheets containing a different number of rows.


I want to delete all rows in sheet 1 that have a value in column A that can not be found in column A of sheet 2.


Can anybody explain me if this is possible in excel and if possible; how can i do this?

All help greaaaatly appreciated!!



See more

4 replies

Answer 1 / 4
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
 789
what is your expertise in excel. Are you familiar with macros.
any how I am giving you manual solution. if you want macro post back

suppose your sheet 1 is like this

hdnag1 hdng2
f 4
d 3
a 1
s 2
g 5


some of these of column A in this sheet are there in sheet2 and not all

in sheet 1 C1 type "expt" without quotes
in c2 type or copy this formula
=IF(ISNA(VLOOKUP(A2,Sheet2!$A$2:$A$100,1,0)),"",VLOOKUP(A2,Sheet1!$A$2:$A$100,1,0))
now sort sheet1 according to column "expt" DESCENDING

those rows where the cells in column C are blank will come in the bottom. you can delete those rows.
subsequently you can also delete the column "expt" in sheet1

try this in this experimental data. if you want macro whose operation will be easier post back
Thank you
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 5390 users have said thank you to us this month

Answer 2 / 4

Thank you very very much.

It took me some time to understand the formula.
(still I don't understand exactly what is happening, but it's working now)

I had to change the formula to my Dutch version of Excel (meaning that comma's had to be replaced with ;)

Resulting in:
=IF(ISNA(VLOOKUP(A4;Sheet1!$A$2:$A$80000;1;0));"";VLOOKUP(A4;Sheet2!$A$2:$A$80000;1;0))

(this may probably help people with a Dutch or similar version of Excel)

Once again, thank you very very much
Answer 3 / 4

If possible I am also curious about the MACRO,

as I am trying to work more with MACRO's in the future
Answer 4 / 4
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
 789
see help under VLOOKUP
Ask a question