## How to copy Unique text or a string from columns in Excel

In this tutorial I will show

In the picture below you see more columns. In each column there is a formula to

**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 (AZ).In the picture below you see more 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.**Attention!**Pay attention to the separator in formulas. Maybe you need to use a

**comma (,)**instead of a

**semicolon (;)**

## 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

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");"")

**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

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

**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&""))

=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))))

=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 count the number of times specific text appears in column

The formula in cell 'F3' is next below. This formula returns a

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

**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 data from Excel column

**ARRAY formula in cell 'G3'**is next below. Another formula, which

**returns a unique textual data from the 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.

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

=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));"")

**ARRAY formula in cell 'I3'**is as follows below. The 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. The 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

=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));"")

**displaying unique data from column**'A' to 'I' column we have only one array formula.**ARRAY formula in cell 'J3'**is next below. The 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 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 number 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"**is 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 the 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 examples of how you can

In the first 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

=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);"")

=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);"")

**extract the unique data from a table**In the first 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 and returns 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 cells A2:D5 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 into the 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 the range of Excel table

In this second 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.

=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");)&""

**ARRAY formula in cell 'F2'**is as follows below: The formula is**searching range of cells A2:D9 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 the range.=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");)&""