+ Reply to Thread
Results 1 to 13 of 13

Need help creating a dynamic list that sorts with count/frequencies

  1. #1
    Registered User
    Join Date
    10-19-2022
    Location
    Minneapolis, MN
    MS-Off Ver
    365
    Posts
    4

    Exclamation Need help creating a dynamic list that sorts with count/frequencies

    I'm trying to create a dynamic list (column I) that will populate based off the sorted city (column F) and the count (column G) - I think i figured out the sorting part (columns E-G) and I currently typed in the dynamic list in column I but I'm hoping someone can help me create a formula that will update the dynamic list if the store count or sorted cities change. Thank you!!!

    Sorted List Example.xlsx
    Last edited by egreen1; 10-19-2022 at 04:50 PM.

  2. #2
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Need help creating a dynamic list that sorts with count/frequencies

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

  3. #3
    Registered User
    Join Date
    10-19-2022
    Location
    Minneapolis, MN
    MS-Off Ver
    365
    Posts
    4

    Re: Need help creating a dynamic list that sorts with count/frequencies

    Thanks for the reply, I tried this formula out in cell I2 and got a "#NAME?" error - any suggestions?

  4. #4
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Need help creating a dynamic list that sorts with count/frequencies

    You're version of Excel is obviously out of date, so it depends on which of the functions you are missing.
    Do you have LET & SCAN

  5. #5
    Registered User
    Join Date
    10-19-2022
    Location
    Hungary, Szeged
    MS-Off Ver
    365
    Posts
    10

    Re: Need help creating a dynamic list that sorts with count/frequencies

    Hi, see the attached workbook (with an auxiliary column) for a possible solution...

    The formulas used in the table:
    H2: =IF(G2="","",2)
    H3: =IF(G3="","",H2+G2) (Range: H3:H12)
    I2: =IF(ROW()<INDEX(H:H,MATCH(ROW(),H:H),1)+INDEX(G:G,MATCH(ROW(),H:H),1),INDEX(F:F,MATCH(ROW(),H:H),1)&"-"&ROW()-INDEX(H:H,MATCH(ROW(),H:H),1)+1,"") (Range: I2:I30)
    Attached Files Attached Files

  6. #6
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Need help creating a dynamic list that sorts with count/frequencies

    If it's just the Scan function you don't have try
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    10-19-2022
    Location
    Minneapolis, MN
    MS-Off Ver
    365
    Posts
    4

    Re: Need help creating a dynamic list that sorts with count/frequencies

    It was just the SCAN formula that wasn't available which is odd because i have the latest version of excel. I tried the new formula you provided and this is the result:
    Attachment 801619

  8. #8
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Need help creating a dynamic list that sorts with count/frequencies

    Worksheet event with VBA code can be tried . Is it ok ?
    It will be dynamic list.
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  9. #9
    Registered User
    Join Date
    10-19-2022
    Location
    Minneapolis, MN
    MS-Off Ver
    365
    Posts
    4

    Re: Need help creating a dynamic list that sorts with count/frequencies

    This worked perfectly, THANK YOU!

  10. #10
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Need help creating a dynamic list that sorts with count/frequencies

    Unfortunately the attachment is coming back as invalid. Does it work in the attached.
    Attached Files Attached Files

  11. #11
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Need help creating a dynamic list that sorts with count/frequencies

    This worked perfectly, THANK YOU!
    What did? ...

  12. #12
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Need help creating a dynamic list that sorts with count/frequencies

    Please amend your profile to show the 365 VERSION you have...

    model it on my profile.
    Attached Images Attached Images
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  13. #13
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Need help creating a dynamic list that sorts with count/frequencies

    Worksheet event code
    Please Login or Register  to view this content.
    Code uses only columns A and B.
    Range used is A2:B10000. 100000 can be changed as required.
    Attached Files Attached Files

+ 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. [SOLVED] Count frequencies for numbers that appears more than one in columns
    By Karnik in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-10-2019, 07:04 PM
  2. [SOLVED] Count number of stoppage frequencies
    By cutebeach in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 10-18-2016, 03:15 AM
  3. Pseudorandom draw from list. Probabilities defined by dynamic relative frequencies
    By jakopak in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-16-2015, 03:09 AM
  4. Creating a dynamic dropdown list of a selection of a master list
    By RooMar in forum Excel Formulas & Functions
    Replies: 20
    Last Post: 05-15-2015, 05:27 AM
  5. Why doesn't this VBA code count the frequencies of each value?
    By seigna in forum Excel Programming / VBA / Macros
    Replies: 38
    Last Post: 04-15-2013, 06:42 PM
  6. Macro to count and filter frequencies
    By EAGLEBUCKS in forum Excel General
    Replies: 0
    Last Post: 07-29-2011, 08:04 AM
  7. how to count the number of text frequencies and copy to other cell
    By DG in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-06-2005, 03:05 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1