How we can copy data when there is filter
 

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 |
+-------------------------------------------------------------------+

--
dalipsinghbisht
------------------------------------------------------------------------
dalipsinghbisht's Profile: http://www.excelforum.com/member.php...o&userid=32557
View this thread: http://www.excelforum.com/showthread...hreadid=524178

How we can copy data when there is filter
 

Hi

One solution for your problem is to use a macro for copying multiple
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 he
* that the 3 rows in your example are just that, an example, & you will
potentially have many more rows when using real data.
* that the "y" in column D is short for "yes"/true and therefore the
hidden rows will be "n" or false.

If my above assumptions are correct & you would prefer not to use a
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...


--
broro183
------------------------------------------------------------------------
broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068
View this thread: http://www.excelforum.com/showthread...hreadid=524178

How we can copy data when there is filter
 

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 it
will over write the data in coulmn C..


Please tell how it can be solve..

Thanks


--
dalipsinghbisht
------------------------------------------------------------------------
dalipsinghbisht's Profile: http://www.excelforum.com/member.php...o&userid=32557
View this thread: http://www.excelforum.com/showthread...hreadid=524178

How we can copy data when there is filter
 

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

"and the second as you tell to paste data in coulmn c As value then
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" in
column D.
Therefore when the copying to column C occurs, yes, the data will be
overwritten, but it should be the same data as was there before the
copy.

The only instance I can think of where this won't be true is if there
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...


--
broro183
------------------------------------------------------------------------
broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068
View this thread: http://www.excelforum.com/showthread...hreadid=524178