paste over a filtered range

I have a spreadsheet with 40000 records, I have filtered down to 1000 records
that have one coloumn of incorrect information that needs replacing, how do I
paste the new data over the old?? What keeps happening is the new data
pastes over the first 1000 records, not only the filtered recorsds

freddie

When you have your filter in place and the 1000 records are showing, hit
F5>Special>Visible cell only and OK.

Now paste your replacement data.


Gord Dibben Excel MVP

On Tue, 26 Apr 2005 15:36:02 -0700, "freddie2711"
<[email protected]> wrote:

>I have a spreadsheet with 40000 records, I have filtered down to 1000 records
>that have one coloumn of incorrect information that needs replacing, how do I
>paste the new data over the old?? What keeps happening is the new data
>pastes over the first 1000 records, not only the filtered recorsds

That caused a problem when I did this (xl2003).

I put some test data in A1:I25
I filtered to see only row 3 and row 7.

Then I put some more test data in A30:I31 and copied it.

I selected Rows 4:7 and did the edit|goto special|visible cells only.

I pasted into that selection.

When I showed all my data, I saw my paste had affected both rows 3 and 4 and
rows 7 and 8.



Gord Dibben wrote:
>
> freddie
>
> When you have your filter in place and the 1000 records are showing, hit
> F5>Special>Visible cell only and OK.
>
> Now paste your replacement data.
>
> Gord Dibben Excel MVP
>
> On Tue, 26 Apr 2005 15:36:02 -0700, "freddie2711"
> <[email protected]> wrote:
>
> >I have a spreadsheet with 40000 records, I have filtered down to 1000 records
> >that have one coloumn of incorrect information that needs replacing, how do I
> >paste the new data over the old?? What keeps happening is the new data
> >pastes over the first 1000 records, not only the filtered recorsds

--

Dave Peterson

Can you sort that range to put all the data together, then paste into that
contiguous area?

I like to put 1, 2, 3, ... down a helper column.

Then I can sort by whatever I want. Do what I need to do and then sort by that
helper column to put things back in the original order.

freddie2711 wrote:
>
> I have a spreadsheet with 40000 records, I have filtered down to 1000 records
> that have one coloumn of incorrect information that needs replacing, how do I
> paste the new data over the old?? What keeps happening is the new data
> pastes over the first 1000 records, not only the filtered recorsds

--

Dave Peterson

When you paste, it will paste into a contiguous range, not the visible
cells in the filtered range.

Perhaps you could mark the visible rows, by putting an X in an adjacent
column. Then, sort by the marked column, and paste into the grouped cells.

freddie2711 wrote:
> I have a spreadsheet with 40000 records, I have filtered down to 1000 records
> that have one coloumn of incorrect information that needs replacing, how do I
> paste the new data over the old?? What keeps happening is the new data
> pastes over the first 1000 records, not only the filtered recorsds


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Thanks for the quick response, unfortunatley it didn't work.... I get the
error message "The information cannot be pasted because the Copy area and the
Paste area are not the same size and shape..."

Any suggestions??

"Gord Dibben" wrote:

> freddie
>
> When you have your filter in place and the 1000 records are showing, hit
> F5>Special>Visible cell only and OK.
>
> Now paste your replacement data.
>
>
> Gord Dibben Excel MVP
>
> On Tue, 26 Apr 2005 15:36:02 -0700, "freddie2711"
> <[email protected]> wrote:
>
> >I have a spreadsheet with 40000 records, I have filtered down to 1000 records
> >that have one coloumn of incorrect information that needs replacing, how do I
> >paste the new data over the old?? What keeps happening is the new data
> >pastes over the first 1000 records, not only the filtered recorsds
>
>

If (and only if) by any chance the data you are replacing is the same value
for *all* your filtered rows, then do Edit / Go To Special / Visible Cells
only, enter the replacement data in the first selected cell and then use
CTRL+ENTER to enter the data, at which point it will be propogated to all
the visible cell selected.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"freddie2711" <[email protected]> wrote in message
news:[email protected]...
> I have a spreadsheet with 40000 records, I have filtered down to 1000
records
> that have one coloumn of incorrect information that needs replacing, how
do I
> paste the new data over the old?? What keeps happening is the new data
> pastes over the first 1000 records, not only the filtered recorsds

Dave and Debra are correct.

My method doesn't work on non-contiguous cells.

Back to tester's school<g>


Gord

On Tue, 26 Apr 2005 16:29:25 -0700, Gord Dibben <gorddibbATshawDOTca> wrote:

>freddie
>
>When you have your filter in place and the 1000 records are showing, hit
>F5>Special>Visible cell only and OK.
>
>Now paste your replacement data.
>
>
>Gord Dibben Excel MVP
>
>On Tue, 26 Apr 2005 15:36:02 -0700, "freddie2711"
><[email protected]> wrote:
>
>>I have a spreadsheet with 40000 records, I have filtered down to 1000 records
>>that have one coloumn of incorrect information that needs replacing, how do I
>>paste the new data over the old?? What keeps happening is the new data
>>pastes over the first 1000 records, not only the filtered recorsds

Hi Freddie

not sure how to achive this, but are you pasting the SAME VALUE over all the
1000 records (ie updating "Dept" to "Department"), if so you can use the
visible cells only option with control & enter

.... try on a copy of your workbook

filter the data and select the column you want to replace, choose edit /
goto / special - visible cells, ok
now without clicking anywhere type the new value and press CONTROL & ENTER
--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"freddie2711" <[email protected]> wrote in message
news:[email protected]...
>I have a spreadsheet with 40000 records, I have filtered down to 1000
>records
> that have one coloumn of incorrect information that needs replacing, how
> do I
> paste the new data over the old?? What keeps happening is the new data
> pastes over the first 1000 records, not only the filtered recorsds