# How to extract unique code from a column having multiple codes in is cell

1. ## How to extract unique code from a column having multiple codes in is cell

Hey, I'm a new member of this forum. I trying to learn Excel. So i need help from all of you.
I have a sheet in which column F contains multiple codes in each cell.
From column F i want to extract the unique codes. In column H2 down words

I have tried text to column....copy paste column one below another...remove duplicates

But it text a lot of time

Is there any Formula or Mac row to help me

Antor

2. ## Re: How to extract unique code from a column having multiple codes in is cell

Try with
simultaneously press shift+ctrl+enter
Formula:
`Please Login or Register  to view this content.`

copy paste down till blank

3. ## Re: How to extract unique code from a column having multiple codes in is cell

Hi

first off, as a disclaimer: it's a very slow calculating formula.

In H2 to extract numbers from 241000 to 243000

=AGGREGATE(15,6,ROW(\$241000:\$244000)/((COUNTIF(F\$2:F\$840;"*"&ROW(\$241000:\$244000)&"*")>0)*(COUNTIF(H\$1:H1,ROW(\$241000:\$244000))=0)),ROWS(\$1:1))

Edit: maybe the following one it's a little faster than my first one

=AGGREGATE(15,6,ROW(\$241000:\$244000)/ISNUMBER(MATCH("*"&ROW(\$241000:\$244000)&"*",F\$2:F\$840,0)),ROWS(\$1:1))

Regards

4. ## Re: How to extract unique code from a column having multiple codes in is cell

Both of the above formula are not working.

Please provide me with a solution

I have made a manual extraction of the Unique codes in H column (Please see attachment)

I have given a manual extraction of the unique codes

5. ## Re: How to extract unique code from a column having multiple codes in is cell

Hi,

please refer to the attachment: in column I've copied

=AGGREGATE(15,6,ROW(\$241000:\$244000)/ISNUMBER(MATCH("*"&ROW(\$241000:\$244000)&"*",F\$2:F\$840,0)),ROWS(\$1:1))

For your convenience, I put the formula in a elementary IFERROR

Please let me know which are unexpected results

AGGREGATE is avaliable only from Excel 2010 onwards. Maybe this one is a problem.

If you're using previous version of Excel:

=IFERROR(SMALL(IF(ISNUMBER(MATCH("*"&ROW(\$241000:\$244000)&"*",F\$2:F\$840,0)),ROW(\$241000:\$244000)),ROWS(\$1:1)),"")

to be confirmed with control+shift+enter before to be copied down (please refer ti file 2007).

Regards

6. ## Re: How to extract unique code from a column having multiple codes in is cell

=IFERROR(AGGREGATE(15,6,ROW(\$241000:\$244000)/(FREQUENCY(--TEXT(MID(SUBSTITUTE(\$F\$2:\$F\$840," ",REPT(" ",99)),COLUMN(A\$1:L\$1)*99-98,99),"0;;;\0"),ROW(\$241000:\$244000))>0),ROWS(I\$1:I2)),"")

or for Excel2007
J2 and press Ctrl+Shift+Enter
=IFERROR(SMALL(IF(FREQUENCY(--TEXT(MID(SUBSTITUTE(\$F\$2:\$F\$840," ",REPT(" ",99)),COLUMN(A\$1:L\$1)*99-98,99),"0;;;\0"),ROW(\$241000:\$244000)),ROW(\$241000:\$244000)),ROWS(J\$1:J2)),"")

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1