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

[@excelisfun]

You can also check out my videos on combining two lists into one here: Excel Compare / Merge Two Lists youtube [dot] com/playlist?list=PL83E0D7B674B73A06

[@excelisfun]

Beautiful formula you made! Advantage of yours is that if there are no empty cells it is shorter. Advantage of the MATCH REPT is that if there are empty cells, it will still get last.

[@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)

[@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.

[@excelisfun]

I tried to post the formula that will work on the data set in the above video, but the formula is more than 500 characters long, more than is allowed in the comments at KZbin. I will try to break it apart and post it in pieces. The formula in the next post, is a formula I learned from Domenic at the Mr Excel Message Board.

[@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.

[@excelisfun]

I am glad that they help and you can find what you want!

[@excelisfun]

I am glad to help! When I went to that Mr Excel link it was a tread from the year 2003...

[@excelisfun]

I am glad that you like the video!

[@excelisfun]

I am glad that you like it! Happy New Year!

[@excelisfun]

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

[@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 KZbin 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.

[@excelisfun]

I am glad that you liked it!

[@excelisfun]

I am glad that you like it!!!

[@excelisfun]

I do not have a video that shows extracting unique list for multiple data types AND SORTING. But I do have a few from extracting unique lists with no sort: Excel Magic Trick 473: Extract Unique Records with Formula (Complex Array Formula) Excel Magic Trick 698: Extract Unique Items w Formula For Data Validation Drop-Down List Excel Magic Trick #187: Unique List w Dynamic Formula Excel Magic Trick 627: FREQUENCY Array Function (10 Examples)

[@excelisfun]

You got it! Switch less than to greater than.

[@excelisfun]

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

[@excelisfun]

I couldn't agree more!!

[@excelisfun]

Yes, the formula in the video does not work on mixed text or text with empty cells. The formula for mixed data or empty cells is very complicated. In my new Array Formula book coming out in June 2013 I show a formula for it. It is the most complicated formula in the book.

[@cpm3521]

Do you have a link to this book or do you have a video with this information?

[@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]

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.

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

[@prasadavasare1]

A great trick as usual.....Just wanted to suggest a change in Name Range "nn", we can use =$A$2:INDEX($A$2:$A$6000,COUNTA($A$2:$A$6000))...much shorter than =A2:INDEX($A$2:$A$6000,MATCH(REPT("z",255),$A$2:$A$6000)).... this is what I remember and learnt from your one of the trick....You are my Excel Guru....thanks to Bill and Mike for every posts..... :-)

[@rahulghosalkar4204]

Hi Mike, I have ordered your two books today ,,, I m your big fan.

[@excelisfun]

Nice to meet you, baleshst !!

[@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.

[@excelisfun]

I am glad that the vids help!

[@tomoleusz]

That is exactly what I was looking for! Thank you very much!

[@excelisfun]

You are welcome!

[@excelisfun]

Oh, the IFERROR does not work in 2003, But I provided the 2003 formula in the downloadable workbook on the answer sheet.

[@ScottMarler]

Holy crap, that's a brilliant solution. Thanks for sharing it... I have to do this kind of thing a lot and was never able to figure out how to accomplish a sort like this with just one formula.

[@rbrtktl]

it is 2021 and still so amazing training!!

[@excelisfun]

Beautiful, TheSandywarrior !! Quite Nice!

[@excelisfun]

You are welcome!

[@anthonyverdin6743]

Whoa....My brain hurts...lol. I am so glad the new functions make this situation so much easier!!! Yay Microsoft!!! Good job Mike...

[@excelisfun]

You are right: soooooo much easier. But we did this old way for almost 40 years....

[@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)/COUNTIF(nn,nn&"")) and you original formula will then become =IF(ROWS($K$1:K1)

[@excelisfun]

Formulas like this are mostly impractical. Large data sets just kill large array formulas. It is best to switch over to VBA code. To have back and forth dialog to get Excel solutions, try posting here: mrexcel [dot] com/forum state your problem (be very clear about all the parameters of the project becasue code will have to be written) and ask for a VBA solution.

[@chrisagreathouse]

Your video's are amazingly informative with every detail of the who, what, why, when, where explained in simple easy terms! A++ and the excitement in your voice "Oh This is so exciting!! (16:39)" is always nice... Excel can be a dry subject... but never in your vid's! Thank you! You've saved my ass several times over when I'm stumped!

[@excelisfun]

@Jasonrubalcava , post question to THE best excel question site: mrexcel [dot] com/forum

[@excelisfun]

Oh well, the comments section of KZbin won't let me post the formula at all because there are characters that are not allowed.

[@excelisfun]

Cool!

[@steveholdaway6653]

man, you are a Godsend!! I don't know how many videos I've been through trying to find this and especially find someone that can explain it that well!! I'm using 2013 so had to make a few tweaks but works great! only problem is... I would like the results on the second worksheet. any ideas how to adapt??

[@excelisfun]

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

[@emanalbar]

Thank you so much Mike. You are the best :-)

[@jthalluri]

Wow. This is what exactly I was looking for. Thanks a ton. And, you explain really well. Evaluating the formula step by step helps ppl understand better. =SUM("AMAZING","YOU ROCK","THANK YOU") -J

[@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.

[@alfianmm]

the 2003 formula shows chin from top to bottom? i got what i needed after i checked out some of your earlier posts with regards to generating a unique list. i'm picking up on the dynamic part, checkin out more videos. these series are really great help, very clear and precise. cheers!

[@excelisfun]

This video should do it: Mr Excel & excelisfun Trick 10: Turn Column / Row Upside Dow

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

[@stevebrodhead2219]

Thank you so much for this wonderful video! You saved my day!

[@hmatpin]

Hi. Thanks for the video. It's amazing. On this example: If you clear the contents of any of the first four cells of the nn table, the whole lookup table gets filled with zeros That's kinda weird. Anyway, anyone who works with databases must know there must be no empty rows in a table. Thanks for all your videos. They've been very helpful to me.

[@excelisfun]

After you post, you can send me the link to your post and I can take a look, but there are many MUCH smarter Excel masters at this site than me and so you may get solutions from a few people!

[@teodosy]

You are great! Thank you for this tutorial!

[@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.

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

[@emraharslankececioglu439]

Congratulations amazing method, it's very brilliant!

[@excelisfun]

@krn14242 , yes. Maybe VBA? Post question to: mrexcel [dot] com/forum

[@oxistu4037]

Great trick, use it all the time. Now I need a way of sorting an entire table, names in rows and columns. Do you have any suggestion? I've tried to expand on the range but it gives me and error.

[@silveiramr]

ExcelIsFun Best Ever!!!

[@planiolro]

Pure Magic. A great way to start 2011

[@educational6621]

You Know This is Blow My Mind, Thanks Bruh

[@excelisfun]

Glad it is good, Hubert Lie!! Thanks for the support : )

[@educational6621]

@@excelisfun Hello ExcellsFun, I have one problem, Can we use this formula with indirect function, I want to make it flexible if i change the location of source. I have test it but indirect function can not work with dynamic array (name manager). Can you help me to solve my problem, Please..

[@educational6621]

Hello ExcellsFun, I have search in google. And i found that the indirect function can not be use with dynamic range (name manager). But there is one solution from 'RoryA (Account Name) '. We must tu make one define name again, example = name : Datatype, with the formula is (=Evaluate(the Cell that you want to be Location of indirect) ). After that you can use indirect funtion like this " Indirect(Datatype)".

[@renegadek9]

Would having letters and numbers within a cell and only numbers or only letters within other cells of the same dynamic range effect the outcome? Examples of what I have in the cells are LT, D3, D5, D2/4, 14, 30. Currently using 2003 at work and 2010 at home. It did not properly sort in either version. It only returned LT, D3, D5, D2/4 and none of the regular numbers.

[@excelisfun]

Formula would be: =INDEX($A$1:$A$1500,ROWS(A1:A$1500)) in cell B1 and copy down.

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

[@YourXLNerd]

i posted a video response to do something similar except pull uniques and sort by the summation of values in the corresponding column. I didn't know if you had already made a video answering that question or not. I'm sure you could come up with a better method. Cheers.

[@titandronic]

Wow, I love this video & the solution. Almost exactly what I have been looking for. Well almost comes from how do we get to the situation with duplicates in the list. In my case it is by combining two lists, that can have the same names on them. Would it be possible to add this prior to your solution? To clarify I want to join (append) two lists and then use your solution on the appended list. I need everything dynamic as it will be a repetitive update of both lists. Any thoughts would be great

[@wirechair]

2:12 "further than you'll ever have names" haahaahaha!!

[@aknoimak]

Wspaniała formuła = Great formula. It is super!!!!! Thank you. :))

[@excelisfun]

It does work in 2003.

[@MohamedAlyCLAY]

i can't build it myself but i will copy it in my sheets :) thanks every time for every video :)

[@oxistu4037]

What if at the end I want to say total?? what modification should I put on the formula.. thank you your formula has been very helpfull

[@favoritos1608]

Hello. Great Video!! I just have 1 question, if I have numbers and text then the sorting of the text is eliminated, is tehre a way to work things when my list has both numeric and text fields?

[@excelisfun]

KZbin is not a good place to have back and forth dialog to get Excel solutions. Try this alternative site (not affiliated with excelisfun at KZbin): mrexcel [dot] com/forum IMPORTANT: 1) When you ask your question do not reference this video (they have not seen it), 2) state your question and describe your full problem. 3) say how the two data sets are set up, 4) give a small example with the expected results.

