Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data set filtation
I have a data set consisting of six columns and roughly 40,000 rows. I need
to be able to reduce the dataset in size by selecting every 15th row, and then exporting the values of the 6 columns, ie I need to reduce my 40,000 rows to less than 3,000 and be able to cut and paste the 3,000 rows by six columns into modelling software. This equal interval filtering seems like basic data manipulation but I can't figure it in excel. The 40,000 rows have unique non repeating time stamps (first column). Thanks! -- Thermophobe |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data set filtation
Put this formula in the first blank column and copy down
=MOD(ROW(),15) This will put a 0 in row 15,30,45,... You can filter on 0, copy visible cells and paste to another sheet... --------------------- Click 'Yes' if this helps. "BSU_Data" wrote: I have a data set consisting of six columns and roughly 40,000 rows. I need to be able to reduce the dataset in size by selecting every 15th row, and then exporting the values of the 6 columns, ie I need to reduce my 40,000 rows to less than 3,000 and be able to cut and paste the 3,000 rows by six columns into modelling software. This equal interval filtering seems like basic data manipulation but I can't figure it in excel. The 40,000 rows have unique non repeating time stamps (first column). Thanks! -- Thermophobe |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data set filtation
Assume your source data is in Sheet1, in A2:F2 down
In another sheet, you could place this in any startcell, say in B2: =OFFSET(Sheet1!A$2,ROWS($1:1)*15-15,) Copy B2 across by 6 cols to G2, fill down as far as required to exhaust the source data extract Success? Click the YES button below -- Max Singapore http://savefile.com/projects/236895 Downloads:25,000 Files:300 Subscribers:70 xdemechanik --- "BSU_Data" wrote: I have a data set consisting of six columns and roughly 40,000 rows. I need to be able to reduce the dataset in size by selecting every 15th row, and then exporting the values of the 6 columns, ie I need to reduce my 40,000 rows to less than 3,000 and be able to cut and paste the 3,000 rows by six columns into modelling software. This equal interval filtering seems like basic data manipulation but I can't figure it in excel. The 40,000 rows have unique non repeating time stamps (first column). Thanks! -- Thermophobe |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data Labels- POssible to show data value and data label together? | Charts and Charting in Excel | |||
Increase Your Business By Data Conversion, Data Format and Data EntryServices in India | Excel Worksheet Functions | |||
Save 20% On Data Conversion and Data Formats Services by Data EntryIndia | Excel Discussion (Misc queries) | |||
Data Entry Online, Data Format, Data Conversion and Data EntryServices through Data Entry Outsourcing | Excel Discussion (Misc queries) | |||
MULTIPLE DATA - How to insert new data into existing data.... | Excel Discussion (Misc queries) |