Extract Readings from One sheet to another sheet

[thong]

Hello All,

I have a reading of temperatures and Humidity for one month every five minutes. My problem is I want to copy only selected time to another worksheet.

the Time that I want to copy are,

1/1/2019 0:02
1/1/2019 1:02
1/1/2019 2:02
1/1/2019 3:02
1/1/2019 4:02
1/1/2019 5:02
1/1/2019 6:02
1/1/2019 7:02
1/1/2019 8:02
1/1/2019 9:02
1/1/2019 10:02
1/1/2019 11:02
1/1/2019 12:02
1/1/2019 13:02
1/1/2019 14:02
1/1/2019 15:02
1/1/2019 16:02
1/1/2019 17:02
1/1/2019 18:02
1/1/2019 19:02
1/1/2019 20:02
1/1/2019 21:02
1/1/2019 22:02
1/1/2019 23:02

Up to 1/31/2019 11:02:00 PM

Please find attached file that I'm currently working on.
Cross posted to this link

Thank you so much.

thong

 

[Chihiro]

Personally, I'd just add helper column to data and use pivot table to summarize.

Ex:
In E2 =MINUTE(B2)=2 copy down.

See attached.

If you have access to Excel 2016 (PowerQuery & PowerPivot). You can control layout of report quite a bit more (using PowerView etc).

 

[vletm]

thong
... or press [ Do It ] in 'Hourly Readings -sheet

 

[thong]

Hi vletm,

Thank you so much! Im crying for another help, can you please do also extraction of the dates.

Thanks,

 

[vletm]

thong
Which dates are missing?

 

[thong]

Hi vletm,

There's no missing date. What I mean is for example when I change the month for February, it will automatically copy the dates too..

Thank you

 

[vletm]

thong
... and in the beginning
You wrote only about Jan-2019 ... hmm?
... and now, any month and only dates ...
I added dates and times, based 'Raw Data's Time-column.

 

[thong]

Thanks vletm...

 

[thong]

Hi Vletm,

Question, What part of the code Im going to change if I want to copy different time ending like .12 ; or .27.

Thank you so much.

 

[vletm]

thong
+ TimeSerial(0,2,0) -

 

[thong]

If I copy the time ending in .12 I will change it to

+ TimeSerial(0,12,0) -?

Thanks

 

[vletm]

thong
I tried to give exact part to change ...
Would it better to use this version?
... and You would also notice something from Your data ... or not?

 

[AlanSidman]

Using Power Query, here is the MCode

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Sensor", type text}, {"Time", type datetime}, {"Humid", type number}, {"Temp", type number}}),
    #"Split Column by Delimiter" = Table.SplitColumn(Table.TransformColumnTypes(#"Changed Type", {{"Time", type text}}, "en-US"), "Time", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Time.1", "Time.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Time.1", type date}, {"Time.2", type time}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(Table.TransformColumnTypes(#"Changed Type1", {{"Time.2", type text}}, "en-US"), "Time.2", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Time.2.1", "Time.2.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Time.2.1", Int64.Type}, {"Time.2.2", type text}}),
    #"Split Column by Delimiter2" = Table.SplitColumn(#"Changed Type2", "Time.2.2", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Time.2.2.1", "Time.2.2.2"}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"Time.2.2.1", Int64.Type}, {"Time.2.2.2", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type3", each ([Time.2.2.1] = 2)),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Filtered Rows", {{"Time.2.1", type text}, {"Time.2.2.1", type text}}, "en-US"),{"Time.2.1", "Time.2.2.1"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged"),
    #"Merged Columns1" = Table.CombineColumns(#"Merged Columns",{"Merged", "Time.2.2.2"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged.1"),
    #"Merged Columns2" = Table.CombineColumns(Table.TransformColumnTypes(#"Merged Columns1", {{"Time.1", type text}}, "en-US"),{"Time.1", "Merged.1"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Time")
in
    #"Merged Columns2"