Excel Magic Trick 759: Array Formula To Sort List & Remove Duplicates - Dynamic Named Range

Download file: https://people.highline.edu/mgirvin/ExcelIsFun.htm

See how to create a Sorted Unique List Of Words With Formula using INDEX, MATCH, COUNTIF functions and Comparative Operators. Learn about using the INDEX function and Defined Names to create a dynamic range so that the formula will pick up any new names added to list and automatically re-sort the unique list. Array Formula. Also see the REPT function. Sort List With Formula and Remove Duplicates. Richard Schollar at Mr Excel Message Board Posted This Amazing Formula

Category:

Science & Technology

Tags:

• Excel
• 2007
• 2010
• excelisfun
• Mike
• Gel
• Girvin
• Highline
• Community
• College
• Slaying
• Dragons
• Sorted
• Unique
• Remove
• Duplicates
• List
• Of
• Words
• With
• Formula
• using
• INDEX
• MATCH
• COUNTIF
• REPT
• Function
• and
• Comparative
• Operators
• function
• Defined
• Names
• to
• create
• dynamic
• range
• Richard
• Schollar
• at
• Mr
• Message
• Board
• Posted:

[Jasonrubalcava]

ok this is a long shot, but i need someone to help or make a spreadsheet to do the things i need it to do..you seem to know what your doing. this maybe in multiple comments so please bare with me. i am a karaoke host and i need a program to assist with the singer rotation. here is how i do it manually..lets say i have 20 singers in one night..we are on our 1st rotation (haven't went all the way through the list), as ppl come to me to add them to the list they simply go to the bottom of the

[ExcelIsFun]

@Jasonrubalcava , post question to THE best excel question site:

mrexcel [dot] com/forum

[krn14242]

Mike, great trick and formula but having extended processing time with huge (400 rows) of sorted, unique values. It is because my array is large?

[ExcelIsFun]

@krn14242 , yes. Maybe VBA? Post question to:

mrexcel [dot] com/forum

[krn14242]

@ExcelIsFun Mike, I think I got it. I just added =if(a2="","",.... and that appears to have stopped the extended processing. Before, I think the index formula was trying to get unique values from all the blanks somehow and attempting to sort.

[ExcelIsFun]

@krn14242 , cool!

[Jasonrubalcava]

4th

Second rotation:

The second rotation consists of the first singer (now an "old" singer) then a New singer. Then it continues old singer, new singer, old singer, new singer. If the new singers exceed the number of old singers, they are just added to the end of the list

[Jasonrubalcava]

3rd comment..here is a good rule of thumb i use....The Karaoke rotation is a difficult thing to manage. In an attempt to be fair to all participants, I use the "Old" Singer, "New" Singer method. Which has nothing to do with age but it works like this:

First rotation:

The first 12 to 15 singers to request songs will make up the first rotation. All singers are entered into the rotation on a first come, first sing basis.

[Jasonrubalcava]

2nd comment....list...then when i get to the 2nd rotation (all the way through the list) i start over..but..when new ppl come in the middle of the 2nd rotation (new singer) i put them within the top 5 of what ever number im on..thus number 7 is singing and i put the new singer in the 11a spot..everyone behind him/her gets moved down the list. i also need it to remember the singers song and key change on another sheet..any ideas??

[ExcelIsFun]

@incantar , cool! I am glad that there were 20 cool Excel bits in this video!