Reducing data in a Worksheet

Hello All

I have a very large number of data points, approximately 36000 pairs
that was genrated from a test.  I would like to reduce the number of
datasets so it is not so large of a file to manipulate by skipping a
every other row (or maybe 2 or 3 rows).  Does anyone now of a formula
or routine to create a new data set in this way?

Thanks.


-- 
cspellman
------------------------------------------------------------------------
cspellman's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=37620
View this thread: http://www.excelforum.com/showthread.php?threadid=572411

0
8/16/2006 9:05:23 PM
excel 39879 articles. 2 followers. Follow

3 Replies
245 Views

Similar Articles

[PageSpeed] 55

If you put this formula in a blank column next to your data:

=MOD(ROW(),2)

and copy it down, it will give you 1 and 0 alternately. This formula:

=MOD(ROW(),3)

will give 1, 2 and 0 down the column.

You could then apply autofilter to the column and select 0. Highlight
all the rows that are displayed, then Edit | Delete Row. Remove the
filter and your data will have been "squashed". Note that you will
still have the alternating sequence, as the formula will adjust itself
automatically, so you could just repeat the procedure until you have
removed enough data.

Hope this helps.

Pete

cspellman wrote:
> Hello All
>
> I have a very large number of data points, approximately 36000 pairs
> that was genrated from a test.  I would like to reduce the number of
> datasets so it is not so large of a file to manipulate by skipping a
> every other row (or maybe 2 or 3 rows).  Does anyone now of a formula
> or routine to create a new data set in this way?
>
> Thanks.
>
>
> --
> cspellman
> ------------------------------------------------------------------------
> cspellman's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=37620
> View this thread: http://www.excelforum.com/showthread.php?threadid=572411

0
pashurst (2576)
8/16/2006 9:50:42 PM
In D1 enter this and drag/copy down until you return zeros.

=INDIRECT("A"&ROW(C1)*5)  will return every 5th data point in column A.


Gord Dibben  MS Excel MVP

On Wed, 16 Aug 2006 17:05:23 -0400, cspellman
<cspellman.2cniit_1155762607.7334@excelforum-nospam.com> wrote:

>
>Hello All
>
>I have a very large number of data points, approximately 36000 pairs
>that was genrated from a test.  I would like to reduce the number of
>datasets so it is not so large of a file to manipulate by skipping a
>every other row (or maybe 2 or 3 rows).  Does anyone now of a formula
>or routine to create a new data set in this way?
>
>Thanks.

0
Gord
8/16/2006 10:27:13 PM
Thanks Gord, That did the tric

--
cspellma
-----------------------------------------------------------------------
cspellman's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3762
View this thread: http://www.excelforum.com/showthread.php?threadid=57241

0
8/17/2006 3:31:12 PM
Reply:

Similar Artilces:

Advice required
I need to create some graphs from data that is derived from a long series of separate calculations on my source data. For aesthetic reasons, I would like to hide all the "interim" calculations on the spreadsheet, but I've noticed that as soon as I hide the interim data (i.e set the row height to zero), the detail on the graphs disappears. I presume that this is a design feature feature of Excel (I supoose I can just about accept the rationale for it), but does anyone know whether there's a way around this? Many thanks. CRS. Use white fonts -- Regards, Peo Sjobl...

worksheet skipped in page numbering
I have an Excel document with 10 worksheets (tabs). I went to set up footers and my options from the drop down were page 1 for first tab, page 2 for second tab, etc. until I got to the 5th tab and it went back to page 1. Tab 6 gives me dropdown options for page 5 - in short, it is like it skipped a worksheet and will only number that sheet as an indpendent one, and calls it page 1. Any ideas as to why it doesn't recognize a worksheet? -- Zach ...

