+ Reply to Thread
Results 1 to 7 of 7

Make Column that Finds Unique Values Based on Keywords without Duplicates

  1. #1
    Registered User
    Join Date
    09-23-2016
    Location
    NY
    MS-Off Ver
    Pro Plus 2010
    Posts
    4

    Make Column that Finds Unique Values Based on Keywords without Duplicates

    Hello,

    I am trying to create a unique values column (V) that looks to data source column (S), that will pull out unique values that only contain "key text" without repeats. So far I have the formula that will analyse the entire data source column and will create a list of unique values. This formula is as follows:

    {=IFERROR(INDEX($S$3:$S$500,MATCH(0,INDEX(COUNTIF($V$2:V2,$S$3:$S$500),0,0),0)), "")}

    Any idea how to make it so it will only add values that contain "MET/" and/or "XDCR/" to the unique values column?
    Last edited by daveys93; 09-23-2016 at 04:04 PM.

  2. #2
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Make Column that Finds Unique Values Based on Keywords without Duplicates

    Post some sample data and show us what results you expect.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Registered User
    Join Date
    09-23-2016
    Location
    NY
    MS-Off Ver
    Pro Plus 2010
    Posts
    4

    Re: Make Column that Finds Unique Values Based on Keywords without Duplicates

    Source Column:

    Cell S3: MET/LN1
    Cell S4: MET/LN2
    Cell S5: 21B/LN1
    Cell S6: 87B/LN2
    Cell S7: XDCR/R1
    Cell S8: XDCR/R2
    Cell S9: 51A/R3
    Cell S10: MET/LN1
    Cell S11: MET/LN3
    Cell S12: XDCR/R1



    Results Using my Formula:

    Cell V3: MET/LN1
    Cell V4: MET/LN2
    Cell V5: 21B/LN1
    Cell V6: 87B/LN2
    Cell V7: XDCR/R1
    Cell V8: XDCR/R2
    Cell V9: 51A/R3
    Cell V10: MET/LN3
    Cell V11: blank



    My desired results:

    Cell V3: MET/LN1
    Cell V4: MET/LN2
    Cell V5: XDCR/R1
    Cell V6: XDCR/R2
    Cell V7: MET/LN3
    Cell V8: blank

  4. #4
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Make Column that Finds Unique Values Based on Keywords without Duplicates

    I could only do this by using a helper column.

    Data Range
    S
    T
    U
    V
    2
    ------
    ------
    ------
    ------
    3
    MET/LN1
    X
    MET/LN1
    4
    MET/LN2
    X
    MET/LN2
    5
    21B/LN1
    XDCR/R1
    6
    87B/LN2
    XDCR/R2
    7
    XDCR/R1
    X
    MET/LN3
    8
    XDCR/R2
    X
    9
    51A/R3
    10
    MET/LN1
    X
    11
    MET/LN3
    X
    12
    XDCR/R1
    X


    This formula entered in T3 and copied down:

    =IF(SUM(COUNTIF(S3,{"MET/*","XDCR/*"})),"X","")

    This array formula** entered in V3:

    =IFERROR(INDEX(S$3:S$12,MATCH(0,IF(T$3:T$12="X",COUNTIF(V$2:V2,S$3:S$12)),0)),"")

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.

    Copy down until you get blanks.

  5. #5
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Make Column that Finds Unique Values Based on Keywords without Duplicates

    Quote Originally Posted by Tony Valko View Post
    I could only do this by using a helper column.
    I must have been suffering from a brain cramp!

    Data Range
    S
    T
    U
    V
    2
    ------
    ------
    ------
    3
    MET/LN1
    MET/LN1
    4
    MET/LN2
    MET/LN2
    5
    21B/LN1
    XDCR/R1
    6
    87B/LN2
    XDCR/R2
    7
    XDCR/R1
    MET/LN3
    8
    XDCR/R2
    9
    51A/R3
    10
    MET/LN1
    11
    MET/LN3
    12
    XDCR/R1


    This array formula** entered in V3:

    =IFERROR(INDEX(S$3:S$12,MATCH(0,IF((LEFT(S$3:S$12,4)="MET/")+(LEFT(S$3:S$12,5)="XDCR/"),COUNTIF(V$2:V2,S$3:S$12)),0)),"")

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.

    Copy down until you get blanks.

  6. #6
    Registered User
    Join Date
    09-23-2016
    Location
    NY
    MS-Off Ver
    Pro Plus 2010
    Posts
    4

    Re: Make Column that Finds Unique Values Based on Keywords without Duplicates

    Works perfectly! Thanks so much; this will save me so much time throughout the next few years.

  7. #7
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Make Column that Finds Unique Values Based on Keywords without Duplicates

    You're welcome. Thanks for the feedback!

+ 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. Replies: 2
    Last Post: 08-19-2015, 10:22 AM
  2. Counting unique values and Duplicates only once in Column A.
    By areichst in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-17-2015, 12:29 AM
  3. [SOLVED] To find duplicates in a column and suffix them with numbers to make them unique
    By JishnuSurendran in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-23-2014, 06:34 AM
  4. [SOLVED] HDI - Return multiple keywords in a text column based on a set of available keywords
    By zneiley in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-25-2013, 01:32 PM
  5. [SOLVED] Macro to count unique values in a column, enter it in next column, then delete duplicates
    By pmorisse in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-25-2013, 03:27 PM
  6. [SOLVED] change column a duplicates -> unique values for column b
    By seattlemicah in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 07-23-2012, 05:51 PM
  7. Unique values in a column with duplicates
    By rbellotti in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-09-2008, 02:45 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