Sara asks:

How can you use large with multiple criteria?? Example looking for top 5 of a list based on name, state and city..

Answer:

Array formula in cell E3:

Formula in cell H3:

How to create an array formula

1. Select cell E3
2. Paste array formula
3. Press and hold Ctrl + Shift
4. Press Enter

How to copy array formula

1.  Select cell E3
2. Copy cell (not formula)
3. Select cell range F3:G3
4. Paste
5. Select cell range E3:G3
6. Copy cells (not formulas)
7. Select cell range E4:G9
8. Paste

Explaining array formula in cell E3

See post: Create a unique distinct list and sort by occurrances from large to small

Download example *.xlsx file

Unique distinct records sorted by frequency.xlsx

Joe asks:

I have a worksheet that has rows, each containing sequential groupings of values of "1" and "0" .These alternate across 1800 colunms of data.

My question: how do I count the number of groupings of each? In other words, across those 1800 columns, how many arrays of value 1 and how many arrays of value 0 do I have?
Thanks.
Joe

Answer:

I am only using six columns, in this example.

I replaced (Ctrl + H) all zeros in the range with value 2. Why? If I compare an empty cell ("") with 0 (zero) the formula returns TRUE. I want the formula to return FALSE.

Here is a picture of a cell range with random values. The first row has 1 group of 1´s and 1 group of 2´s. The second row has 2 groups of 1's and 3 groups of 2's and so on. I counted the groups in the range and there are 14 groups of 2's and 12 groups of 1's.

Lets create a formula!

Array formula in cell I2:

Array formula in cell J2:

How to create an array formula

1. Select cell I2
2. Paste formula
3. Press and hold Ctrl + Shift
4. Press Enter

Explaining array formula in cell I2

Step 1 - Compare cell range B1:G7 with A1:F7

B1:G7<>A1:F7

becomes

{1, 1, 1, 1, 2, 2;2, 1, 2, 2, 1, 2;2, 1, 2, 2, 2, 1;2, 1, 1, 2, 1, 1;2, 2, 2, 1, 2, 1;2, 1, 2, 2, 1, 2;1, 1, 1, 1, 1, 2} <> {0, 1, 1, 1, 1, 2;0, 2, 1, 2, 2, 1;0, 2, 1, 2, 2, 2;0, 2, 1, 1, 2, 1;0, 2, 2, 2, 1, 2;0, 2, 1, 2, 2, 1;0, 1, 1, 1, 1, 1}

and returns

{TRUE, FALSE, FALSE, FALSE, TRUE, FALSE;TRUE, TRUE, TRUE, FALSE, TRUE, TRUE;TRUE, TRUE, TRUE, FALSE, FALSE, TRUE;TRUE, TRUE, FALSE, TRUE, TRUE, FALSE;TRUE, FALSE, FALSE, TRUE, TRUE, TRUE;TRUE, TRUE, TRUE, FALSE, TRUE, TRUE;TRUE, FALSE, FALSE, FALSE, FALSE, TRUE}

Step 2 - Count only 1's

(B1:G7<>A1:F7)*(B1:G7=1)

becomes

{TRUE, FALSE, FALSE, FALSE, TRUE, FALSE;TRUE, TRUE, TRUE, FALSE, TRUE, TRUE;TRUE, TRUE, TRUE, FALSE, FALSE, TRUE;TRUE, TRUE, FALSE, TRUE, TRUE, FALSE;TRUE, FALSE, FALSE, TRUE, TRUE, TRUE;TRUE, TRUE, TRUE, FALSE, TRUE, TRUE;TRUE, FALSE, FALSE, FALSE, FALSE, TRUE}*(B1:G7=1)

becomes

{TRUE, FALSE, FALSE, FALSE, TRUE, FALSE;TRUE, TRUE, TRUE, FALSE, TRUE, TRUE;TRUE, TRUE, TRUE, FALSE, FALSE, TRUE;TRUE, TRUE, FALSE, TRUE, TRUE, FALSE;TRUE, FALSE, FALSE, TRUE, TRUE, TRUE;TRUE, TRUE, TRUE, FALSE, TRUE, TRUE;TRUE, FALSE, FALSE, FALSE, FALSE, TRUE}*{TRUE, TRUE, TRUE, TRUE, FALSE, FALSE;FALSE, TRUE, FALSE, FALSE, TRUE, FALSE;FALSE, TRUE, FALSE, FALSE, FALSE, TRUE;FALSE, TRUE, TRUE, FALSE, TRUE, TRUE;FALSE, FALSE, FALSE, TRUE, FALSE, TRUE;FALSE, TRUE, FALSE, FALSE, TRUE, FALSE;TRUE, TRUE, TRUE, TRUE, TRUE, FALSE}

and returns

