Solved

Filtered Rows in Excel 2010 cannot copy formula and then copy records

Posted on 2014-03-24
2
396 Views
Last Modified: 2014-03-25
Excel 2010 64 bit

I have a spreadsheet that has over 32,000 rows.

I have many rows I need to modify.

So I filtered the first 9500 since the filter has a 10,000 record limit.

I have this

A   B                                        C         D       E                         F
1   98 This is a file name       1          1     M;\path       =right(b1, len(b1)-3)
2   50 This is a file name       1           2    M:\path        =right(b1, len(b1)-3)
3   66 This is another one      2          1    M:\path          =right(b1, len(b1)-3)


F is so I can strip off the numbers in the rows that contain the number.

When I filter column E which contains the filename and path to that file I get the list of all the files in the first 9,500 records

Problem is when I copy down column F formula to all the rows in the filter it places bad information in the rows. looks like from the wrong cells.

I copied all the filtered rows and placed them into a new page ran the formula on all the rows looked good

But when I copied them into the original spreadsheet over top of the filtered rows they all get bad data again.

How can I modify all these records so I do not have to scroll down thru 32000 rows.

Any help would be appreciated.
0
Comment
Question by:Thomas Grassi
2 Comments
 
LVL 39

Accepted Solution

by:
nutsch earned 500 total points
ID: 39952422
Copying over filtered rows will paste regardless of filter. Unfilter, put the formula throughout, copy / paste values, then filter again. You'll have the formula in the right spot.

Thomas
0
 
LVL 23

Author Closing Comment

by:Thomas Grassi
ID: 39952969
Thanks You gave me an idea on what to do
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now