Question: How do I create a random list of unique numbers from say 1 to 10, without using VBA (Generate unique random numbers) (Unique random numbers in VBA) or without enabling iterative calculation in excel options?

Answer:

Formula in cell A2:

=INDEX(SMALL(IF(COUNTIF($A$1:A1, ROW($1:$10))=0, ROW($1:$10), ""), ROW(INDIRECT("1:"&SUM(IF(COUNTIF($A$1:A1, ROW($1:$10))=0, 1, 0))))), ROUND(RAND()*SUM(IF(COUNTIF($A$1:A1, ROW($1:$10))=0, 1, 0)), 0), ) + CTRL + SHIFT + ENTER copied down to cell A14.

How to customize array formula to your excel work sheet
If your list starts at F3 change $A$1:A1 to $F$2:F2 in the above array formula. To change the numbers from 1 to 10 to, for example, 2 to 12, change $1:$10 to $2:$12 also in the above array formula.

Press F9 to generate a new random list of unique numbers.

Download excel sample file for this tutorial.
unique-random-list-of-numbers.xls
(Excel 97-2003 Workbook *.xlsx)

Functions in this article:

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

MATCH(lookup_value;lookup_array; [match_type]
Returns the relative position of an item in an array that matches a specified value

IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given condition

SUM(number1,[number2],)
Adds all the numbers in a range of cells

SMALL(array,k) returns the k-th smallest row number in this data set.

ROW(reference) returns the rownumber of a reference

INDIRECT(ref_text,[a1])
Returns the reference specified by a text string

This week Excel blogs have been posting June web statistics. Here are Get Digital Help June web statistics:

Here are the links to Excel blogs June web stats:

Contextures
Daily Dose of Excel
Pointy Haired Dilbert
PTS Blog
The Spreadsheet Page
Bacon Bits

Here is a list of the most visited blog posts in June:

1. Lookup with multiple criteria and display multiple search results using excel formula
2. How to extract a unique list in excel from a column
3. Count number of times a string exist in multiple cells using excel formula
4. Create a drop down list containing only unique distinct alphabetically sorted text values using excel array formula
5. Count records between two dates in excel

Question: How do I sort text values in two columns combined?

Answer:

Formula in D2:

=IF(ISERROR(MATCH(ROWS(D2:$D$2), COUNTIF(List1, "<"&List1)+1+COUNTIF(List2, "<"&List1), 0)), INDEX(List2, MATCH(ROWS(D2:$D$2), COUNTIF(List2, "<"&List2)+SUM(IF(COUNTIF(List2, "<"&List1)=0, 1, 0))+1, 0)), INDEX(List1, MATCH(ROWS(D2:$D$2), COUNTIF(List1, "<"&List1)+1+COUNTIF(List2, "<"&List1), 0))) + CTRL + SHIFT + ENTER copied down to D10.

The downside with this formula is that it does not work with duplicate entries.

Named ranges
List1 (A2:A5)
List2 (B2:B6)
What is named ranges?

How to implement array formula to your workbook
Change named ranges. If your list starts at, for example, F3. Change D2:$D$2 in the above formula to F2:$F$2.

Download excel sample file for this tutorial.
sort-from-a-to-z-from-two-columns.xls
(Excel 97-2003 Workbook *.xlsx)

Functions in this article:

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

ROWS(array) returns the number of rows in a reference or an array

MATCH(lookup_value;lookup_array; [match_type]
Returns the relative position of an item in an array that matches a specified value

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given condition

ISERROR(value)
Checks whether a value is an error and returns TRUE or FALSE

Question: How do I  merge two ranges into one list?

Answer:

Excel 2007 array formula in C2:

=IFERROR(INDEX(List1, ROWS(C1:$C$1)), IFERROR(INDEX(List2, ROWS(C1:$C$1)-ROWS(List1)), "")) + CTRL + SHIFT + ENTER copied down.

Named ranges
List1 (A2:A6)
List2 (B2:B3)
What is named ranges?

How to implement array formula to your workbook
Change named ranges. If your merged list starts at, for example, F3. Change C1:$C$1 in the above formula to F2:$F$2.

Download excel sample file for this tutorial.
merge-two-columns.xlsx
(Excel 2007 Workbook *.xlsx)

Functions in this article:

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

ROWS(array) returns the number of rows in a reference or an array

IFERROR(value;value_if_error) Returns value_if_error if expression is an error and the value of the expression itself otherwise

Question: How do I  merge three columns into one list?

Answer:

Excel 2007 array formula in D2:

=IFERROR(INDEX(List1, ROWS(D1:$D$1)), IFERROR(INDEX(List2, ROWS(D1:$D$1)-ROWS(List1)), IFERROR(INDEX(List3, ROWS(D1:$D$1)-ROWS(List1)-ROWS(List2)), ""))) + CTRL + SHIFT + ENTER copied down to D13.

Named ranges
List1 (A2:A6)
List2 (B2:B3)
List3 (C2:C5)

What is named ranges?

How to implement array formula to your workbook
Change named ranges. If your merged list starts at, for example, F3. Change D1:$D$1 in the above formula to F2:$F$2.

Download excel sample file for this tutorial.
merge-three-columns.xlsx
(Excel 2007 Workbook *.xlsx)

Functions in this article:

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

ROWS(array) returns the number of rows in a reference or an array

IFERROR(value;value_if_error) Returns value_if_error if expression is an error and the value of the expression itself otherwise

Question: How do I create a unique distinct list where other columns meet two criteria?

Answer:

Excel 2007 array formula in H2:

=IFERROR(INDEX(List, MATCH(0, COUNTIF(H1:$H$1, List)+IF(Category2<>Criteria2, 1, 0)+IF(Category1<>Criteria1, 1, 0), 0)), "") + CTRL + SHIFT + ENTER copied down to H16.

Earlier Excel versions, array formula in H2:

=IF(ISERROR(INDEX(List, MATCH(0, COUNTIF(H1:$H$1, List)+IF(Category2<>Criteria2, 1, 0)+IF(Category1<>Criteria1, 1, 0), 0)), ""));"";INDEX(List, MATCH(0, COUNTIF(H1:$H$1, List)+IF(Category2<>Criteria2, 1, 0)+IF(Category1<>Criteria1, 1, 0), 0)), "")) + CTRL + SHIFT + ENTER copied down to H16.

