Regarding selection of every 4th row from a huge excel datasheet...

**skonduru** · 09-04-2007, 06:53 PM

Hi,

I have a excel data sheet with 10000 rows and 25 columns. Now, i want to reduce it by selecting every 4th row, can any one please help me in regard of this issue.

I exported the excel sheet into microsoft query and i am ok with importing excel table into microsoft query software, but i am not sure of writing SQL query for selecting every 4th row...if it is possible that way, can anyone send me the query form please..

Thanks in advance, i appreciate that..

Bye
Sathish.K

**Special-K** · 09-05-2007, 07:29 AM

Does it have to be via SQL? I don't know a great deal about SQL.

You want some form of record counter and an INTeger function.

Each time you read a record add 1 to this record counter

Then calculate

If INT(variable / 4) * 4 = variable

So something like (remember I dont know SQL)

SELECT data FROM file
Temp1 = Temp1 +1
Temp2 = INT(Temp1 / 4) * 4
WHERE Temp1 = Temp2

When Temp2 = Temp1 the record count is a multiple of 4, therefore you are selecting every 4th record only

**Greg M** · 09-05-2007, 11:40 AM

Hi there,

Here's a cheap 'n' cheerful way of deleting every fourth line.

Enter the following code in a VBA module of your workbook:

Please Login or Register  to view this content.

where:
intFirstRow is the number of the first row which contains data
lngNoOfRows is the number of data rows
strColumn is a new column which will receive "DELETE" markers.

You can alter the above parameters to suit the layout of your data worksheet.

Run the "MarkRows" routine - this will enter "DELETE" on every fourth row - you can then check that the appropriate rows have been marked for deletion.

Now run the "DeleteRows" routine - this will delete every row which was marked for deletion by the "MarkRows" routine.

Hope this helps - please let me know how you get on.

Best regards,

Greg M

**skonduru** · 09-07-2007, 12:39 PM

Thank you very for your valuable code man...This helped me alot, I appreciate your help man...It saved me hell a lot of time...

Have a great day man...

**Greg M** · 09-07-2007, 12:43 PM

Hi again Skonduru,

Many thanks for the feedback. You're very welcome - delighted to have helped.

All the best,

Greg M

**skonduru** · 09-07-2007, 05:24 PM

i have one more question...

Do u have any idea about data reduction techniques in excel...i.e, as i said i have huge data, when i plot this data into excel sheet i am getting very zigzag curve(not smooth)...Actually i want to get the smooth curve, after getting smooth curve i want to filter out the data on the smooth curve...to be brief i want to eliminate the erratic data by using some data reduction r mining techniques what ever it is in excel r any other software...

Thank you..

Sathish.K

**Greg M** · 09-07-2007, 05:39 PM

Hi again,

I've never had occasion to get involved with such large quantities of data & so smoothing was never really an issue for me.

The best I can do at this stage is to suggest that you look at "Smooth the angles of line charts" and "Exponential Smoothing analysis tool and formula" in the Excel Help menu.

Sorry I can't offer anything more.

Best regards,

Greg M