• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Extract Readings from One sheet to another sheet

thong

Member
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
 

Attachments

  • Temperature reading.xlsx
    220.9 KB · Views: 3
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).
 

Attachments

  • Temperature reading.xlsx
    386.1 KB · Views: 3
Hi vletm,

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

Thanks,
 
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
 
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.
 

Attachments

  • Temperature reading.xlsb
    138.5 KB · Views: 6
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.
 
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?
 

Attachments

  • Temperature reading.xlsb
    145.2 KB · Views: 2
Using Power Query, here is the MCode

Code:
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"
 

Attachments

  • Temperature reading.xlsx
    250.1 KB · Views: 2
Back
Top