Our Recommendation for You Search your Query, You can find easily. for example search by book name or course name or any other which is related to your education

Followers

Unique Data in Excel

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

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

How to copy unique data or text from a column in Excel
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 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 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 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.

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

How to copy, grouping and sorting unique values or numbers in the column in Excel
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 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);"")

How to extract the unique data from Excel spreadsheets Table

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.

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

Copy unique data from range of Excel table