[@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.

[@craigpritch6906]

Fantastic. Thanks a ton.

[@excelisfun]

You are welcome a ton!!!

[@jacquedreyer2015]

Great formula with limitless possibility, so for me, import an to master. I can get it to work with the limited data in the complimentary excel sheet that accompanies this video however if i try it on my (large) data set i get some errors. The first is when I F9 (evaluate) the list of unique names. Excel returns "formula cannot be longer than 8192 characters. I assume that this is due to the length of the array in the formula? Has anyone successfully applied this formula, or a derivative, on a very large data set? Thanks for the really great channel - I love it, absolutely awesome!

[@mattolaughlin6532]

Wow, very helpful. Thank you. Can you address sorting and removing duplicates in a list (Dynamic Named Range) that has mixed values (numbers & words).

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

[@RandyKelly19]

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

[@leisuregirl1]

AMAZING!

[@ssalazarjr]

Brilliant!

[@excelisfun]

I would switch to Access.

[@erikmarius]

hi, great tip. im trying to do this on a long list of account numbers (about 7000 rows), with 0's and duplicates, which i dont want. the actual list will be around a 100 rows long. But with this formula, it takes forever to copy down the formula. do you have any explanation as to why that is?

[@alfianmm]

lifesaver... thanks!

[@shumed38]

At 3:25, you say that we want -1 (greater than) but shouldn't it be 1 (less than)? That is the default I thought and also yields the right result. If you put a -1, it gives an error...please clarify!

[@robertanderssen]

Would it work to put that formula into a name so that you can have a dynamic dropdown list?

[@sgaddu]

You are brilliant

[@YourXLNerd]

cunning! thank you!

[@emailuznow]

Hi Mike, This sort is from smallest to largest How can you tweak the formula to show largest to smallest? Thanks

[@xim76]

I'm using this trick in conjunction with Excel Magic Trick 703: Extract Records Multiple Criteria and the one issue I'm facing is getting the dynamic name range to exclude cells populated with a formula that are producing a "blank" value. Is there a formula that I can use to get the dynamic range while exlcuding "blank" value cells?

[@krn14242]

Wow, too cool.

[@Alex.becker28]

Will this work for a column in a table in leiu of a named range?

[@colinhuntley9729]

good morning, mike your ideas on how to solve this scenario would be apprectiated: there 3 colums of raw data: col.a) english, col.b) german, col. c)company. these are expanding columns and data is entered unsorted in the columns. there are also many duplicates, which are needed. for example: german english company selbstbewusst - self-assured - comp. d neugierig - curious - comp a überzeugend - convincing - comp. f kraftvoll - energetic - comp. c kreativ - creative - comp.h these colums need to be sorted, 1) in german with the english translation and 2) english with german translation, for example: (german english) kraftvoll - energetic kreativ - creative neugierig - curious selbstbewusst -self-assured überzeugend - convincing and (english german) convincing - überzeugend creative - kreativ curious - neugierig energetic - kraftvoll self-assured - selbstbewusst as i said, the colums are very long and expand constantly. also there are many duplictes which i do not need. your video was easy to follow and i got one column sorted, without duplicates, but after of trying different formulas i am really stuck on the column b, which should not sort alphabeticaly, but the contents should be the translation of column a. please excuse the formatting. if it would held i could send you an excel file. many many thanks in advance. colin huntley

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

