Export Specific Data from existing excel spreadsheet into new Excel Sheet

[Annie1065]

Hello,

I have a long list of test results that have been exported into excel from another program.

This is an extremely long list as the readings are every second; is there any way that I could create another list from this one which picks out the readings every 20 seconds.

Hope this makes sense.

Thanks

Annie

[Richard Buttrey]

Hi,

Yes. The general approach would be to use a helper column to identify every 20th item, e.g. =MOD(ROW(),20). Then just filter on the helper column for the value that represents the particular 20th second you want. For instance if your data starts on the hour in row 1 then modulus 20 would be 1, if you start on row 5 it would be 5.

After filtering the data then just copy it to a new sheet, or use Data Filter Advanced to do it for you.

Regards

[Annie1065]

Hi Richard,

It took a little bit of thinking about i.e. the formula was to be used for conditional formatting but once I realised that it worked a treat.

Thank you; much appreciated.

Annie

[Annie1065]

Morning all,

I have been asked how to recreate the same result using Excel 2003; any advice would be gratefully received

Thanks

Annie

[Richard Buttrey]

Hi,

The general approach for XL 2003 should be the same. The only difference is that only three conditional formats are permitted but I don't think that matters with your case.

[Annie1065]

Hi Richard,

I have tried it but you cannot filter by colour; and I have tried to used the go to special function but cannot get it to work. Is there another way?

Thanks

Annie