# Working With Unique Data in Excel

## How to Copy Unique Text or a String from Columns in Excel

In this **Excel example tutorial** I will show how we can **copy and grouped for a unique values**, or **text string** in Excel. This tutorial you realize as a collection of formulas (I am not the author, all the formulas) using that include unique data, text, values or numbers, whether they are just copied into the existing order or **sorted in ascending order** (A-Z).

In the picture below you see multiple columns. In each column there is a **formula to Copy Unique Data** or counting of certain data appearing in the column. Column "A" contains data. On the first picture you see that the situation is that we have a text data in column A.

**Pay attention**, you may need as a separator of arguments formula **use a comma (,) instead of a semicolon (;)**.

Copy formula to Notepad and with Find/Replace change semicolon to comma.

## How to to count the number of times specific text appears in column

The formula in cell "B3" is next below. This formula performs counting certain conditions and in this case it is a string or text item 'John Doe'. Copy a formula to last row.

=IF($A$3:$A$21="John Doe";COUNTIF(A$3:A3;A3);"")

## How to from duplicate text to create Unique Text

I suppose sometimes you can get into a situation where **from duplicates in a range of cells you need to create a unique data** that you can continue further calculations and calculated to achieve the desired goal.

The formula in cell "C3" is next below. This formula searches the column 'A' and set the condition in the formula joins the serial number of the order of occurrence conditions and in this case it is a string or text item 'John Doe'. Copy a formula to last row.

=IF(A3="John Doe";"John Doe"&COUNTIF(A$2:A3;"John Doe");"")

## How to to Count Unique Data and display the sequence number of appearances

The formula in cell 'D3' is next below. This formula search column 'A' and as a result **returns the number of occurrences of a unique data from the same row**. In as much, the formula encounters a duplicate, ignores him and continues in the next row. Copy a formula to last row.

=IF(COUNTIF(A$1:A3;A3)=1;MAX(D$1:D1)+1;"")

## Copying cells that contain Unique data/text in the column

The formula in cell 'E2' is the next below. This formula **counting unique data text in the column** 'A'. The formula is a prerequisite and related to the formula below in cell E3.

=SUMPRODUCT(--ISTEXT(A$3:A$21);1/COUNTIF(A$3:A$21;A$3:A$21&""))

ARRAY formula in cell 'E3' is next below. This formula as a result of **returns all the Unique Data from column** 'A'. The formula is related to the formula in cell 'E2'. This formula you finish with **Ctrl+Shift+Enter**. Copy a formula to last row.

=IF(ROWS(E$3:E3)>E$2;"";INDEX(A$3:A$21;SMALL(IF(FREQUENCY(IF(ISTEXT(A$3:A$21);IF(A$3:A$21<>"";MATCH(A$3:A$21;A$3:A$21;0)));ROW(A$3:A$21)-ROW(A$3)+1);ROW(A$3:A$21)-ROW(A$3)+1);ROWS(E$3:E3))))

## How to to Count the Number of Times specific text appears in column

The formula in cell 'F3' is next below. This formula **returns a number of occurrences the unique text data from column** 'A'. The formula is related to the formula in cell 'E3' because it contains the condition. Copy a formula to last row

=IF(OR(E3=0;E3="");"";COUNTIF($A$3:$A$21;E3))

## Extracting Unique Text String from Excel column

**ARRAY formula** in cell 'G3' is next below. Another formula, which returns a unique textual data from column. This formula as a result of **returns all the unique data of the text from column** 'A', of course, as you can observe **data are unsorted**. Copy a formula to last row

=IFERROR(IF(INDEX($A$3:$A$21;MATCH(0;COUNTIF($G$2:G2;$A$3:$A$21 );0))=0;"";INDEX($A$3:$A$21;MATCH(0;COUNTIF($G$2:G2;$A$3:$A$21 );0)));"")

## Copy and Grouping Unique Data from column

**ARRAY formula** in cell 'H3' is as follows below. Another formula that **returns Unique Unsorted Data from the column**. Copy a formula to last row

=IFERROR(INDEX($A$3:$A$21;SMALL(MATCH($A$3:$A$21;$A$3:$A$21;0);SUM((COUNTIF($A$3:$A$21;$H$2:H2)))+1));"")

## Copy and sort of Unique Data from column

In the 'I' column there are two formula and the second formula depend on the first.

**ARRAY formula** in cell 'I3' is as follows below. Formula as a result, **returns the First Unique Data from column** 'A'.

=INDEX($A$3:$A$21;MATCH(0;COUNTIF($A$3:$A$21;"<"&$A$3:$A$21);0))

**ARRAY formula** in cell 'I4' is next below. Formula as a result, continues to **return other unique data from column** 'A'. The formula is depending on to the above formula in cell I3. Copy a formula to last row.

=IF(COUNTIF($A$3:$A$21;">"&I3);INDEX($A$3:$A$21;MATCH(COUNTIF($A$3:$A$21;"<="&I3);COUNTIF($A$3:$A$21;"<"&$A$3:$A$21);0));"")

