How we can copy data when there is filter

[dalipsinghbisht]

I want to know that how we can copy the data together when there is a
filter in worksheet.

I am attaching a JPG file for your considration.

there are three coulmn and three rows on which i am working. I gave a
filter to Y In coulmn D and then i want to paste the data of coulmn E
in coulmn C. that can replace all the data on the same place. i.e. i
want Ream,y in the place of Ream, shyam,y in place of shyam and btht,y
in place of btht. I want to copy all the data at the same time.

Note Please take a look on the Picture attached with this Thread.


Thanks in advance


+-------------------------------------------------------------------+
|Filename: Problem.jpg |
|Download: http://www.excelforum.com/attachment.php?postid=4481 |
+-------------------------------------------------------------------+

 

[broro183]

Hi

One solution for your problem is to use a macro for copying multipl
selections, no promises that it'll work but have a look at:
http://j-walk.com/ss/excel/tips/tip36.htm

Or without using a macro...
I'm making a couple of assumptions here:
* that the 3 rows in your example are just that, an example, & you wil
potentially have many more rows when using real data.
* that the "y" in column D is short for "yes"/true and therefore th
hidden rows will be "n" or false.

If my above assumptions are correct & you would prefer not to use
macro, I'd:

1) remove the filter (ie show all or [alt + d + f + s])
2)type this into column E:
=if(d2="y", concatenate(C2&", "&D2),C2)

3) copy down as far as needed
4) copy column E & paste special as values into column C.

hth
Rob Brockett
NZ
Always learning & the best way to learn is to experience..

 

[dalipsinghbisht]

HI,

Thanks for your help

Your macro is not working in Filter...


and the second as you tell to paste data in coulmn c As value then i
will over write the data in coulmn C..


Please tell how it can be solve..

Thank

 

[broro183]

Hi,
I thought you may have problems with the macro not working after I gav
it a quick test but had hoped that someone else would spot the thread
offer advice on how to overcome this.

re: "and the second as you tell to paste data in coulmn c As value the
it will over write the data in coulmn C.."

I thought that you wanted the data copied into column C?
The formula in column E,
=if(d2="y", concatenate(C2&", "&D2),*C2*)
means that the value of any cell in column E (before the copy action)
will be the *same as the value in column C *if there is no "y" i
column D.
Therefore when the copying to column C occurs, yes, the data will b
overwritten, but it should be the same data as was there before th
copy.

The only instance I can think of where this won't be true is if ther
are blanks in column C. In this case, try using this formula instead:
=if(d2="y", concatenate(C2&", "&D2),if(isblank(C2),"",C2))

(Ooopps, missed a closing bracket in above line - now fixed)

I hope this clarifies my line of thinking for you,
Rob Brockett
NZ
Always learning & the best way to learn is to experience..