Sorting text cells using array formula in excel Table of Contents I am inspired once again by the article Sorting Text in Excel using Formulas at Pointy haired Dilbert. In Chandoo´s article he sorts text with a "helper" column. My goal with this article is to show you how to sort text cells alphabetically without a helper column. Here is a example list containing text values in column A. Here is how to automatically sort text cells without any user interaction in cell B2: Array formula in cell B2: =INDEX(List, MATCH(SMALL(COUNTIF(List, "<"&List), ROW(1:1)), COUNTIF(List, "<"&List), 0)) How to create an array formula Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar.Press and hold Ctrl + Shift.Press Enter once.Release all keys. Copy cell B2 and paste it down as far as needed. Explaining array formula in cell B2 Step 1 - Count "smaller" values COUNTIF(range,criteria) counts the number of cells within a range that meet the given condition =INDEX(List, MATCH(SMALL(COUNTIF(List, "<"&List), ROW(1:1)), COUNTIF(List, "<"&List), 0))
Arthapedia Eileen's Lounge • View topic - Can this be done Here is a new version, with a new syntax. Code: Select all Function ConcatenateIfs(ConcatenateRange As Range, ParamArray Criteria() As Variant) As Variant ' Source: EileensLounge.com, August 2014 Dim i As Long Dim c As Long Dim n As Long Dim f As Boolean Dim Separator As String Dim strResult As String On Error GoTo ErrHandler n = UBound(Criteria) If n < 3 Then ' Too few arguments GoTo ErrHandler End If If n Mod 3 = 0 Then ' Separator specified explicitly Separator = Criteria(n) Else ' Use default separator Separator = "," End If ' Loop through the cells of the concatenate range For i = 1 To ConcatenateRange.Count ' Start by assuming that we have a match f = True ' Loop through the conditions For c = 0 To n - 1 Step 3 ' Does cell in criteria range match the condition? Syntax: =ConcatenateIfs(ConcatenateRange, CriteriaRange1, Operator1, Condition1, CriteriaRange2, Operator2, Condition2, ..., Separator) Example: =ConcatenateIfs(A1:A6, B1:B6, "<", 2, C1:C6, "=", "b") or In your workbook:
Famous Biographies & TV Shows - Biography.com Singer, Music Producer, Songwriter (1961–) Roland Orzabal is an English musician, songwriter and record producer best known as co-founder with Curt Smith of the popular '80s pop band Tears for Fears. Talk Show Host, Football Player, Athlete (1971–) Michael Strahan is a former defensive end for the New York Giants, and co-host of the morning television program Live! Film Actor, Director (1963–) Johnny Depp is an award-nominated actor known for his portrayal of odd-ball characters including Captain Jack Sparrow in the Pirates of the Caribbean. Game Show Host, Reality Television Star, Film Actor/Film Actress (1972–) Jenny McCarthy was named Playboy's 1994 Playmate of the Year and hosted MTV's 'Singled Out' from 1995 to 1997. Film Actor, Athlete (1972–) Dwayne Johnson became a film actor (2001's The Mummy Returns) after gaining fame with the World Wrestling Federation as "The Rock." Film Actor, Theater Actor, Television Actor, Comedian, Philanthropist (1926–) Lawyer, U.S. Chester A.
VirtualSalt