Today we are going to discuss about the method to get a sorted unique list from the given list with duplicates. This method is most wild and classic but effective. Works in all versions of excel. There are dynamic array formula's which can do that with ease, using unique and sort functions, but here we are not going to discuss about that.
First we will take a look at our requirement and desired result.
Sorting of texts, done by the same logic of number sorting. Sort order Ascending in numbers is same as sort order A-Z in text. If the first letter is same for 2 texts, then it will compare 2nd letter and so on.
If you want to sort the texts using excel formula's, we can see 2 common methods using Countif function and Comparative operator method. Today we are going to discuss about 2nd method by comparative operator.
Take a look at the formula in D3 cell,
=INDEX($B$3:$B$19,INDEX(MODE.MULT(IFERROR(MATCH(ROW($B$3:$B$19)-ROW($B$3)+1,MMULT(--($B$3:$B$19>=TRANSPOSE($B$3:$B$19)),(ROW($B$3:$B$19)-ROW($B$3)+1)^0),{0,0}),"")),ROWS($D$3:D3)))
Let me deconstruct that into digestive blocks,
The core part of this formula lies in the Array 1 argument of MMULT function (Matrix multiplication).
Ignore the double negative for now, we will discuss that later, We simply use the comparative operator 'greater than or Equal to' in between the range B3:B19 and the transposed range of the same. The output of this construction will look like below.
Let me take the name Mike Girvin from B9 cell and compare it with C2 cell name which is Sumit Bansal. So the question will be like, Is Mike Girvin is greater than or equal to Sumit Bansal? the answer is big NO, that's why the result in C9 cell says FALSE.
Let me take the same name Mike Girvin from B9 cell and compare it with D2 cell name which is Leila Gharani. Is Mike Girvin is greater than or equal to Leila Gharani? Yes for damn sure and so the result in D9 cell is TRUE.
We have to convert the TRUE and FALSE into 1 and 0, For that, we can do any mathematical operations such as adding 0, multiply with 1 or divide by 1. But here we used double negative, which is considered to be the fastest method. If we multiply the TRUE with -, then there is a mathematical operation and the result is -1, if we multiply that again with -, then the result will become 1. So without changing the value, we did mathematical operation in faster way.
In the resulting array C3:S19, we should get the sum of each row to find the sort order position. We can get help from MMULT function to get the sum of each row in a single cell formula.
=MMULT(array1,array2)
In our case, we can feed the resulting array from C3:S19, after doing multiplication with double negative, into array 1 argument in MMULT function. Size of the array in array 1 argument is 17*17, that is 17 rows and 17 columns, If we create an array with size 17*1, that is 17 rows and 1 column, we can use that as array 2 argument in MMULT function.
So, let's start create the 2nd array with size 17*1,
The ROW function will deliver the Row number of given reference, If we feed B3 cell as the reference in ROW function, it will deliver 3. If we feed B3:B19 as the reference it will deliver the row numbers of that range, So the output will look like below.
If we detect the row of B3 which is 3, then the sequence will start from 0 and so we are adding 1 to it. This will create the array with size 17*1, then we add power of zero to this array. Any number with power zero is one, and so the final output of array 2 will look like below.
When we do Matrix multiplication between these two array, the output will look like below,
Larger numbers are the last item in sorting order and the smaller numbers are the first item in sorting order. To get the relative position of each items, we have to match the sequence of numbers from 1 to 17 with the resulting array. So we can use MATCH function to get the relative position as shown below.
We can use the same construction with row function to create the sequence of number from 1 to 17 and feed that into lookup_value argument in match function.
Feed the resulting array from MMULT function into look_up array argument.
Match type is the exact match and so we used 0. Output of match function is shown below.
Do not bother about error values, we have a work around to exclude the error values.
First number in the resulting array is 4, check the relative position of 4 in the range B3:B19.
Which is Alan Murray. Then, 2nd number is 13, if we check the relative position of 13 in the range B3:B19, it is Bob Umlas. Now at least we know that we are in the right track.
This is the error removing part, please pay attention on this,
In the MATCH function, 3rd argument is [match_type], here we fed 0 for exact match, instead of that we can feed {0,0} in that argument. That means, we are forcing the match function to provide the result in 2 columns, so output will look like below.
Wrap the entire match function with IFERROR function to get rid of error values,
Wrap the entire iferror function inside MODE.MULT function, this function will pick the number with most number of occurrence in the given range. In our case, each numbers occurs twice and so it will deliver all numbers. This function is programmed to ignore text values, so that zero length text string ("") is getting omitted here.
Then it's a piece of cake now, get 1st number from this resulting array which is 4 and use this as the relative position in B3:B19 range to pick Alan Murray. Get 2nd number in this resulting array which is 13 and use this as the relative position in B3:B19 and pick Bob Umlas.
Comments