SMALL(array, k)

Returns the k-th smallest value in a data set.

LARGE(array, k)

Returns the k-th largest value in a data set.

Arguments

array - Is a reference to a cell range or is an array of constants.

k - The position in the array of the value to return.

Example 1 - Cell range contains numbers, text and blanks

becomes

=SMALL({3; 6; "A"; 5; "X"; 0; 4; 4},3)

Text strings and blanks are overlooked. The array becomes

=SMALL({3; 6; ; 5; ; ; 4; 4},3)

and returns 4. 4 is the third smallest numerical value in the array.

Example 2 - Use a condition (Array formula)

The following array formula looks for the second smallest value in column C if corresponding value in adjacent column D is 2012-Dec:

becomes

=SMALL(IF({"2013-Jan"; "2012-Dec"; "2013-Jan"; "2012-Dec"; "2012-Nov"; "2013-Jan"; "2013-Jan"; "2012-Dec"; "2013-Jan"; "2012-Nov"; "2012-Dec"}="2012-Dec", C4:C14, ""), F4)

becomes

=SMALL(IF({FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE}, C4:C14, ""), F4)

becomes

=SMALL({"";60.69;"";8.08;"";"";"";24.44;"";"";2.25}, 2)

and returns 8.08 in cell F7.

Download excel *.xlsx file

SMALL and LARGE function.xlsx

The code below is tested in Excel 2010, Windows 7.

Copy a file

Sub CopyFile()
Dim src As String, dst As String, fl As String
'Source directory
src = Range("B3")
'Destination directory
dst = Range("D3")
'File name
fl = Range("B6")
On Error Resume Next
    FileCopy src & "\" & fl, dst & "\" & fl
    If Err.Number <> 0 Then
        MsgBox "Copy error: " & src & "\" & fl
    End If
On Error GoTo 0
End Sub

Copy and rename a file

Sub CopyRenameFile()
Dim src As String, dst As String, fl As String
Dim rfl As String
'Source directory
src = Range("B3")
'Destination directory
dst = Range("D3")
'File name
fl = Range("B6")
'Rename file
rfl = Range("D6")
On Error Resume Next
    FileCopy src & "\" & fl, dst & "\" & rfl
    If Err.Number <> 0 Then
        MsgBox "Copy error: " & src & "\" & rfl
    End If
On Error GoTo 0
End Sub

Rename a file

Sub RenameFile()
Dim src As String, dst As String, fl As String
Dim rfl As String
'Folder
src = Range("B3")
'File name
fl = Range("B6")
'Rename file
rfl = Range("D6")
On Error Resume Next
    Name src & "\" & fl As src & "\" & rfl
    If Err.Number <> 0 Then
        MsgBox "Error: " & src & "\" & rfl
    End If
On Error GoTo 0
End Sub

List files in a folder

Sub ListFilesinFolder()
Dim Value As String
Dim strt As Range
Set strt = Range("B6")
Value = Dir(Range("B3"), &H1F)
Do Until Value = ""
If Value <> "." And Value <> ".." Then
    strt = Value
    Set strt = strt.Offset(1, 0)
End If
Value = Dir
Loop
End Sub

List files in a folders and sub-folders

List files in a folder and subfolders

Download excel *.xlsm file

Copy rename files (excel vba).xlsm

Vijay asks: 

I've 30 objects and 10 people, and I need to assign each person with randomly (unique) selected objects as a daily activity. Would you please suggest me on how I can do it using excel formulae?

Answer:

The objects are unique within that day. It means that a person can get the same object next day if unlucky. If this is not what you are looking for, let me know.

The array formula in cell C3:C32 returns a random unique value every time you press F9. The objects are in column F.  The table does not need to be sorted by date.

Array formula in cell C3:

How to enter an array formula

1. Select cell C3
2. Copy/Paste above array formula to formula bar
3. Press and hold CTRL + SHIFT
4. Press ENTER

Explaining array formula in cell C3

Step 1 - Calculate possible objects

COUNTIFS($C$2:C2, $F$3:$F$32, $A$2:A2,A3)=0

becomes

{0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0}=0

and returns

{TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}

All objects can be randomly selected. If there had been previous object that day they would have been ruled out.

Step 2 - Calculate row numbers

(COUNTIFS($C$2:C2, $F$3:$F$32, $A$2:A2,A3)=0)*MATCH(ROW($F$3:$F$32), ROW($F$3:$F$32))

becomes

{TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}*{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}

and returns {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}

Step 3 - Return a random number

RANDBETWEEN(1, 31-COUNTIF($A$2:A3, A3))

becomes

RANDBETWEEN(1, 31-1)

