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

Extract, Copy, Group and Sort Unique Text to another Column

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.

How to extract, copy and group unique data text in the another column of Excel worksheet
In the picture above we have the following situation, solving the task to group extracted and copied text from column 'A'. In column 'C' 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 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) 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));"")

How to copy, group and sort Unique data in another column