Filtered List - copy and paste

[GEF]

I have a filtered list I wish to copy to another column.
For example (much simplified):

A1 1
a2 2
a3 1
a4 2
a5 1

I want to filter out the 2 values leaving the 1 values
then copy the resulting filtered list into cells c1, c3
and c5. I have tried normal copy and paste - this copies
the results into the hidden cells.

I have selected visible cells using Edit Goto and tried
pasting this and tried pasting values ...to no avail.

Is this possible without vba? My current solution is to
loop through the A column values and copy them to C one at
at time - but it is not seem very efficient.

 

[Frank Kabel]

Hi
try the following:
- apply an autofilter and filter out the '2's
- select your range
- hit F5, choose Special and select 'Only visible cells
- now copy the data and paste it to the new location

 

[Peo Sjoblom]

I doubt that will work, the OP asked to paste the data in
C1, C3 and C5. It will paste in C1, C2 and C3 if C1 is selected
I'd suggest using a formula in C1 like

=IF(A1=2,"",A1)

then copy down and paste special as values in place
This assumes of course that the patter is every other
row is 1

 

[Frank Kabel]

Hi Peo
thanks for the correction. Misread the OP's answer

 

[Dave Peterson]

And another way to achieve the same effect as Peo's:

filter your range in column A.
Select column C (sames rows as your filtered range)
Write the following formula for the activecell's row:

=A2

Hit ctrl-enter to fill those visible cells in column C.

In earlier versions of excel (pre-xl97), you'll have to select the range in
column C and then

Edit|goto|special|visible cells only

Then enter the formula and hit ctrl-enter.

(xl97+ doesn't need that extra step.)

 

[Guest]

Thanks for all your efforts - I should be able to make
more progress with my problem now