{1, 0, 0, 0, 0, 0;0, 1, 0, 0, 1, 0;0, 1, 0, 0, 0, 1;0, 1, 0, 0, 1, 0;0, 0, 0, 1, 0, 1;0, 1, 0, 0, 1, 0;1, 0, 0, 0, 0, 0}

Step 3 - Sum values

SUM((B1:G7<>A1:F7)*(B1:G7=1))

becomes

SUM({1, 0, 0, 0, 0, 0;0, 1, 0, 0, 1, 0;0, 1, 0, 0, 0, 1;0, 1, 0, 0, 1, 0;0, 0, 0, 1, 0, 1;0, 1, 0, 0, 1, 0;1, 0, 0, 0, 0, 0})

and returns 12 in cell I2.

Download excel *.xlsx file

Count groups of a value.xlsx

Question: Is there a way to have a unique list generated from a list? Meaning I have a sheet that contains my data and I want to generate a drop down list based on the input from one column. Which I already have.

Once the user selects the value from the drop down, I want it to generate another unique list just in a column.

For example: the sheet contains store numbers (one column) and employee names (a second column), which may be listed multiple times. I want to create the drop down for the store number and then creating a list of employee names (non-repeating) that isnt a drop down, just a standard list is say A1,A2,A3,A4,etc

Is that clear as mud?

Thanks.

Answer:

This workbook contains:

• Array formula (Sheet: Data) sorts and filters unique distinct store numbers
• Named range: UniqueStoreNumbers =Data!$E$2:$E$27
• Drop down list (data validation) (Sheet: Sheet1)
• Custom function: Concat_Unique (Sheet: Sheet1)
• Sheets: Sheet1 and Data

Sheet: Data

Array formula in cell E3:

How to create an array formula

1. Select cell E3
2. Paste formula
3. Press and hold Ctrl + Shift
4. Press Enter

How to copy array formula

1. Select cell E3
2. Copy cell (not formula)
3. Select cell range E4:E29
4. Paste

Create a named range

1. Go to tab "Formulas"
2. Click "Name Manager" button
3. Click "New.."
4. Type UniqueStoreNumbers 
5. Select cell range =Data!$E$2:$E$27 in Refers to:
6. Click OK!

Sheet: Sheet1

Create drop down list

1. Select cell A2
2. Go to tab "Data"
3. Click "Data validation.." button
4. Go to tab "Settings"
5. Select List in Allow: field
6. Type in source: field: =UniqueStoreNumbers 
7. Click OK

Add vba code to module

1.  Copy vba code below
2. Press Alt+F11
3. Right click on workbook in project explorer
4. Select Insert and Module
5. Paste vba code to code module
6. Return to excel

Vba code

Function Concat_Unique(Lookup_Value As String, Lookup_Column As Range, Concat_column As Range)
Dim i As Single
Dim Unique As New Collection
Dim Value As Variant
Dim result As String
For i = 1 To Lookup_Column.Cells.Rows.Count
    If Lookup_Value = Lookup_Column.Cells(i).Value Then
        If Len(Concat_column.Cells(i)) > 0 Then
            On Error Resume Next
            Unique.Add Concat_column.Cells(i), CStr(Concat_column.Cells(i))
            On Error GoTo 0
        End If
    End If
Next i
For Each Value In Unique
    result = result & Value & ", "
Next Value
If Len(result) = 0 Then
    Concat_Unique = ""
Else
    Concat_Unique = Left(result, Len(result) - 2)
End If
End Function

Formula in cell B2:

Copy cell B2 and paste to cell range B3:B8.

Download excel *.xlsm file

Will.xlsm

Cell B3 contains a drop down list (Data Validation). The values in drop down list are from column H. I am using a dynamic named range.

Enter a value in cell E3 and press "Add" button to add the value to the list in column H. You can also remove a value using the "Remove" button.

See the animated gif below.

Create a named range

1. Go to tab "Formulas"
2. Click "Name Manager" button
3. Click "New.."
4. Enter List in Name field
5. Enter formula in Referes to: field:
   =Sheet1!$H$3:INDEX(Sheet1!$H$3:$H$1000, COUNTA(Sheet1!$H$3:$H$1000))
6. Click Close button

Explaining named range formula

Step 1 - Count the number of cells in a range that are not empty

 COUNTA(Sheet1!$H$3:$H$1000) returns 4.

Step 2 - Return a cell reference to  a single cell

INDEX(Sheet1!$H$3:$H$1000, COUNTA(Sheet1!$H$3:$H$1000))

becomes

INDEX(Sheet1!$H$3:$H$1000, 4)

and returns

$H$6

Step 3 - Create a cell reference to a cell range

=Sheet1!$H$3:INDEX(Sheet1!$H$3:$H$1000, COUNTA(Sheet1!$H$3:$H$1000))

