+ Reply to Thread
Results 1 to 8 of 8

Picking out select rows from a large table

  1. #1
    Registered User
    Join Date
    06-12-2020
    Location
    Montreal, Quebec
    MS-Off Ver
    365
    Posts
    7

    Picking out select rows from a large table

    I'm trying to build an overlay chart with two lines. One line tracks the number of Robin Hood users holding shares of AAL stock. The other line tracks the price of AAL stock.

    I downloaded user data from RobinTrack but it's poorly organized. I'm trying to chart the number of daily users (weekdays only) over two years. RobinTrack provides hourly data and includes weekends.

    RobinTrack's table has two columns: Date/Time and Number of Users. There are 24 rows for each day time stamped at hourly intervals. I only need one row for each weekday time stamped at 20:45 UTC. So I only need one hour per day and I don't need weekends at all.

    The price data is downloaded from Yahoo Finance and is fine. But I need the data from RobinTrack and the data from Yahoo to line up in terms of the date. The date column has to match for both time series I'm plotting (number of users and price).

    I've attached a copy of the spreadsheet and an image of what I would like the chart to look like.
    Attached Files Attached Files

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,803

    Re: Picking out select rows from a large table

    Using Power Query, I was able to filter out all times not equal to 12:45

    Please Login or Register  to view this content.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Registered User
    Join Date
    06-12-2020
    Location
    Montreal, Quebec
    MS-Off Ver
    365
    Posts
    7

    Re: Picking out select rows from a large table

    Hi Alan,

    I'm sure this works but could you dumb it down for me? Once I'm in Power Query Editor and I highlight the Date and Time column and I select Split Column by Delimiter do I select the field Custom from the drop down box? And if so what do I type into the box just below it?

    Attachment 682967

  4. #4
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,803

    Re: Picking out select rows from a large table

    Custom is the default name for the new column. You can change it now or later to your desired name. In the box below, you will want to use the delimiter "space"

  5. #5
    Registered User
    Join Date
    06-12-2020
    Location
    Montreal, Quebec
    MS-Off Ver
    365
    Posts
    7

    Re: Picking out select rows from a large table

    Hi Alan, would it be possible for you to attach the revised spreadsheet with the changes you made? Thanks!

  6. #6
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,803

    Re: Picking out select rows from a large table

    Sure thing! Attached.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    06-12-2020
    Location
    Montreal, Quebec
    MS-Off Ver
    365
    Posts
    7

    Re: Picking out select rows from a large table

    Great, thanks very much! I tried to "Add Reputation" to your post but it won't allow me.

  8. #8
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,803

    Re: Picking out select rows from a large table

    No issues, probably I was the last person you repped. Cannot rep the same person twice in a row. Your thoughts are appreciated.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Pivot Table from a table - not picking up full range
    By sam1859 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 01-29-2020, 08:06 AM
  2. Replies: 1
    Last Post: 04-23-2019, 06:51 AM
  3. VBA to Select Ungrouped Table Rows
    By IIVRglobal in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-22-2018, 08:06 AM
  4. Replies: 3
    Last Post: 02-02-2016, 01:49 PM
  5. Replies: 1
    Last Post: 02-06-2015, 07:42 AM
  6. picking same SMALL and LARGE values from list
    By Karolis in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-27-2011, 11:20 AM
  7. Picking data from a large worksheet
    By Nyman in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-14-2010, 06:28 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1