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

[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??

[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!

[incantar]

Thanks. You are a good teacher. I learned about 20 excel tricks in one video. Plus, the video finally made me understand the following formula which does basically the same thing as your C2 formula in a slightly different way.

=INDEX(nn,MATCH(0,COUNTIF($C$1­:C1,nn),0))

Also, the way you say zero reminds me of Corky St Clair from Waiting For Guffman for some reason XD

[incantar]

@incantar Actually I lied because it doesn't sort them =(

[ExcelIsFun]

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

[nimble04]

Hi,

Just looking at this formula but it only works if there are no spaces in the array (Column A), can it be adapted to account for spaces in the data, and is there an easier way of doing this?

[ExcelIsFun]

Here are some videos that can deal with blanks:

Excel Magic Trick 473: Extract Unique Records with Formula (Complex Array Formula)

Excel Magic Trick 581: Unique List With Criteria Using Advanced Filter

Excel Magic Trick 690: Extract Unique Records Based on 2 Columns, List Values Horizontally

Excel Magic Trick 627: FREQUENCY Array Function (10 Examples) (WATCH THIS VIDEO IF YOU WANT TO LEARN ALL THE WHYS)

[funnyscaleman]

I have looked through a number of Sort issues but I still have a problem. I have a list of random numbers from cell A1 through cell A100. I need to flip them around where cell A100 is in cell A1 and vice verse while maintaining the order. How can I do that? Actually there are about 1500 rows I am using in column A.

[ExcelIsFun]

This video should do it:

Mr Excel & excelisfun Trick 10: Turn Column / Row Upside Dow

[ExcelIsFun]

Formula would be:

=INDEX($A$1:$A$1500,ROWS(A1:A$­1500))

in cell B1 and copy down.

[gk039]

Hi,could you please write the full url for the thread at Mr Excel Message Board as it cannot being seen on this video. Searching in the forum i couldn't find it.Thanks in advance.

[ExcelIsFun]

Comments will not allow it. Downlaod the workbook. The link is in the workbook and then you can just click it.

[gk039]

@ExcelIsFun

OK. Thanks a lot. Regards. George.

[RandyKelly19]

any advantage to doing it this way as opposed to adv. filter unique records?

[ExcelIsFun]

The advantage to formulas is that they automatically update when data changes. If you use a dynamic range (Table feature or Defined Name Formula), it makes it even more automatic.

[ExcelIsFun]

If you are doing it only once, Advanced Filter, Sort is great - I do this all the time.

[Markblackburn86]

Got any Access videos? :)

I posted the Q on MrExcel.

Was thinking that I could save each month as a seperate Workbook with a summary page. Then link all the summary pages to a Master Workbook, and then build pivots from that. the summary page would be about 5,000 rows. what do you think? or should I start learning Access?

[ExcelIsFun]

Oh. Of course, what you do is to use PowerPivot!! You can have many millions of rows. Mr excel has some good videos for PowerPivot.

[Markblackburn86]

ok cool, I will give that a go. Thanks Mike.

[loyaus]

Really superb trick, I got clear few doubts about "Match" Function. is there any video to remove duplicates about entire table?.

Thanks in advance!!.

[ExcelIsFun]

How about Advanced Filter, Unique Records and the Sort - that way you don't permanently remove records like you do (I think) with Remove Duplicates.

Try this video title:

Excel Magic Trick 660: Advanced Filter Unique Records Only

[6642633]

Thank Mike

VERY Helpfulllllll video

[ExcelIsFun]

You are welcome!

[Markblackburn86]

wow, I think i need to watch this one a few times. really awesome! Thanks

[ExcelIsFun]

You are welcome!

[Markblackburn86]

HI Mike,

Is there a faster formula, that will accomplish the same thing? (can this formula be tweaked?) I am using this on a range of 100,000 rows, and its really slow to calculate. Or would a Macro be a better solution? i.e.Removed duplicates, then sort.

Look forward to your response.

[ExcelIsFun]

How about Advanced Filter, Unique Records and the Sort - that way you don't permanently remove records like you do (I think) with Remove Duplicates.

As far as formulas, this formula is pretty fast - it is the 100,000 that is slowing it down - not much you can do about 100,000 rows.

[Markblackburn86]

Thanks Mike,

Advanced filter it is. I am a bit worried, this database is going to grow by 100,000 per month. Any suggestions what I can do after month 10?

[ExcelIsFun]

I would switch to Access.

[krn14242]

Wow, too cool. 

[ExcelIsFun]

I am glad that you like it!!!

[plastikman001]

Pure magic!

One question though. It will generate an error, or lost of zeros, if you have an empty cell in the middle of the “List With Dups and Not Sorted” column (remove for example “Joe”). Is there an easy way to solve this?

in

[ExcelIsFun]

Yes. Please go to the Mr Excel Message Board link (as seen in video or downloadable workbook) where there are about 30 + comments and a few of use provided solutions for when there are blanks.

[ssalazarjr]

Brilliant!

[ExcelIsFun]

I am glad that you like the video!

[emailuznow]

Silly Me

you just had to change this

Less than symol to greater than symbol

For some reason youtube dont allow to out the comparative syntax in

[emailuznow]

Silly Me

you just had to change this

[emailuznow]

Hi Mike,

This sort is from smallest to largest

How can you tweak the formula to show largest to smallest?

Thanks

[ExcelIsFun]

You got it! Switch less than to greater than.

[planiolro]

Pure Magic. A great way to start 2011

[ExcelIsFun]

I am glad that you like it!

Happy New Year!

[baleshst]

Hey sorry Mike, I commented on the wrong video, yes those comments were for this video. I go by the same name in Mr excel as here.

However, I found out a major glitch in my solution. I used the code function, i guess that is the major drawback. I guess i was the second person to comment on this thread.

[ExcelIsFun]

Nice to meet you, baleshst !!



[moazzamca1]

Great. I have a problem. I have to develop a file to assign reference no. to our letters. There are 5-6 departments who currently need to mention a running no. on their letters (unique for every letter). Currently a secretary keeps a register and tells the next available no. on phone. I want to create a shared workbook. All departments will have their own sheet wherein they enter letter particulars on a row. They should get next available no. against the data on the same row. Any idea how to do

[ExcelIsFun]

Post this question to the Mr Excel Message Board. It is MUCH easier to work on a solution at the Mr Excel Message Board because we can post examples and it is easier to communicate back and forth and there are not as many limitations about what you can post as there here at YouTube Comments.

mrexcel[dot]com

You can e-mail me the link and then I will take a look - although many other smarter Excel people may give you an answer first.

[hamy72]

Great Videos Mike to kickstart 2011........ Can't wait to see the rest...... BTW, are you going to start some videos on Access too? Coz, that would be really super cool! Thanks always for making us a little more enlightened... And wishing you and yuour family more "FUN" during 2011

[ExcelIsFun]

Thanks, hamy72! I wish you and your family more "fun" in the New Year also!!

I am going to do some Access videos in the new “Office 2010” series, but they will only be basic Access videos. Although I know something about Excel, I am no expert when it comes to Word, PowerPoint and Access. I do know some basics, but that is all.

[TheSandywarrior]

Hello Mike. For the 2003 version, you could also have this formula in another cell to count the number of unique names

=SUMPRODUCT((LEN(nn)>0)/COUNTI­F(nn,nn&""))

and you original formula will then become

=IF(ROWS($K$1:K1)<=$J$2,INDEX(­nn,MATCH(0,COUNTIF(nn,"<"&nn)-­SUM(COUNTIF(nn,C$1:C1)),0)),""­)

where J2 is where you have the SUMPRODUCT formula.

This may speed up the calc if the range is big

[ExcelIsFun]

Beautiful, TheSandywarrior !! Quite Nice!

[abcd321839955]

amazing, as always!! ... keep going!!

[ExcelIsFun]

I am glad that you liked it!

[Accounting010203]

Mr Excel Message Board Rules!

[ExcelIsFun]

I couldn't agree more!!