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..... :-)
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.
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.
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!
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.
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.
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.
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
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)
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.
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??
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!
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
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.
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.
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!
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)
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.
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
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.
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.
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.
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
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?
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!
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
@@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..
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)".
@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.
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)
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?
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.
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.
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
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 UA-cam. 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.
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?
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
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
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!
Hi. Thank you very much! Great Formula!! I have the Problem that this just seems to work with Arrays that are smaller than 260. My Array is about 4000 Names big. Is there a solution?
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 UA-cam 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.
hi sir how are you i need some help for my problem . i used this formula but it gives either numberical values or alpha numberical . i need result both types of values in result coloumn.
7 років тому
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;"
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!
I think I found a better way to set the named ranged. =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1) The $A:$A is a new one to me, but it will take the range of the whole column A into consideration.
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.
What if the data is in horizontal format? Example: Jo, Joe, Sioux, Chin all listed horizontally. I found the formula given works only for vertical table arrays. Any suggestions? Thanks.
Try this video: Ctrl + Shift + Enter: Excel Array Formulas 16: Formulas To Extract Records With Criteria 23 examples BE SURE to click on the "Show more" button the video and look through the time hyperlink table of content for the "Display extracted records vertically using ROWS function or horizontally using COLUMNS function" It is at the 33:37 minute mark. Also, look through the table of contents for other sections that might apply. The video is THE most comprehensive video on this topic.
+Polo Man Imagine Your RANGE Is In A1:Z1, Horizontally. Just Use TRANSPOSE(A1:Z1) Instead Of A1:Z1. TRANSPOSE Function Converts A Range From HORIZONTAL To VERTICAL, In The Formula Itself.
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
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.
Glad it is amazing for you, nitesh!!! Thanks for your support on these videos that you learn from with your comments, thumbs up, and of course your Sub. It means a lot to me and helps grow the channel so that more people can find this free education : )
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..... :-)
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.
I am glad to help! When I went to that Mr Excel link it was a tread from the year 2003...
I am glad that you like the video!
I am glad that they help and you can find what you want!
Nice to meet you, baleshst !!
Hi Mike, I have ordered your two books today ,,, I m your big fan.
I am glad that you liked it!
If you are doing it only once, Advanced Filter, Sort is great - I do this all the time.
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.
it is 2021 and still so amazing training!!
I am glad that you like it!
Happy New Year!
I am glad that you like it!!!
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!
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.
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.
You got it! Switch less than to greater than.
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.
@incantar , cool! I am glad that there were 20 cool Excel bits in this video!
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.
That is exactly what I was looking for! Thank you very much!
You are welcome!
Beautiful, TheSandywarrior !! Quite Nice!
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
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)
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.
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??
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!
I couldn't agree more!!
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
This video should do it:
Mr Excel & excelisfun Trick 10: Turn Column / Row Upside Dow
I am glad that the vids help!
Whoa....My brain hurts...lol. I am so glad the new functions make this situation so much easier!!! Yay Microsoft!!! Good job Mike...
You are right: soooooo much easier. But we did this old way for almost 40 years....
You are welcome!
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.
Pure Magic. A great way to start 2011
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.
ExcelIsFun Best Ever!!!
Oh, the IFERROR does not work in 2003, But I provided the 2003 formula in the downloadable workbook on the answer sheet.
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!
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)
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.
Formula would be:
=INDEX($A$1:$A$1500,ROWS(A1:A$1500))
in cell B1 and copy down.
Congratulations amazing method, it's very brilliant!
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
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.
Do you have a link to this book or do you have a video with this information?
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.
Thank you so much Mike. You are the best :-)
Thank you so much for this wonderful video! You saved my day!
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.
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
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?
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!
You are great! Thank you for this tutorial!
Oh well, the comments section of UA-cam won't let me post the formula at all because there are characters that are not allowed.
i can't build it myself
but i will copy it in my sheets :)
thanks every time for every video :)
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
You Know This is Blow My Mind, Thanks Bruh
Glad it is good, Hubert Lie!! Thanks for the support : )
@@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..
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)".
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
It does work in 2003.
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?
Matt, I need to do the same thing. Have you found a way to do this?
@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.
any advantage to doing it this way as opposed to adv. filter unique records?
Cool!
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)
Wow, very helpful. Thank you. Can you address sorting and removing duplicates in a list (Dynamic Named Range) that has mixed values (numbers & words).
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?
=REPT("You Are Welcome! ",100)
Great reply 😂😁.
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.
Hi Mike,
This sort is from smallest to largest
How can you tweak the formula to show largest to smallest?
Thanks
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.
I would switch to Access.
Fantastic. Thanks a ton.
You are welcome a ton!!!
Wspaniała formuła = Great formula.
It is super!!!!! Thank you. :))
Would it work to put that formula into a name so that you can have a dynamic dropdown list?
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
Brilliant!
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 UA-cam. 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.
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?
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
You are brilliant
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
Will this work for a column in a table in leiu of a named range?
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!
Comments will not allow it. Downlaod the workbook. The link is in the workbook and then you can just click it.
Hi. Thank you very much! Great Formula!! I have the Problem that this just seems to work with Arrays that are smaller than 260. My Array is about 4000 Names big. Is there a solution?
mmenzz try replacing the 250 at "nn" formula
Andres Perez Zepeda i tried this already, but this just works till 260
Yes
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 UA-cam 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.
lifesaver... thanks!
AMAZING!
hi sir how are you i need some help for my problem . i used this formula but it gives either numberical values or alpha numberical . i need result both types of values in result coloumn.
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;"
Wow, too cool.
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!
I think I found a better way to set the named ranged. =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)
The $A:$A is a new one to me, but it will take the range of the whole column A into consideration.
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.
What if the data is in horizontal format? Example: Jo, Joe, Sioux, Chin all listed horizontally. I found the formula given works only for vertical table arrays.
Any suggestions? Thanks.
Try this video:
Ctrl + Shift + Enter: Excel Array Formulas 16: Formulas To Extract Records With Criteria 23 examples
BE SURE to click on the "Show more" button the video and look through the time hyperlink table of content for the "Display extracted records vertically using ROWS function or horizontally using COLUMNS function" It is at the 33:37 minute mark. Also, look through the table of contents for other sections that might apply. The video is THE most comprehensive video on this topic.
+Polo Man Imagine Your RANGE Is In A1:Z1, Horizontally. Just Use TRANSPOSE(A1:Z1) Instead Of A1:Z1. TRANSPOSE Function Converts A Range From HORIZONTAL To VERTICAL, In The Formula Itself.
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
cunning! thank you!
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.
Amazing amazing
Glad it is amazing for you, nitesh!!! Thanks for your support on these videos that you learn from with your comments, thumbs up, and of course your Sub. It means a lot to me and helps grow the channel so that more people can find this free education : )