becomes

RANDBETWEEN(1, 30)

Step 4 - Return the k-th largest number

LARGE(array, k)

LARGE((COUNTIFS($C$2:C2, $F$3:$F$32, $A$2:A2,A3)=0)*MATCH(ROW($F$3:$F$32), ROW($F$3:$F$32)), RANDBETWEEN(1, 31-COUNTIF($A$2:A3, A3)))

becomes

LARGE({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}, RANDBETWEEN(1, 30))

and returns a random row number between 1 and 30.

Step 5 - Return a value

=INDEX($F$3:$F$32, LARGE((COUNTIFS($C$2:C2, $F$3:$F$32, $A$2:A2,A3)=0)*MATCH(ROW($F$3:$F$32), ROW($F$3:$F$32)), RANDBETWEEN(1, 31-COUNTIF($A$2:A3, A3))))

becomes

=INDEX($F$3:$F$32, random_row_number)

becomes

=INDEX({"A"; "B"; "C"; "D"; "E"; "F"; "G"; "H"; "I"; "J"; "K"; "L"; "M"; "N"; "O"; "P"; "Q"; "R"; "S"; "T"; "U"; "V"; "X"; "Y"; "Z"; "AA"; "BB"; "CC"; "DD"; "EE"}, random_row_number)

and returns a random object.

Download excel *.xlsx file

Assign each person with randomly (unique) selected objects as a daily activity.xlsx

ROW(reference)
Returns the row number of a reference.

You can enter a reference to a single cell or a cell range. The ROW function returns an array of numbers if you enter a reference to a cell range. Remember to enter the formula as an array formula.

The reference argument is optional. Enter =ROW() in a cell and a reference to the cell itself is used. Example, =ROW() in cell F4 returns 4.

The first example demonstrates basic usage. Example 2 and 3 are more advanced and requires some knowledge about MATCH and INDEX function.

Example 1

A cell reference can be relative or absolute or a combination of relative and absolute.

Absolute and relative references in excel

In cell B2: =ROW(B2) returns 2.

In cell B6: =ROW() returns 6

In cell B11: =ROW($D$10) returns 10 (absolute cell reference).

Example 2 - Duplicate columns in an array

In cell range B3:B5:

returns {3; 4; 5}

Now you might wonder why the array formula doesn´t return {3, 3; 4, 4; 5, 5}. There are six cells in the cell range, how come only three values are returned?

The answer is that there is no need for multiple duplicate columns in the array. Excel simplifies the array down to a single column. But when used with multiple cell ranges in more complicated array formulas, make sure the number of rows match. See this example: Unique distinct values from a cell range

Example 3 - Row numbering a cell range

The INDEX function returns a value of the cell at the intersection of a particular row and column, in a given range. To be able to work with multiple values from an arbitrary cell range using the INDEX function we must number each row. That is exactly what we did in the previous example but the first row in a given range has to be 1 and the second 2 and so on. Why? The INDEX function needs properly numbered cell ranges.

This is where the ROW and MATCH function comes in.

The beauty with this formula is that it row numbers any cell range with any size.

In this example, the cell range is C3:D5:

becomes =MATCH({3; 4; 5}, {3; 4; 5}) and returns {1; 2; 3}.

Remember, enter the formula as an array formula.

So what can you do with row numbered cell ranges?

• How to return multiple values using vlookup
  
• Match two criteria and return multiple rows in excel

The MATCH function can also be used to find the relative position of a value in a cell range. See these posts:

• How to extract unique distinct values from a column
  
• Repeat values
• Fetch data from another table
• Shift Schedule

Download example file:

ROW function.xlsx

You can highlight duplicates in an excel defined table using conditional formatting.

However, that won´t work if you only want to find duplicates among filtered visible values. Example, the table below has filtered values from January 2012.

Create a new conditional formatting rule

1. Select table column "Description"
2. Go to "Home" tab (Ribbon)
3. Click "Conditional formatting" button
4. Click "New Rule.."
5. Click "Use a formula to determine which cells to format"
   
6. Copy and paste this conditional formatting formula:
   =SUM(COUNTIF(INDIRECT("Table2[@Description]"), IF(SUBTOTAL(3, OFFSET(INDIRECT("Table2[Description]"), MATCH(ROW(INDIRECT("Table2[Description]")), ROW(INDIRECT("Table2[Description]")))-1, 0, 1)), INDIRECT("Table2[Description]"), "")))>1
7. Click "Format..." button
8. Go to Fill tab
9. Pick a color
10. Click OK
11. Click OK

Download excel *.xslx file

Highlight duplicates in a filtered table.xlsx