SOLVED

Sorting out data from a column

%3CLINGO-SUB%20id%3D%22lingo-sub-3248192%22%20slang%3D%22en-US%22%3ESorting%20out%20data%20from%20a%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3248192%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20very%20long%20column%20(28281%20rows)%20with%20dates%20and%20times%20(format%3A%20dd%2Fmm%2Fyyyy%20hh%3Bmm).%20There%20are%20no%20breaks%20between%20days%2C%20weeks%20or%20months%3A%20data%20is%20continuous.%20Next%20two%20columns%20are%20numbers%20(wind%20speeds%2C%20min.%20and%20max).%20Each%20day%20has%20hourly%20data%2C%20i.%20e.%20each%20day%20has%2024%20rows.%20Data%20is%20for%20three%20years%2C%20with%20some%20missing%20months%20mainly%20in%20the%20beginning.%20I%20would%20like%20to%3A%3C%2FP%3E%3CP%3Ea)%20Insert%20an%20empty%20line%20between%20each%20month%2C%20in%20order%20to%20calculate%20averages%20and%20medians%20per%20month%3B%3C%2FP%3E%3CP%3Eb)%20Delete%20lines%20that%20refer%20to%20hours%20between%2020%3B00%20and%2008%3B00%20each%20day.%20I%20only%20need%20daylight%20data.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWould%20someone%20help%20me%20finding%20a%20formula%20to%20do%20this%3F%20I%20have%20tried%20doing%20it%20manually%2C%20but%20it%20is%20very%20very%20long.%3CBR%20%2F%3E%3CBR%20%2F%3EThanks%20in%20advance%2C%3C%2FP%3E%3CP%3ELuis%20Serpa%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3248192%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3248558%22%20slang%3D%22en-US%22%3ERe%3A%20Sorting%20out%20data%20from%20a%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3248558%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%2C%20perfect!%20Many%20many%20thanks.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELuis%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3248540%22%20slang%3D%22en-US%22%3ERe%3A%20Sorting%20out%20data%20from%20a%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3248540%22%20slang%3D%22en-US%22%3EThank%20you%20so%20much%2C%20Riny.%20I'm%20going%20to%20try%20this%20method%2C%20it%20seems%20perfect.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3248462%22%20slang%3D%22en-US%22%3ERe%3A%20Sorting%20out%20data%20from%20a%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3248462%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F101468%22%20target%3D%22_blank%22%3E%40Luis%20Serpa%3C%2FA%3E%26nbsp%3BI%20suggest%20you%20look%20into%20Power%20Query%20as%20demonstrated%20in%20the%20attached%20example.%20In%20general%20it's%20not%20a%20good%20idea%20to%20insert%20empty%20rows%20in%20data%20sets%20to%20hold%20calculations%20relating%20to%20sub-sets%20of%20the%20data.%20In%20your%20case%20averages%20and%20medians%20per%20month.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3248339%22%20slang%3D%22en-US%22%3ERe%3A%20Sorting%20out%20data%20from%20a%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3248339%22%20slang%3D%22en-US%22%3EThanks%2C%20NikolinoDE.%20Will%20try%20it%20tomorrow.%3CBR%20%2F%3E%3CBR%20%2F%3ECheers%3CBR%20%2F%3E%3CBR%20%2F%3ELuis%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3248252%22%20slang%3D%22en-US%22%3ERe%3A%20Sorting%20out%20data%20from%20a%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3248252%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F101468%22%20target%3D%22_blank%22%3E%40Luis%20Serpa%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fes-es%2Foffice%2Fcrear-o-eliminar-una-lista-personalizada-para-ordenar-y-rellenar-datos-d1cf624f-2d2b-44fa-814b-ba213ec2fd61%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3ECrear%20o%20eliminar%20una%20lista%20personalizada%20para%20ordenar%20y%20rellenar%20datos%3C%2FA%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%3EIn%20the%20attached%20file%20the%20example%20with%20the%20table%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20I%20was%20able%20to%20help%20you%20with%20this%20information.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fexcel-blog%2Fmeet-niko-chatzoudis-excel-forum-contributor%2Fba-p%2F2941385%22%20target%3D%22_blank%22%3ENikolinoDE%3C%2FA%3E%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3248251%22%20slang%3D%22en-US%22%3ERe%3A%20Sorting%20out%20data%20from%20a%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3248251%22%20slang%3D%22en-US%22%3EPS%20-%20Until%20%22Sep%2006%22%20it's%20what%20I%20have%20done%20manually.%20Thereafter%20it's%20the%20original%20file.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3248242%22%20slang%3D%22en-US%22%3ERe%3A%20Sorting%20out%20data%20from%20a%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3248242%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F722750%22%20target%3D%22_blank%22%3E%40NikolinoDE%3C%2FA%3E%2C%20many%20thanks.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOS%3A%20Windows%2010%3C%2FP%3E%3CP%3EExcel%20version%3A%20H%26amp%3BS%202019%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFil'e%20attached.%20It's%20public%20data%2C%20no%20sensitive%20info.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETanks%3C%2FP%3E%3CP%3ELuis%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3248197%22%20slang%3D%22en-US%22%3ERe%3A%20Sorting%20out%20data%20from%20a%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3248197%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F101468%22%20target%3D%22_blank%22%3E%40Luis%20Serpa%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFirst%20of%20all%2C%20based%20on%20the%20text%2C%20I%20can%20recommend%20inserting%20the%20columns%20into%20a%20table%20and%20using%20filters%20to%20display%20the%20data%20as%20desired.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20more%20and%20precise%20information%2C%20please%20add%20more%20information.%3C%2FP%3E%3CP%3EWhich%20%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Fwindows%2Fwhich-version-of-windows-operating-system-am-i-running-628bec99-476a-2c13-5296-9dd081cdd808%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Eoperating%20system%3C%2FA%3E%3F%3C%2FP%3E%3CP%3EWhich%20%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Fabout-office-what-version-of-office-am-i-using-932788b8-a3ce-44bf-bb09-e334518b8b19%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EOffice%20version%3C%2FA%3E%3F%3C%2FP%3E%3CP%3EWhere%20are%20the%20files%20stored%3F%3C%2FP%3E%3CP%3E(OneDrive%2C%20Sharepoint%2C%20hard%20drive%2C%20USB%2C%20etc.).%3C%2FP%3E%3CP%3EInsert%20the%20files%20(without%20sensitive%20data).%3C%2FP%3E%3CDIV%20class%3D%22%22%3E%3CDIV%20class%3D%22%22%3EDrag%20and%20drop%20here%20or%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fexcel%2Fsorting-out-data-from-a-column%2Fm-p%2F3248192%23%22%20target%3D%22_blank%22%3Ebrowse%3C%2FA%3E%20files%20to%20attach%3C%2FDIV%3E%3CDIV%20class%3D%22%22%3EMaximum%20size%3A%2071%20MB%20%E2%80%A2%20Maximum%20attachments%20allowed%3A%205%3C%2FDIV%3E%3C%2FDIV%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20understanding%20and%20patience%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fexcel-blog%2Fmeet-niko-chatzoudis-excel-forum-contributor%2Fba-p%2F2941385%22%20target%3D%22_blank%22%3ENikolinoDE%3C%2FA%3E%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi all,

 

