+ Reply to Thread
Results 1 to 7 of 7

Taking a list of data and transforming it + adding a drop down to make it searchable

  1. #1
    Registered User
    Join Date
    06-18-2020
    Location
    Arizona
    MS-Off Ver
    Office 365
    Posts
    75

    Taking a list of data and transforming it + adding a drop down to make it searchable

    Hi there,

    In the attached file I have a list of data in the 'Raw Data' worksheet that I want to reformat into what you see in the 'Summary' worksheet. Additionally I would like there to be a drop-down list by date on the Summary worksheet that allows me to select dates to populate the information in that format. Is that possible via a function? I am familiar with PQ if that helps any.
    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,810

    Re: Taking a list of data and transforming it + adding a drop down to make it searchable

    Here is a PQ solution

    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-18-2020
    Location
    Arizona
    MS-Off Ver
    Office 365
    Posts
    75

    Re: Taking a list of data and transforming it + adding a drop down to make it searchable

    That worked great for transforming the data, thank you! I learned how to use unpivot in PQ as well which is very useful.

    On to the second question in my OP, I would like there to be a drop-down list populated by date on the Summary worksheet that allows me to select weekly dates to populate the information in that format. For example, if I chose from the drop-down "9/14/2020" it populates data from that week in the aforementioned format. Is this possible?

  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,810

    Re: Taking a list of data and transforming it + adding a drop down to make it searchable

    I created a parameter query where you will select a date in the following format 9/20/2020 and input it and then select Refresh All on the Data Tab. See Attached file.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    06-18-2020
    Location
    Arizona
    MS-Off Ver
    Office 365
    Posts
    75

    Re: Taking a list of data and transforming it + adding a drop down to make it searchable

    This is a step in the right direction! Is there a way to make it a dropdown of dates? And where can I go to learn how you did this? It's not immediately apparent to me.

    Thank you!

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: Taking a list of data and transforming it + adding a drop down to make it searchable

    As to a drop down of dates:
    As they appear to be workdays (Monday:Friday) you may want the first cell, say cell XFD2, in the list to reference the earliest date on the Excel Help Forum sheet, i.e. ='Excel Help Forum'!B2
    Cells XFD3 and down are populated using: =WORKDAY(XFD2,1)
    Cell H2 is then populated using data validation with a source of the list of cells XFD2 and down. i.e. =$XFD$2:$XFD$100
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  7. #7
    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,810

    Re: Taking a list of data and transforming it + adding a drop down to make it searchable

    To learn how to do Parameter Queries.

    https://www.youtube.com/watch?v=gK2yBpiITvI

+ 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. Searchable Drop-Down List Graph with No data forTimepoints
    By llogballig in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-01-2020, 02:19 PM
  2. Drop down menu to reference different sheets in searchable drop down list
    By MJAHNKE in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-05-2019, 01:00 PM
  3. Searchable Drop Down List
    By mhkeim in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-06-2018, 06:48 PM
  4. Make Dependent Drop Box Searchable
    By DawnA530 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-19-2018, 05:45 PM
  5. [SOLVED] Searchable drop down list
    By roadwreck in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-19-2018, 04:52 AM
  6. Searchable Drop down list
    By axangec in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 01-20-2016, 08:43 AM
  7. Searchable Drop down list
    By Jesscyca in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-21-2014, 11:03 AM

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