AHHHH-Get Data from Multiple Excel workbooks
Dear All, I am going down real bad. 1 - I have many excel workbooks. Let's say around 30. 2 - Each workbook contains only 1 sheet or may in the future contain more sheets. 3 - Each sheet contains list of data with dynamic named ranges which expand as new data is entered. 4 - How do I create a query in Excel (MS query) from the Data > Import External Data > New Data Base Query command. 5 - I tried everything. Nothing works. I can not add a second excel workbook while I want to add as many workbboks as I want say 30. The Owner & Table > Add Table > Workbook options in MS...

Outlook 2000 to 2003 transfer user data
Hey there, I need to transfer the user files from a windows 98SE with Outlook 2000 to a newer XP Pro based P.C with Outlook 2003. What do I need to do? -- "You''re only as good as your last build". Answered here daily. The file you need is your Personal Folders file (*.pst) It's where all the mail, calendar, contacts etc are stored. Take a look at these pages for info on Outlook data backup or transfer: http://www.slipstick.com/config/backup.htm http://www.howto-outlook.com/Howto/backupandrestore.htm http://office.microsoft.com/en-us/assistance/HA010...

How can I check whether the data is in the table or not with VBA?
Hi, Hope someone have quick answer to my question. I have a table call "IssueTable". I want to code to find out whether the user inputed informaiton is in the IssueTable before user enter it in the table. I don't want to duplicate the issue in the issuetable. Therefore, I want to check it before I enter it as new item. In the Usertable, there is the Authrization# field and if user enter part of Authrization#, I want the code to check if the User input is in the Issue Table. Thanks Use DLookup(). For examples, see: Getting a value from a table: DL...

Show all data -- FMR
I have 4 tables, each shares Date and Service, but after that they vary from budget details, to capacity details, all the way to actual details. Since the budget details go out into the future and the actuals do not, I want to produce a worksheet showing data from all tables from a date and out to a specified date. I know how to do the date request but the only data I get from my query is what is populated in all tables. (linked on Service and Date) Any assistance would be appreciated: EX: Date Forcast Budget Actual 1/1/07 ...

How do you get changes in the HQ Db picked up by Worksheets
Many times we need to make large volume changes to products in our RMS databases (over 26000 SKUs). We use MS Access queries to make those changes. Right now those changes need to be made in HQ and each of the SO databases. I would like to be able to make them in the HQ Db only and have the RMS work sheet process propigate the changes to all the SO Dbs. I am assuming that the DBTimestamp is what is being used to determine what records need to be update or not. How do I update the timestamp using MS Access to make this happen? Thanks, TomT If you can represent the ...

Restructuring text data in excel? Pivot?
Hi! I have a large sum och data in the order below: Choice Course 1 Course 2 Course 3 Course 4 1 Name 1 Name 3 Name 2 Name 1 2 Name 3 Name 4 Name 4 Name 3 3 Name 5 I would like to structure it differently to the format below: Choice 1 2 3 Name 1 Course 1 Course 4 Name 2 Course 3 Name 3 Course 1 Course 2 Course 4 Name 4 Course 2 Course 3 Name 5 Course 1 Is this possible? I have been trying to use pivottables without any luck. Cheers and thanks, Pete Pivottables work with numbers (sum of, max of, average of). They won't work with text. And I don'...

need excel worksheet help, running total based on date
I am trying to add a series of numbers in different worksheets(same spreadsheet) based upon the date. I don't want the future numbers in the total until the date they are associated with is passed. i.e. in A1 of each worksheet is 200. Each worksheet is titled by date (Mar 5, Mar 19, etc) I want the running total to reflect the amount to the current date. If it's March 18th, the totals would be only thru the worksheet of Mar 5. Any help is appreciated. My solution needs 2 cells, one of which may already be in use: You need a cell on each of the dated sheets that...

