+ Reply to Thread
Results 1 to 6 of 6

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

  1. #1
    Registered User
    Join Date
    03-31-2019
    Location
    Bangladesh
    MS-Off Ver
    Office 7
    Posts
    7

    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

    Thanking you in advance

    Antor

  2. #2
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Excel 2007 & 2003
    Posts
    2,888

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

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

    copy paste down till blank


    atul


    If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.

    Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".

  3. #3
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2010
    Posts
    1,796

    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
    Last edited by canapone; 04-02-2019 at 05:53 AM.
    -----------------------------------------------------

    At Excelforum, you can say "Thank you!" by clicking the star icon ("Add Reputation") below the post.

    Please, mark your thread [SOLVED] if you received your answer.

  4. #4
    Registered User
    Join Date
    03-31-2019
    Location
    Bangladesh
    MS-Off Ver
    Office 7
    Posts
    7

    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
    Attached Files Attached Files

  5. #5
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2010
    Posts
    1,796

    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
    Attached Files Attached Files
    Last edited by canapone; 04-04-2019 at 01:13 AM.

  6. #6
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    5,664

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

    Please try at I2
    =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)),"")
    Attached Files Attached Files
    Last edited by Bo_Ry; 04-02-2019 at 02:07 PM. Reason: add 2007 formula

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Extract the Unique List based on SAP Codes and Header from two tabs
    By Neilesh Kumar in forum Excel General
    Replies: 6
    Last Post: 09-07-2018, 09:29 AM
  2. [SOLVED] Extract Unique Value with Multiple Entries in a single cell
    By spishowtime in forum Excel General
    Replies: 4
    Last Post: 03-08-2017, 04:40 PM
  3. Extract Unique Position code based on multiple criteria
    By ajaypal.sp in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-19-2016, 11:16 PM
  4. [SOLVED] How to extract the unique reference from range of multiple column
    By savetrees in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 03-15-2016, 05:41 PM
  5. [SOLVED] Convert Y Codes from one column to another based on Unique Codes
    By ashishmehra2010 in forum Excel General
    Replies: 2
    Last Post: 10-06-2015, 09:49 PM
  6. Replies: 2
    Last Post: 06-04-2015, 12:47 PM
  7. [SOLVED] Multiple Codes Per Cell and Filter Column With Individual Totals
    By pjohnburke in forum Excel General
    Replies: 2
    Last Post: 04-05-2012, 02:59 PM

Bookmarks

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