• 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

Chihiro

Excel Ninja
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

thong

Member
Hi vletm,

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

Thanks,
 

thong

Member
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

Member
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.
 

AlanSidman

Active Member
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

Top