I have a very long column (28281 rows) with dates and times (format: dd/mm/yyyy hh;mm). There are no breaks between days, weeks or months: data is continuous. Next two columns are numbers (wind speeds, min. and max). Each day has hourly data, i. e. each day has 24 rows. Data is for three years, with some missing months mainly in the beginning. I would like to:

a) Insert an empty line between each month, in order to calculate averages and medians per month;

b) Delete lines that refer to hours between 20;00 and 08;00 each day. I only need daylight data.

 

Would someone help me finding a formula to do this? I have tried doing it manually, but it is very very long.

Thanks in advance,

Luis Serpa

8 Replies

@Luis Serpa 

First of all, based on the text, I can recommend inserting the columns into a table and using filters to display the data as desired.

 

For more and precise information, please add more information.

Which operating system?

Which Office version?

Where are the files stored?

(OneDrive, Sharepoint, hard drive, USB, etc.).

Insert the files (without sensitive data).

Drag and drop here or browse files to attach
Maximum size: 71 MB • Maximum attachments allowed: 5

 

Thank you for your understanding and patience

 

NikolinoDE

I know I don't know anything (Socrates)

Hi @NikolinoDE, many thanks.

 

OS: Windows 10

Excel version: H&S 2019

 

Fil'e attached. It's public data, no sensitive info.

 

Tanks

Luis

PS - Until "Sep 06" it's what I have done manually. Thereafter it's the original file.

@Luis Serpa 

 

Crear o eliminar una lista personalizada para ordenar y rellenar datos

In the attached file the example with the table.

 

Hope I was able to help you with this information.

 

NikolinoDE

I know I don't know anything (Socrates)

Thanks, NikolinoDE. Will try it tomorrow.

Cheers

Luis
best response confirmed by NikolinoDE (Respected Contributor)
Solution

@Luis Serpa I suggest you look into Power Query as demonstrated in the attached example. In general it's not a good idea to insert empty rows in data sets to hold calculations relating to sub-sets of the data. In your case averages and medians per month.

Thank you so much, Riny. I'm going to try this method, it seems perfect.

@Riny_van_Eekelen, perfect! Many many thanks.

 

Luis