1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

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

Discussion in 'Ask an Excel Question' started by thong, Feb 6, 2019.

  1. thong

    thong Member

    Messages:
    67
    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

    Attached Files:

  2. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,246
    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).

    Attached Files:

    thong likes this.
  3. vletm

    vletm Excel Ninja

    Messages:
    4,805
    thong
    ... or press [ Do It ] in 'Hourly Readings -sheet

    Attached Files:

    thong likes this.
  4. thong

    thong Member

    Messages:
    67
    Hi vletm,

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

    Thanks,
  5. vletm

    vletm Excel Ninja

    Messages:
    4,805
    thong
    Which dates are missing?
    Screenshot 2019-02-06 at 22.50.14.png
  6. thong

    thong Member

    Messages:
    67
    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
  7. vletm

    vletm Excel Ninja

    Messages:
    4,805
    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.

    Attached Files:

    thong likes this.
  8. thong

    thong Member

    Messages:
    67
    Thanks vletm...
  9. thong

    thong Member

    Messages:
    67
    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.
  10. vletm

    vletm Excel Ninja

    Messages:
    4,805
    thong
    + TimeSerial(0,2,0) -
  11. thong

    thong Member

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

    + TimeSerial(0,12,0) -?

    Thanks
  12. vletm

    vletm Excel Ninja

    Messages:
    4,805
    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?

    Attached Files:

    sathishsusa likes this.
  13. AlanSidman

    AlanSidman Active Member

    Messages:
    446
    Using Power Query, here is the MCode

    Code (vb):
    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"

    Attached Files:

Share This Page