returns =Sheet1!$H$3:$H$6

That is the cell reference to all the values in column H.

Populate drop down list

1.  Select cell B3
2. Go to tab "Data"
3. Click "Data Validation" button
4. Click "Data Validation..."
5. Select List in "Allow:" field
6. Type =List in source: field
7. Click OK.

Add vba code

1. Press Alt + F11
2. Right click on your workbook in the project explorer
3. Click "Insert" and then "Module"
4. Paste macros to code module

Sub AddValue()
Dim i As Single
i = Worksheets("Sheet1").Range("H" & Rows.Count).End(xlUp).Row + 1
With Worksheets("Sheet1")
.Range("H" & i) = .Range("E3")
.Range("E3") = ""
End With
End Sub

Sub RemoveValue()
Dim i As Single
Dim Cell As Range
i = Worksheets("Sheet1").Range("H" & Rows.Count).End(xlUp).Row
Set Cell = Worksheets("Sheet1").Range("H" & i)
Do Until Cell.Row = 2
    If Worksheets("Sheet1").Range("E3") = Cell And Cell <> "" Then
        Cell.Delete Shift:=xlUp
        Worksheets("Sheet1").Range("E3") = ""
        Exit Sub
    End If
    Set Cell = Cell.Offset(-1, 0)
Loop
MsgBox "Can´t find value: " & Worksheets("Sheet1").Range("E3")
End Sub

Assign macros to buttons

1. Go to tab "Developer"
2. Click "Insert" button
3. Select "Button" (form control)
4. Create a button
5. Enter button text: Add
6. Assign macro AddValue() to button

Create a remove button and assign RemoveValue() macro.

Download excel file

Add or remove a value in a drop down list.xlsm

NETWORKDAYS function returns the number of whole workdays between two dates. The array formula in this blog post counts for example Mondays or any weekday in a date range. Later in this blog post I will show you how to exclude holidays.

Array formula in cell D2:

How to create an array formula

1. Select cell D2
2. Paste array formula
3. Press and hold Ctrl + Shift
4. Press Enter

How to copy array formula

1. Copy cell D2
2. Select cell range E2:J2
3. Paste

Explaining formula in cell D2

Step 1 - Create dates in the date range

$B$1+(ROW($A$1:INDEX($A$1:$A$1000, $B$2-($B$1-1)))-1)

becomes

41000+(ROW($A$1:INDEX($A$1:$A$1000, 41029-(41000-1)))-1)

becomes

41000+(ROW($A$1:INDEX($A$1:$A$1000, 41029-40999))-1)

becomes

41000+(ROW($A$1:INDEX($A$1:$A$1000, 41029-40999))-1)

becomes

41000+(ROW($A$1:INDEX($A$1:$A$1000, 30))-1)

becomes

41000+(ROW($A$1:$A$30)-1)

becomes

41000+({1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30}-1)

becomes

41000+{0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29}

and returns

{41000, 41001, 41002, 41003, 41004, 41005, 41006, 41007, 41008, 41009, 41010, 41011, 41012, 41013, 41014, 41015, 41016, 41017, 41018, 41019, 41020, 41021, 41022, 41023, 41024, 41025, 41026, 41027, 41028, 41029}

Step 2 - Convert dates to days of the week

TEXT($B$1+(ROW($A$1:INDEX($A$1:$A$1000, $B$2-($B$1-1)))-1), "ddd")

becomes

TEXT({41000, 41001, 41002, 41003, 41004, 41005, 41006, 41007, 41008, 41009, 41010, 41011, 41012, 41013, 41014, 41015, 41016, 41017, 41018, 41019, 41020, 41021, 41022, 41023, 41024, 41025, 41026, 41027, 41028, 41029}, "ddd")

and returns

{"Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun", "Mon"}

Step 3 - Check if values in array are equal to the value in cell D1 (Mon)

IF(TEXT($B$1+(ROW($A$1:INDEX($A$1:$A$1000, $B$2-($B$1-1)))-1), "ddd")=D1, 1, 0)

becomes

IF({"Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun", "Mon"}, "ddd")="Mon", 1, 0)

and returns

{0, 1, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 1}

Step 4 - Sum values in array

SUMPRODUCT(IF(TEXT($B$1+(ROW($A$1:INDEX($A$1:$A$1000, $B$2-($B$1-1)))-1), "ddd")=D1, 1, 0))

becomes

SUMPRODUCT({0, 1, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 1})

and returns 5 in cell D2.

Exclude holidays

Array formula in cell D7:

How to create an array formula

1. Select cell D7
2. Paste array formula
3. Press and hold Ctrl + Shift
4. Press Enter

How to copy array formula

1. Copy cell D7
2. Select cell range E7:J7
3. Paste