excel delete cells

[bojan0810]

Hi all!

I have a problem.

I have columns with dates on it, its around 3000 dates in one column, and on other column I have dates too. How can I delete dates in some cell if that date isnt in other column.

so for example.

Column A has cell with 3.4.2005. other column for example B doesnt have that date on it. How can I remove 3.4.2005. (leave it blank)

And if column A has 4.4.2005. and column B has 4.4.2005. too, so that date stays.

Thanks in advance

 

[Claus Busch]

Hi Bojan,

Am Sat, 9 Nov 2013 11:30:02 -0800 (PST) schrieb (e-mail address removed):

Column A has cell with 3.4.2005. other column for example B doesnt have that date on it. How can I remove 3.4.2005. (leave it blank)

And if column A has 4.4.2005. and column B has 4.4.2005. too, so that date stays.

you can filter with advanced filter.
Both columns need the same Header

Step1: Data range = A:A, criteria range = B:B
filter to other range = C1

Step2: Data range = B:B, criteria range = A:A
filter to other range = D1

Now you can delete columns A and B


Regards
Claus B.

 

[bojan0810]

Dana subota, 9. studenoga 2013. 21:04:13 UTC+1, korisnik Claus Busch napisao je:

Hi Bojan,



Am Sat, 9 Nov 2013 11:30:02 -0800 (PST) schrieb (e-mail address removed):






you can filter with advanced filter.

Both columns need the same Header



Step1: Data range = A:A, criteria range = B:B

filter to other range = C1



Step2: Data range = B:B, criteria range = A:A

filter to other range = D1



Now you can delete columns A and B





Regards

Claus B.

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

I am not sure if I understand you right...

Somewhere in column A has date 5.5.(example)and on column B that date isnt on list. So I need to remove that date from column A and leave blank cell on that date.

 

[Claus Busch]

Hi,

Am Sat, 9 Nov 2013 12:41:54 -0800 (PST) schrieb (e-mail address removed):

Somewhere in column A has date 5.5.(example)and on column B that date isnt on list. So I need to remove that date from column A and leave blank cell on that date.

did you test the suggestion?

Another way:
In C1 try:
=IF(COUNTIF(B:B,A1)>0,A1,"")
in D1 try:
=IF(COUNTIF(A:A,B1)>0,B1,"")
and copy both formulas down to the end of your data.
Then copy column C and D and paste it back as values. Then you can
delete column A and B.


Regards
Claus B.

 

[bojan0810]

Dana subota, 9. studenoga 2013. 21:51:22 UTC+1, korisnik Claus Busch napisao je:

Hi,



Am Sat, 9 Nov 2013 12:41:54 -0800 (PST) schrieb (e-mail address removed):






did you test the suggestion?



Another way:

In C1 try:

=IF(COUNTIF(B:B,A1)>0,A1,"")

in D1 try:

=IF(COUNTIF(A:A,B1)>0,B1,"")

and copy both formulas down to the end of your data.

Then copy column C and D and paste it back as values. Then you can

delete column A and B.





Regards

Claus B.

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

Sorry for not answering you before I was working on it

Actually both answers from you helped, first answer I didn't do right at first thats why I said that before.

Thanks for your help