For example, for 123 it returns 2 (1 is the rank of 123 in the data, and there is 1 blank cell) If the cell is a numerical value, it will return the comparative rank and add the total number of blanks.In a nutshell, it will return the total number of blank cells in the data If the cell is blank, it returns the value in cell B2 (which would always be 0) and adds the value in cell D10.The idea for this formula is to segregate blanks, numbers and text values. Text and numbers are processed parallelly and this formula returns the same number for text and number (for example 123 and India both get 1).Įnter the following IS Function in Helper Column 2: =-ISNUMBER(A2)Įnter the following formula in Helper Column 3: =-ISBLANK(A2)Įnter the following formula in Helper Column 4 =IF(ISNUMBER(A2),B2,IF(ISBLANK(A2),B2,B2+$C$10))+$D$10.In the case of duplicates, it returns the same number.So I will use helper columns to address each of these issues.Įnter the following COUNTIF formula in Helper Column 1 =COUNTIF($A$2:$A$9,"<="&A2) You can see there are duplicate values, blank and numbers. I will use 4 helper columns to show you how it works (and then give you a huge formula that will do it without the helper columns). When Data is a Mix of Numbers, Text, Duplicates, & Blanks You have a mix of numbers and text (try putting 123 in one of the cells).You have duplicates in the data (try putting US twice).This formula works well if you have text or alphanumeric values. This is an array formula, so use Control + Shift + Enter instead of Enter. Here is a formula that will do the same without the helper column. In the second cell, it returns Canada (which has the number 2) and so on. In the first cell (C2), it looks for the country name that has the lowest number (Australia has 1). This formula simply extracts the names in the alphabetical order. Now to sort the values, use the following combination of INDEX, MATCH and ROWS functions: =INDEX($A$2:$A$9,MATCH(ROWS($B$2:B2),$B$2:$B$9,0)) For example, in cell B2, it returns 8, as there are 8 text values that are lower than or equal to the text ‘US’ (alphabetical order). This formula compares a text value with all the other text values and returns its relative rank. In the column next to the data, use the following COUNTIF formula: =COUNTIF($A$2:$A$9,"<="&A2) To sort this, I will use a helper column. In this example, all the data is in text format (no numbers, blanks or duplicates). When the Data is all Text with No Duplicates This means you can add data, and it will automatically sort it for you. In this post, I will show you various ways to sort data in alphabetical order using formulas. One Formula to Sort it All (without Helper Columns).When Data is a Mix of Numbers, Text, Duplicates, & Blanks.When the Data is all Text with No Duplicates.
0 kommentar(er)