repeating a formula throughout a worksheet
Hi I havent used excell for some years and have set up a basic worksheet, but am wanting to repeat a formula each third row which is row 1 balance plus row 2 minus row 3 equals balance as displayed in row 4 and then repeat this thoughtou the worksheet for several columns ( if that makes sense- im sure it can be done not sure how thou and have looked through help topics everywhere so if any one can help in basic language it would be great thanks. Jtnru If its single row then click your cell that contains the formula once then hold shift and click the further most cell that you want the f...

Aligning data with varying decimal places
I have a column of cells in which the data include various numbers of decimal places. Is there a way to align the data vertically so that the decimals line up, without adding trailing zeroes? one way: Format/Cells/Number/Custom 0.??? In article <DD9B0C05-0A1D-4A5A-99F4-BB8030D9F7F2@microsoft.com>, edpaul <edpaul@discussions.microsoft.com> wrote: > I have a column of cells in which the data include various numbers of decimal > places. Is there a way to align the data vertically so that the decimals > line up, without adding trailing zeroes? ...

Listing a selection of data from one worksheet on another
At the moment I have a worksheet with a list of client in one column and then in another column that uses an IF formula to display either the number of days to the deadline or text which says No Deadline. What I want to do is enter some kind of formula so that a list of clients with deadlines between 1 and 7 days appears on another worksheet automatically with the number of days to the deadline next to them. To make it even harder the list is currently in name order but on the new worksheet I would like it to automatically appear in days til deadline order with lowest number of days a...

2 sets of data I need one line
I have two sets of data, height and distance and I want both to be on the same line and not two seperate lines. I would appreciate any help Thanks Hi, You need to create a xy scatter chart. Assuming you have already tried selecting both ranges of data and creating a chart try this instead. Select the y values and create a xy scatter chart. Then right click chart and use the Source data dialog to specify the x values. Cheers Andy Tim wrote: > I have two sets of data, height and distance and I want both to be on the > same line and not two seperate lines. I would appreciate any he...

How backup data in Outlook2003w/BCM?
I'm using Outlook 2003w/BCM, and wondered how to backup everything before I uninstall/reinstall Outlook, so I get back to everything looking exactly the way it was before the re-install. (What cause this is...I have an underlying WinXP problem affecting the whole PC, so I have to format/reinstallWinXP, and of course that causes me to loose and have to re-install Outlook...but right now Outlook works ok, so the data's still in tact) In Outlook, there's a File > Business Database > Backup... but it says I also need to backup "linked items." So, what all needs to be...

Create Data Entry "Wizard"
Hi everyone, I'm trying to create a data entry "wizard" for a database. Basic premise - a company has clients that they need to be able to track various aspects of information about. All information might not be available at the time of entry. I'd like to have an AddClient form (data entry mode) with buttons for Add Address (ClientAddress form), Add Phones (ClientPhones form), and Add Emails (ClientEmails form). How can I pass the ClientID value created on the AddClient form to the ClientAddress (and other) forms? Thanks in Advance! On Fri, 14 Mar 2008 09:31:03 -...

Retrieving data from multiple records
How do I put data from multiple records in one report on one line. I have parents in one table and children in another. Each child has its own record. How can I include a sentence like this in a report when the children's names come from various records: "Thank you for registering Brianna, Sean, and Emma for our art workshop." Also, there are other children in the family who were not registered. Using Access 2003. Thanks for your help. There is a generic function that will concatenate the child records at http://www.rogersaccesslibrary.com/forum/forum_posts.asp?T...

Duplicating PivotChart / Changing PivotChart data range
I have a worksheet with a pivottable and an associated pivotchart, all nicely formatted. I want to make a duplicate of this so I can configure the data selection differently, but still retain the formatting. In Excel 2003, I just duplicated the worksheet and the new pivotchart would be associated with the new pivottable. In Excel 2007, I find the new pivotchart is *still* tied back to the original pivottable. Is there a way around this? If I select the PivotChart Tools, I see 'Select Data', but the section for "Chart data range" is grayed out and will not allow edits. I t...

