## How to extracting the text that is repeated from the mixed data and copying, grouping only Unique Data in a new column

In this tutorial I will show how we can

**extract, copy, group and sort unique text in another column**of Excel worksheet. In the picture below we have data in column 'A'. You notice that these are**alphanumeric data**ie. the mixed data, number and text that are repeated, ie.**Duplicated**. Our goal is to**extract only the text from the cell and then group and sort respective text**. Also that in this first part of examples grouped text is not sorted 'A - Z' in alphabetical order.In the picture above we have the following situation, solving the task to group extracted and copied text from column 'A'. In column 'C'

The formula in cell 'C2' is as follows (this formula is copied down). This formula with REPLACE function finds the position of blank characters in the text and number before, replaced by spaces starting with the first character in the text. FIND function finds a blank character in the text.

=REPLACE(A2;1;FIND(" ";A2);"")

or another formula that can also be used (this formula with the TRIM function removes blank character or space at the beginning of alphanumeric number-text after the MID function and SEARCH function perform their action)

=TRIM(MID(A2;SEARCH(" ";A2;1);LEN(A2)))

When we remove a number from the text,

The formula in cell 'D2' counting duplicates is as follows:

=COUNTIF(C$2:C2;C2)

**we have extracted text from cells**that contain the number-text in column 'A'.The formula in cell 'C2' is as follows (this formula is copied down). This formula with REPLACE function finds the position of blank characters in the text and number before, replaced by spaces starting with the first character in the text. FIND function finds a blank character in the text.

=REPLACE(A2;1;FIND(" ";A2);"")

or another formula that can also be used (this formula with the TRIM function removes blank character or space at the beginning of alphanumeric number-text after the MID function and SEARCH function perform their action)

=TRIM(MID(A2;SEARCH(" ";A2;1);LEN(A2)))

When we remove a number from the text,

**there are duplicates of text**that does not suit us for further calculation. We need extra column. In the extra column 'D' was set formula by which**count the number of repetitions**of a particular text (sequence number if the text is duplicated). You notice the sequential number of repetitions, the specified text duplicates.The formula in cell 'D2' counting duplicates is as follows:

=COUNTIF(C$2:C2;C2)

## How to copy and unique data group in alphabetical order

Furthermore, we come to the column 'H' (see picture above) in which we want to

ARRAY formula in cell 'H2' is the following. This formula, copying and grouping unique data from column 'C'. (You need to complete this formula with

=IFERROR(INDEX($C$2:$C$21;SMALL(IF($D$2:$D$21=1;ROW($C$2:$C$21)-ROW($C$2)+1);ROWS($C$2:$C2)));"")

**copy and group unique text from column**'C'. In this column you can see**copied and grouped but unsorted unique data**.ARRAY formula in cell 'H2' is the following. This formula, copying and grouping unique data from column 'C'. (You need to complete this formula with

**Ctrl+Shift+Enter**)=IFERROR(INDEX($C$2:$C$21;SMALL(IF($D$2:$D$21=1;ROW($C$2:$C$21)-ROW($C$2)+1);ROWS($C$2:$C2)));"")

## How to copy, group and sort unique data in alphabetical order

**To copy, group and sort unique data**, we can proceed as follows. (see figure below). In column 'B' as I wrote in the previous section tutorials include only the text from cells in column 'A'.

In column 'E' is extracted text that is copied from column 'A' and

**grouped as the unique data**(

**without blank rows or cells**) but

**is still unsorted**.

ARRAY formula in cell 'E2' is as follows: (a formula you're done with

**Ctrl+Shift+Enter**), you need to copy the formula down.

*This formula is always placed in the second row (Row2) never in the first row*. The formula uses the source data from column 'A' and

**copying unique text, without numbers**. Also you notice that

**using this formula we did not fix the sorting**results.

*The formula should be in a line (row).*

=IFERROR(INDEX(MID($A$2:$A$21;FIND(" ";$A$2:$A$21)+1;256);MATCH(0;MMULT((MID($A$2:$A$21;FIND

(" ";$A$2:$A$21)+1;256)=TRANSPOSE($E$1:$E1))*1;(ROW($E$1:$E1)>0)*1);0));"")

The formula that is in column 'G'

**copy unique data from column 'A' and grouping without blank cells or rows but not extracts text**ie. copying of the

**original Unique data**. Also, note that the data in column 'G'

**sorted order "A-Z"**(according to the rules as they of Excel sorts).

**For copying, grouping and sorting of unique data**in column 'G' we use two formulas.

ARRAY formula in cell 'G2' is as follows: This formula uses column 'A' as a data source.

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

ARRAY formula in cell 'G3' is as follows: This formula is copied down.

*The formula should be in a line (row)*. This formula uses column 'A' as a data source.

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

## Copying, grouping and sorting Unique Data

The formula that is in the 'H' column (figure below)

ARRAY formula in cell 'H2'' below is as follows: This formula uses column 'B' as a data source. The formula should be in a one line (row).

=INDEX($B$2:$B$21;MATCH(0;COUNTIF( $B$2:$B$21 ;"<"&$B$2:$B$21);0))

ARRAY formula in cell 'H3' below is as follows: This formula is copied down.

=IF(COUNTIF($B$2:$B$21;">"&H2);INDEX($B$2:$B$21;MATCH(COUNTIF($B$2:$B$21;"<="&H2);COUNTIF($B$2:$B$21;"<"&$B$2:$B$21);0));"")

**copying unique data from column**'B' and**grouping without blank cells or rows**. Also, note that the**data in column 'H' sorted "A-Z" alphabetical order**.**For copying, grouping and sorting data**in a column 'H' we use two formulas, ie. the**same formula as in the column 'G'**.ARRAY formula in cell 'H2'' below is as follows: This formula uses column 'B' as a data source. The formula should be in a one line (row).

=INDEX($B$2:$B$21;MATCH(0;COUNTIF( $B$2:$B$21 ;"<"&$B$2:$B$21);0))

ARRAY formula in cell 'H3' below is as follows: This formula is copied down.

*The formula should be in one line (row)*. This formula uses the column 'B' as a data source.=IF(COUNTIF($B$2:$B$21;">"&H2);INDEX($B$2:$B$21;MATCH(COUNTIF($B$2:$B$21;"<="&H2);COUNTIF($B$2:$B$21;"<"&$B$2:$B$21);0));"")