[@edlinton]

Matt, I need to do the same thing. Have you found a way to do this?

[@naveensharma3066]

There is a attendance sheet of employees in excel. Find first leave date and last leave date of employees using formula.

[@jamesdial3977]

I ran this formula and its working nicely and ive changed it to sort a table based on numerical values and its sorting them in ASCii For Instance my list of 9 12 18 15 is sorting as 12, 15, 18, 9 any help on a fix for that

Hi! Is there a limit of length of the array ($A$2:$A$13 in this example) that limits usage of this kind of formula? I have a table with 1000+ rows and I have defined my array to $A$2:$A$1236, but if I try to check the formula of =$A$2:INDEX($A$2:$A$1236;MATCH(REPT("z";255);$A$2:$A$1236;1)) with F9 key, it shows me only first 30 rows as result and if I use this later to define a name, the final formula =INDEX(mm; MATCH(0; COUNTIF(mm;"

[@mohamedelgendy2913]

Dear sir, I have a real dilemma here and I really need your help. - Suppose I have car brands, and each brand has some models. - I made a range of brands named "BRAND" contains brands like AUDI and FORD, and I made ranges of models each is named by a specific brand (range name "AUDI" contains AUDI models, range name "FORD" contains FORD models.. etc). - In another cell, I made a list of brands using (Data Validation), and in the cell beside I made a list of models depending on the selected brand in the first cell using (Indirect) function in the (Data Validation) to refer to the chosen brand. - Till now everything work fine, but if I try to make the range names of each brand dynamic, I find the model list (which I use indirect in it's Data Validation) doesn't show any thing, although if I replaced the (Indirect) with the range name directly it displays the list, but I don't know what's the problem when I use the (Indirect) with a dynamic range name. I'm very sorry for making myself long, but if the comments accepts photos I could have been more briefed. Thanks in advance.

[@excelisfun]

=REPT("You Are Welcome! ",100)

[@Ben-hl5mu]

Great reply 😂😁.

[@liudy9k]

i was thinking of a formula.... that doesn't traverse (goes) through all 100.000 registers 300 times, as i think it does right now, with the formula you show in the video. Maybe it is possible to reduce somehow the number of calculations? Thank you!