Named ranges
List (C2:C16)
Category1 (A2:A16)
Category2 (B2:B16)
Criteria1 (F1)
Criteria2 (F2)

What is named ranges?

How to implement array formula to your workbook
Change named ranges. If your unique distinct list starts at, for example, F3. Change H1:$H$1 in the above formula to F2:$F$2.

The ranges don´t have to be adjacent.

Download excel sample file for this tutorial.
create-a-list-of-distinct-values-from-two-criteria.xlsx
(Excel 2007 Workbook *.xlsx)

Functions in this article:

MATCH(lookup_value;lookup_array; [match_type]
Returns the relative position of an item in an array that matches a specified value

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given condition

Question: How do I sum values only if a unique value exists in another column?

Answer:

Formula in D13:

=SUM(IF(COUNTIF(List1, List1)=1, List2, 0)) + CTRL + SHIFT + ENTER

The ranges don´t have to be adjacent.

Named ranges
List1 (A2:A11)
List2 (B2:B11)
What is named ranges?

How to customize the formula to your excel sheet
To copy this formula in to your excel spreadsheet you don´t need to edit the formula.  Only change the named ranges.

Download excel sample file for this tutorial.
sum-only-if-unique-value-in-another-column.xls
(Excel 97-2003 Workbook *.xls)

Functions in this article:

COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given condition

IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

SUM(number1,[number2],)
Adds all the numbers in a range of cells

Question: How would I go about looking up data in an cross reference table.
I have the header row (i.e. 24) value and the column (mm) value and want to return the x/y value. i.e I have 25/X and 9/Y item and want 1.8 to be returned.
(mm) 22 23 24 25 26 27 28 29
8 1.3 1.8 1.8 1.8 1.8 1.8 2.3 2.3
9 1.3 1.8 1.8 1.8 1.8 1.8 2.3 2.3
10 1.3 1.8 1.8 1.8 1.8 1.8 2.3 2.3
11 2.2 2.8 2.8 2.8 2.8 2.8 3.3 3.3

thanks

Answer:

Formula in F1:

=INDEX(tbl, MATCH(y_value, y_header, 0), MATCH(x_value, x_header, 0))

Named ranges
tbl (C4:J7)
y_value (B1)
x_value (D1)
y_header (B4:B7)
x_header (C3:J3)
What is named ranges?

How to customize formula to your workbook
Change named ranges.

Download excel sample file for this tutorial.
looking-up-data-in-an-cross-ref-table
(Excel 97-2003 Workbook *.xls)

Functions in this article:

MATCH(lookup_value;lookup_array; [match_type])
Returns the relative position of an item in an array that matches a specified value

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

Question: I have three ranges or lists and I need to extract duplicates from those ranges combined using excel array formula?

Answer:

Excel 2007 array formula in D2:

=IFERROR(IFERROR(IFERROR(INDEX(List1, MATCH(0, COUNTIF(D1:$D$1, List1)+IF(COUNTIF(List1, List1)>1, 0, 1), 0)), INDEX(List2, MATCH(0, COUNTIF(D1:$D$1, List2)+IF((COUNTIF(List2, List2)+COUNTIF(List1, List2))>1, 0, 1), 0))), INDEX(List3, MATCH(0, COUNTIF(D$1:$D1, List3)+IF((COUNTIF(List3, List3)+COUNTIF(List1, List3)+COUNTIF(List2, List3))>1, 0, 1), 0))), "") + CTRL + SHIFT + ENTER copied down to D20.

Named ranges
List1 (A2:A20)
List2 (B2:B7)
List3 (C2:C8)
What is named ranges?

How to implement array formula to your workbook
Change named ranges. If your duplicates list starts at, for example, F3. Change D1:$D$1 in the above formulas to F2:$F$2.

Download excel sample file for this tutorial.
how-to-extract-a-list-of-duplicates-from-three-columns-in-excel.xlsx
(Excel 2007 Workbook *.xlsx)

Functions in this article:

MATCH(lookup_value;lookup_array; [match_type]
Returns the relative position of an item in an array that matches a specified value

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given condition

IFERROR(value;value_if_error) Returns value_if_error if expression is an error and the value of the expression itself otherwise

I have written a previous post about extracting duplicates from one column (range): How to extract a list of duplicates from a column in excel.

Inspired by a comment in this post I figured out a shorter easier array formula.

Excel 2007 array formula in C2:

=IFERROR(INDEX(List1, MATCH(0, COUNTIF(C1:$C$1, List1)+IF(COUNTIF(List1, List1)>1, 0, 1), 0)), "") + CTRL + SHIFT + ENTER copied down to D20.

Earlier Excel versions, array formula in C2:

=IF(ISERROR(INDEX(List1, MATCH(0, COUNTIF(C1:$C$1, List1)+IF(COUNTIF(List1, List1)>1, 0, 1), 0))), "", INDEX(List1, MATCH(0, COUNTIF(C1:$C$1, List1)+IF(COUNTIF(List1, List1)>1, 0, 1), 0)))

Named ranges
List1 (A2:A20)
What is named ranges?

How to implement array formula to your workbook
Change named ranges. If your duplicates list starts at, for example, F3. Change C1:$C$1 in the above formulas to F2:$F$2.

Download excel sample file for this tutorial.
how-to-extract-a-list-of-duplicates-from-a-column-in-excel.xlsx
(Excel 2007 Workbook *.xlsx)

Functions in this article:

MATCH(lookup_value;lookup_array; [match_type]
Returns the relative position of an item in an array that matches a specified value

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given condition

IFERROR(value;value_if_error) Returns value_if_error if expression is an error and the value of the expression itself otherwise