Excel - Removing different rows in 2 sheets
Solved/Closed
Max
-
10 Oct 2011 à 03:34
venkat1926 Posts 1863 Registration date Sunday 14 June 2009 Status Contributor Last seen 7 August 2021 - 10 Oct 2011 à 07:44
venkat1926 Posts 1863 Registration date Sunday 14 June 2009 Status Contributor Last seen 7 August 2021 - 10 Oct 2011 à 07:44
Related:
- Excel - Removing different rows in 2 sheets
- 텐타클 락커 2 - Download - Adult games
- Saints row 2 cheats - Guide
- Five nights in anime 2 - Download - Adult games
- My cute roommate 2 - Download - Adult games
- Google sheets right to left - Guide
4 responses
venkat1926
Posts
1863
Registration date
Sunday 14 June 2009
Status
Contributor
Last seen
7 August 2021
811
10 Oct 2011 à 05:27
10 Oct 2011 à 05:27
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
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 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
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
If possible I am also curious about the MACRO,
as I am trying to work more with MACRO's in the future
as I am trying to work more with MACRO's in the future
venkat1926
Posts
1863
Registration date
Sunday 14 June 2009
Status
Contributor
Last seen
7 August 2021
811
10 Oct 2011 à 07:44
10 Oct 2011 à 07:44
see help under VLOOKUP
