+ Reply to Thread
Results 1 to 13 of 13

Dynamic List from matrix

  1. 09-15-2021, 04:38 PM #1
    deikenberry01
    deikenberry01 is offline
    Registered User
    Join Date
    09-15-2021
    Location
    USA
    MS-Off Ver
    365
    Posts
    2

    Dynamic List from matrix

    Hi All,

    I have a matrix of data and I'm looking for a way to dynamically create a single-column list from it. Example sheet is attached.

    On the example, I have 20 Store locations listed on a Mon-Fri column-based matrix (Cells A1 through E10). I'd like to have the yellow & red column below (Cells B13 through B32) automatically fill out, skipping blanks, based on what's in the matrix above.

    This sheet will be passed around departments so I'm hoping I can do it with some sort of Index/indirect formula as opposed to macros or vba.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 09-15-2021, 07:44 PM #2
    Estevaoba
    Estevaoba is offline
    Forum Expert
    Join Date
    06-05-2017
    Location
    Brazil
    MS-Off Ver
    Microsoft 365 Version 2404
    Posts
    1,259

    Re: Dynamic List from matrix

    Try in B13 and copy down:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    Good luck!
    Reply With QuoteRegister To Reply
  3. 09-15-2021, 11:52 PM #3
    menem
    menem is offline
    Forum Expert
    Join Date
    09-30-2019
    Location
    Chiangmai, Thailand
    MS-Off Ver
    Office 2016, Excel 2019
    Posts
    1,234

    Re: Dynamic List from matrix

    Please try

    Please Login or Register  to view this content.
    If you want to list [ right then down ] may change to.

    Please Login or Register  to view this content.
    Regards.
    Last edited by menem; 09-15-2021 at 11:55 PM.
    My English is very poor, so please be patient >_<"

    My Dynamic Related Dropdown post:
    https://www.excelforum.com/excel-for...ml#post5657376
    https://www.excelforum.com/excel-for...ml#post5655711
    https://www.excelforum.com/excel-for...ml#post5655762
    Reply With QuoteRegister To Reply
  4. 09-16-2021, 02:37 AM #4
    wk9128
    wk9128 is offline
    Forum Expert wk9128's Avatar
    Join Date
    08-15-2020
    Location
    Australia
    MS-Off Ver
    365 V2403 and WPS V2022
    Posts
    3,416

    Re: Dynamic List from matrix

    Cell B13 Array Formula , Drag down

    HTML Code: 
    Reply With QuoteRegister To Reply
  5. 09-16-2021, 10:58 AM #5
    deikenberry01
    deikenberry01 is offline
    Registered User
    Join Date
    09-15-2021
    Location
    USA
    MS-Off Ver
    365
    Posts
    2

    Re: Dynamic List from matrix

    All 3 solutions worked! Thank you!
    Reply With QuoteRegister To Reply
  6. 09-16-2021, 10:59 AM #6
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,830

    Re: Dynamic List from matrix

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  7. 09-16-2021, 11:30 AM #7
    Estevaoba
    Estevaoba is offline
    Forum Expert
    Join Date
    06-05-2017
    Location
    Brazil
    MS-Off Ver
    Microsoft 365 Version 2404
    Posts
    1,259

    Re: Dynamic List from matrix

    You're welcome. Glad to help.
    Thank you for the feedback and for the reputation added.
    Have a blessed day!
    Reply With QuoteRegister To Reply
  8. 09-16-2021, 12:03 PM #8
    wk9128
    wk9128 is offline
    Forum Expert wk9128's Avatar
    Join Date
    08-15-2020
    Location
    Australia
    MS-Off Ver
    365 V2403 and WPS V2022
    Posts
    3,416

    Re: Dynamic List from matrix

    @deikenberry01 You're Welcome. Glad to help . Thank You for the feedback
    Reply With QuoteRegister To Reply
  9. 09-16-2021, 03:59 PM #9
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,406

    Re: Dynamic List from matrix

    Late again.

    Something different. A bit long but works.

    I did this in C13 and filled down to "shadow" expected results.

    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    Edit: Those without Office 365 may have to array enter that.
    Last edited by FlameRetired; 09-16-2021 at 04:16 PM.
    Dave
    Reply With QuoteRegister To Reply
  10. 09-16-2021, 04:14 PM #10
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,406

    Re: Dynamic List from matrix

    Managed to shorten my previous slightly.
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  11. 09-16-2021, 11:05 PM #11
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,406

    Re: Dynamic List from matrix

    This uses a helper column to return the column indexes the number of times text appears in those columns. It's a construction BMV showed a few months ago. Bo_Ry uses it a lot. I've repurposed it here. In C13 and filled down.
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Then in D13 and filled down this returns the desired store list.
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    This just seemed far simpler than my previous posts.
    Attached Files Attached Files
    Last edited by FlameRetired; 09-16-2021 at 11:10 PM.
    Reply With QuoteRegister To Reply
  12. 09-17-2021, 01:47 AM #12
    Bo_Ry
    Bo_Ry is offline
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Dynamic List from matrix

    Another one

    =FILTERXML("<x><m>"&TEXTJOIN("</m><m>",,TRANSPOSE(A2:E10))&"</m></x>","//m")

    If store name contain ampersand.
    =FILTERXML("<x><m>"&TEXTJOIN("</m><m>",,TRANSPOSE(SUBSTITUTE(A2:E10,"&","&amp;")))&"</m></x>","//m")
    Attached Files Attached Files
    Last edited by Bo_Ry; 09-17-2021 at 02:02 AM.
    Linkedin/Bo-Rydobon
    Youtube.com/XcWizard
    Facebook.com/XcWizard
    Reply With QuoteRegister To Reply
  13. 09-17-2021, 05:42 PM #13
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,406

    Re: Dynamic List from matrix

    Another way. (I was making things way too difficult before.)

    Same helper formula in column C. Then this in D13 and filled down.
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Dynamic list from matrix
    By SebastianNÄ in forum Excel General
    Replies: 4
    Last Post: 06-22-2021, 08:22 AM
  2. Dynamic Covariance Matrix
    By DBell87 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-09-2020, 11:13 AM
  3. Match a person from a matrix [need dynamic dropdown list]
    By Pavel Varganov in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-18-2020, 12:12 AM
  4. [SOLVED] Dynamic list within list from matrix
    By philiplaw in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 01-18-2018, 07:20 AM
  5. [SOLVED] Create dynamic list by looking up matrix
    By Lana14 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 04-27-2017, 07:34 AM
  6. [SOLVED] Populating a Matrix using a dynamic list
    By dcossyl in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 06-19-2015, 01:06 PM
  7. [SOLVED] Excel problem with dynamic matrix with a name
    By Gert-Jan in forum Excel General
    Replies: 0
    Last Post: 04-03-2006, 05:55 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1