## Copy and Sort Unique data from column

Unlike the previous column in 'J' in which there are two formulas for **displaying unique data from column** 'A' to 'I' column we have only one array formula.

**ARRAY formula** in cell 'J3' is next below. Formula as a result, **displays unique data from column** 'A'. Copy a formula to last row.

=IFERROR(INDEX($A$3:$A$21;MATCH(0;COUNTIF($A$3:$A$21;"<"&$A$3:$A$21)-SUM(COUNTIF($A$3:$A$21;"="&J$2:J2));0));"")

## How to Copy a Unique Values or Numbers from the Column in Excel

All of the above is related to text data. If you have a situation in terms of numbers or values then use the same formula above. In the picture below you will see how the situation looks using the above mentioned formula, if 'A' column contains values or numbers.

The formula in cell "C3" is next below. This formula is related to the Numbers or Values, use this formula **can duplicate values converted to unique values**.

=IF(A3=77;A3&"-"&COUNTIF(A$2:A3;77);"")

The formulas contained in row 22 are as follows:

**ARRAY formula** in the cells "A22", "G22", "H22" are next. The **formula counts the number of unique data in a range of cells.**

=SUM(IF(FREQUENCY(IF(SUBTOTAL(3;OFFSET($A$3:$A$21;ROW($A$3:$A$21)-ROW($A$3);0;1));MATCH($A$3:$A$21;$A$3:$A$21;0));ROW($A$3:$A$21)-ROW($A$3)+1);1))

The formula in cell "B22", "D22", "F22", are the following: The **formula counts how many cells in a column filled.**

=COUNT(B3:B21)

The formula in cell 'C22', 'E22', 'I22', 'J22' is as follows: The **formula returns how much filled cells that contain data in column.**

=SUMPRODUCT(--ISTEXT(C$3:C$21);1/COUNTIF(C$3:C$21;C$3:C$21&""))-1

## How to copy the unique data, from range of cells or Excel spreadsheets

In a situation where you have a table or a range of cells, and you have the need to **copy or extract the unique data**, here are a few **Excel examples formulas** of how you can **extract the unique data from a table/range.**

In the first Excel example in the figure below, notice that **all the cells in a table filled**. If a cell does not contain data, an empty then the formula will return as a result of the number zero (0).

**ARRAY formula** in cell 'F2' is as follows: The formula is searching the entire range of cells of the table/range and **return the first unique data from the range**. Copy a formula to last row

=IFERROR(INDEX($A$2:$D$5;MIN(IF(COUNTIF($F$1:F1;$A$2:$D$5)=0;ROW($A$2:$D$5)-MIN(ROW($A$2:$D$5))+1));MATCH(0;COUNTIF($F$1:F1;INDEX($A$2:$D$5;MIN(IF(COUNTIF($F$1:F1;$A$2:$D$5)=0;ROW($A$2:$D$5)-MIN(ROW($A$2:$D$5))+1));;1));0);1);"")

**ARRAY formula** in cell 'G2' is as follows: The formula is searching range of 'A2:D5' cells and **Returns Unique Data from the range**. You notice that the range of cells in a table **named as "myrange"**. The formula should be a single line of code in to formula bar.

=IFERROR(INDEX(myrange;SMALL(IF(SMALL(IF(COUNTIF($G$1:G1;myrange)=0;COUNTIF(myrange;"<"&myrange)+1;"");1)=COUNTIF(myrange;"<"&myrange)+1;ROW(myrange)-MIN(ROW(myrange))+1);1);MATCH(MIN(IF(COUNTIF($G$1:G1;myrange)>0;"";COUNTIF(myrange;"<"&myrange)+1));INDEX(COUNTIF(myrange;"<"&myrange)+1;SMALL(IF(SMALL(IF(COUNTIF($G$1:G1;myrange)=0;COUNTIF(myrange;"<"&myrange)+1;"");1)=COUNTIF(myrange;"<"&myrange)+1;ROW(myrange)-MIN(ROW(myrange))+1);1);;1);0);1);"")

## Copy Unique Data From Range of Excel table

In this second Excel example of the image below you see a similar situation as in the previous example. The difference is that in the range of cells Excel table **some cells are empty** and do not contain data.

**ARRAY formula** in cell 'F2' is as follows below: The formula is **searching range of 'A2:D9' cells and copied and groups all Unique data**. You notice this part of the formula 'ROW($2:$9)' which is tied to the number of rows in range. I used INDIRECT Function and combine with COUNTIF, ROW, COLUMN etc.

=INDIRECT(TEXT(MIN(IF(($A$2:$D$9<>"")*(COUNTIF($F$1:F1;$A$2:$D$9)=0);ROW($2:$9)*100+COLUMN($A:$D);7^8));"R0C00");)&""

Posts related to **Unique Data in Excel**.

- Copying and Sorting Unique Alphanumeric Data in Excel
- UNIQUE vs DISTINCT data
- Count Unique From Multiple Columns If Match Criteria