Data Migration
I'm trying to migrate Sales History information into CRM. Invoices and Invoice Lines are migrating and calculating the Extended value correctly. I'm having a problem with Credit Notes. Credit Note Lines (where Price per unit < 0) are not migrating. I'm getting a "CrmIncidentUnknownError" in the migration log for these lines. The same error occurs for lines where Price per Unit = 0. I've modified all the relevant value fields on the forms to allow minimum values of -100,000,000. I'm using Existing products and over-riding the Price each time. I previously ...

Excel should allow chart axes to be set from sheet data.
I often chart account balances where I have x axis of dates and y axis of monetary amounts. It would be helpful if there was a facility to allow the x axis minimum and maximum to be set to some value as calculated from the data itself (or even calculated using a formula). I like to keep a "rolling" picture of the last three months (so, today()-90 to today()) and the next three months (today() to today() + 90) (on two separate charts). I have to manually update the x axis's minimum and maximum each day. you can set up formulas in cells to calculated your desired max/mins a...

help with schema for xml data from exchange server
I have xml data coming in as a stream from a web service running against exchange server.The attributes for the elements dtstart and dtend, b:dt="dateTime.tz" xmlns:b="urn:uuid:c2f41010-65b3-11d1-a29f-00aa00c14882/, are causing trouble. I tried to create schema using xmlspy and I get an error saying name "b:dt" cannot have a namespace prefix. I am new to xml. Can somebody please help with schema. <appointments> <appointment> <url>http://test/exchange/test1/Calendar/meeting1-7.EML</url> <subject>meeting1</subject> <dtstar...

selective import data from SO to HQ
Can i selectively import data(customer, cashier, etc.) from SO to HQ and export it out to other new stores? Thanks. - The customer data is brought to HQ automatically through Worksheet 401. So answer is NO you cannot be selective about this. - Cashiers are not global setting [unless you use RR HQ Misc that allows you to have global cashier and send messages from to store]. Afshin Alikhani - [afshin@retailrealm.co.uk] CEO - Retail Realm -- URL http://www.retailrealm.co.uk "JayK" wrote: > Can i selectively import data(customer, cashier, etc.) from SO to HQ and > expo...

Help needed with Data Validation.
I have a workbook that contains a Template sheet and several copies of it, N01 - N0X. I need to control what can be entered in certain cells in N01 - N0X, so need to use Data > Validation > List. I also want users to be able to enter their own free form text in some cells, so have to use named ranges, and include a blank cell within the range. I have a separate sheet 'Validation' so as to be able to only have to update this one sheet and have the changes reflected in the drop down options in the template and all copies. I have played around with this and am now co...

Timesheet NONbillable data
whenever i enter timesheet under administrative tasks Actualnonworkbillable is not reflecting in database?? Can i have link between Project Resource and Timesheet Resource is this possible in project server database??? ...

Whats the formula to look at data in precentages in fixed periods
how do I calculate a continous percentage return of two data points at fixed intervals? example : data set={12,50,03,59,22,....} , fixed interval=2periods. [(12-03)/12], [(50-59)/50], [(03-22)/03],....I'd like to do this in Excel 03 "donald" wrote: > how do I calculate a continous percentage return > of two data points at fixed intervals? > example : data set={12,50,03,59,22,....} , > fixed interval=2periods. > [(12-03)/12], [(50-59)/50], [(03-22)/03], Assuming data is in A2 down: 12,50,3,59,22,... In B2: =(A2-A4)/A2 B2 formatted as percentage, then copi...

How to automatically populate an Excel database w/data from many forms?
I have a data collection spreadsheet laid out like a form for multipl users; each user will do a save as to create hundreds of records o copies of this form. We are using the data collected to build an Exce database. Currently we are manually keying the data from the form into a single spreadsheet (the database). Is there a way to automat the transfer of the data from the forms into the database? Also, eac record will have a unique ID number. We are working in MS Excel 2002. Thank you -- ~~ Message posted from http://www.ExcelForum.com I think I'd tell the user to run a macro that...