+ Reply to Thread
Results 1 to 8 of 8

Automatically sort list from index/match formula

  1. 01-09-2023, 07:25 PM #1
    Mae Stoll
    Mae Stoll is offline
    Registered User
    Join Date
    10-17-2011
    Location
    TX
    MS-Off Ver
    MS Home and Office 2019
    Posts
    10

    Automatically sort list from index/match formula

    I'm using the following array formula to generate a list of unique names from an original list where a name can appear multiple times.

    INDEX($B$3:$B$13,MATCH(0,COUNTIF($D$2:D2,$B$3:$B$13),0))

    Works great, BUT, I need the new list to appear in alphabetical order, right away (in column D), or copied to another column and sorted. It would be simple to do manually, of course, but the original list will change from time to time, so the sorting would need to happen "magically"!

    Thanks so much in advance. I've enclosed a simple example.

    Mae
    Auto sorting question.xlsx
    Last edited by Mae Stoll; 01-09-2023 at 07:30 PM.
    Reply With QuoteRegister To Reply
  2. 01-09-2023, 08:13 PM #2
    Phuocam
    Phuocam is online now
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,908

    Re: Automatically sort list from index/match formula

    Try this in D4

    =INDEX($B$4:$B$14,MATCH(AGGREGATE(14,6,COUNTIF($B$4:$B$14,">"&$B$4:$B$14)/(COUNTIF($D$3:D3,$B$4:$B$14)=0),1),
    COUNTIF($B$4:$B$14,">"&$B$4:$B$14),0))

    Enter with Ctrl+Shift+Enter.
    Reply With QuoteRegister To Reply
  3. 01-09-2023, 08:35 PM #3
    sandy666
    sandy666 is offline
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Automatically sort list from index/match formula

    with Power Query

    total list total list
    bob alex
    mary betty
    betty bob
    bonnie bonnie
    alex cathy
    cathy mary
    bob nancy
    nancy
    bob
    nancy
    bonnie 


    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  4. 01-10-2023, 02:46 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,412

    Re: Automatically sort list from index/match formula

    Cell F4 array formula , Drag down

    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  5. 01-10-2023, 09:10 PM #5
    Mae Stoll
    Mae Stoll is offline
    Registered User
    Join Date
    10-17-2011
    Location
    TX
    MS-Off Ver
    MS Home and Office 2019
    Posts
    10

    Re: Automatically sort list from index/match formula

    Thanks so much!
    Reply With QuoteRegister To Reply
  6. 01-10-2023, 09:11 PM #6
    Mae Stoll
    Mae Stoll is offline
    Registered User
    Join Date
    10-17-2011
    Location
    TX
    MS-Off Ver
    MS Home and Office 2019
    Posts
    10

    Re: Automatically sort list from index/match formula

    Thanks for giving me the coding option....
    Reply With QuoteRegister To Reply
  7. 01-10-2023, 09:16 PM #7
    Mae Stoll
    Mae Stoll is offline
    Registered User
    Join Date
    10-17-2011
    Location
    TX
    MS-Off Ver
    MS Home and Office 2019
    Posts
    10

    Re: Automatically sort list from index/match formula

    Hello Phucam, It works, but when I try to save the file, it says "cant save file with circular reference...."
    Reply With QuoteRegister To Reply
  8. 01-11-2023, 08:30 AM #8
    Phuocam
    Phuocam is online now
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,908

    Re: Automatically sort list from index/match formula

    It works for me.
    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. Filter Sort Issue on Index Match (column with formula Index Aggregate)
    By SimChengKeng in forum Excel General
    Replies: 1
    Last Post: 08-17-2019, 01:11 PM
  2. sort data (smallest to largest) with formula (index - match)
    By chris1089 in forum Excel General
    Replies: 5
    Last Post: 10-25-2016, 11:48 AM
  3. [SOLVED] Sort results of INDEX/MATCH array formula remove blanks. . .
    By PeteABC123 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-14-2014, 01:53 PM
  4. Trying to create an if index match formula of some sort
    By Sivalensis in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 10-03-2014, 11:27 AM
  5. Formula to Index, Match, Rank, and Sort a Dynamic Range of Values
    By AustinLe in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-14-2014, 03:49 AM
  6. Automatically list values using INDEX and MATCH
    By Rob-V-1990 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-21-2014, 10:37 AM
  7. [SOLVED] How to change reference area automatically by dragging Index Match Formula down
    By Nuada in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-20-2013, 01:19 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