I was wondering what the best formula is to sort multiple ranges of data, and then sort alphabetical.
In my example my formula would be in H2 and I'd like it to take all the data from A2:F5, A7:511, A13:F15. Basically excluding row 1, 6 & 12 because they are labels. Then ideally once in H2 I'd like it to sort alphabetically.
I'd appreciate suggestions. I'd be using this is in both Excel & Google Sheets.
Screen Shot 2018-09-12 at 7.06.33 PM.png
Next time please don't leave us with an area the size of a postage stamp to work with!!!
This is going to be messy with a single formula...
1. Remove Group from A2 (on the attached sheet). Headers are now ALL numbers. Names are all text
2. Formula to return unique list, in H2:
=IFERROR(INDIRECT(TEXT(AGGREGATE(15,6,(ROW($A$3:$F$17)*100+COLUMN($A:$F))/(($A$3:$F$17<>"")*(ISTEXT($A$3:$F$17))*(COUNTIF($H$1:H1,$A$3:$F$17)=0)),1),"R0C00"),FALSE),"")
3. ARRAY Formula to sort uniques, in I2:
=INDEX($H$2:$H$12,MATCH(SMALL(COUNTIF($H$2:$H$12,"<="&$H$2:$H$12),ROWS($I$1:I1)),COUNTIF($H$2:$H$12,"<="&$H$2:$H$12),0))
Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.
You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.
Don't type the curly brackets yourself - it won't work...
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Oh my, I'm so sorry about the sizing / Rows I hide. That was just a force of habit.
See how in the test data there is two instances of "Person O" but on the sort only 1. Just wondering if it's possible to list both in the sort?
Thanks for the help
Firstly remove all group numbers.
For unique in "H2"Formula:
Please Login or Register to view this content.
For Sorting 'I2"Formula:Please Login or Register to view this content.
CTRL+SHIFT+ENTER
See attachment.
atul
If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.
Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".
I decided to try a different approach in Test1 sheet, seeing which will work best. D2 would grab all the names from A2:C46 and sort alphabetical and if could list each, even if double (Not sure if that's even possible)
Beh162 : I have attach file, go through as per post #4
Hi, Yes that works wonderful. My only problem is the double "people" I was hoping when they appear twice, they'd also list twice.Originally Posted by avk
Most unusual!! 99 times out of 100... a unique list is desired.
Amend 1st formula to:
=IFERROR(INDIRECT(TEXT(AGGREGATE(15,6,(ROW($A$3:$F$19)*100+COLUMN($A$3:$F$19))/(ISTEXT($A$3:$F$19))*($A$3:$F$19<>0),ROWS(I$30:I30)),"R0C00"),FALSE),"")
It is most unique.
Can I ask you to just explain to me two things. The meaning of the 15,6 in the formula and also the "R0C00" and the significance of box I30.
I'm trying to recreate the formula in a bigger test environment and would like to learn a few of those things so I understand where to adjust formula.
Last edited by Beh162; 09-14-2018 at 07:12 AM.
Apols.... Today has been a day on the road, so only getting to this now.
Not the easiest to explain...
Ultimately the formula aims to produce a "number" - being a combintation of the row number (R) and the columns number (C), of every cell that meets two criteria.
1. (ISTEXT($A$3:$F$19)) anything that is text (so excluding the group numbers)
2. ($A$3:$F$19<>0) - or perhaps easier to follow ($A$3:$F$19<>"") anything that is non-blank.
So, that selects ONLY those cells that contain TEXT names. The row number is x100 and to that the column number is added.
So cell A3 (which is Person A), at Row 3, column A (ie the first column.... column 1, if you like) returns 3x100 +1 = 301. Likewise for all the cells that are nonblanks and which contain text (it has just occurred to me that the second parameter ($A$3:$F$19<>"") might be omitted... I will check).
AGGREGATE(15,6....
returns them (15) smallest to largest, ignoring errors (6) one at a time, smallest first (I$30:I30) - which is simply a counter resolving to 1, 2, 3, as it is copied down.
The R0C00 business simply tells excel that you are referring to the cell address in RC notation.
Clear as mud??
... and yes, the second criterio IS redundant.
This works perfectly well, too:
=IFERROR(INDIRECT(TEXT(AGGREGATE(15,6,(ROW($A$3:$F$19)*100+COLUMN($A$3:$F$19))/(ISTEXT($A$3:$F$19)),ROWS(I$30:I30)),"R0C00"),FALSE),"")
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks