Sorting Every 3rd Cell into a new column

[sbmike]

have a very long list of data of temperatures recorded at one minute intervals. There is a date and time in one column and the temperature in the other. The columns are approximately 25K rows long.

I have discovered the data was actually collected every 3 minutes, so one third of my data is duplicated.

So how do I create a new set of columns that only includes every third row of data. If there is a way to do this not using macros it would be prefered. But I can manage if a macro is the only way.

I am a Mac user

Thanks

 

[Aladin Akyurek]

have a very long list of data of temperatures recorded at one minute intervals. There is a date and time in one column and the temperature in the other. The columns are approximately 25K rows long.

I have discovered the data was actually collected every 3 minutes, so one third of my data is duplicated.

So how do I create a new set of columns that only includes every third row of data. If there is a way to do this not using macros it would be prefered. But I can manage if a macro is the only way.

I am a Mac user

Thanks

C2:

=INDEX(A$2:A$1000,3*(ROWS(C$2:C2)-1)+1)

would copy every 3rd value from the range in column A to column C.

 

[shemayisroel]

You could also use the advanced filter option (unique records) option to extract a unique list by date/time as it will remove all duplicated date/times.

 

[sbmike]

Thanks for the answer, however deleting duplicate data will not work. Each piece of data has a date/time value in one column and a temperature value in the second column. What I need to do is extract the information from every third row and put this information in another column.

 

[Aladin Akyurek]

Thanks for the answer, however deleting duplicate data will not work. Each piece of data has a date/time value in one column and a temperature value in the second column. What I need to do is extract the information from every third row and put this information in another column.

See my reply in case you missed it...

 

[shemayisroel]

Thanks for the answer, however deleting duplicate data will not work. Each piece of data has a date/time value in one column and a temperature value in the second column. What I need to do is extract the information from every third row and put this information in another column.

Ok then Aladin's solution is definantely the best one using a formula solution

 

[sbmike]

Thanks for your help.

I see now I can create a new column and assign the rows repeating values of 1 - 0 - 0. I will then sort the data by the new column and the date/time column.

Thanks