+ Reply to Thread
Results 1 to 12 of 12

One instance of each value within a text range

  1. #1
    Forum Contributor
    Join Date
    07-17-2013
    Location
    Birmingham, England
    MS-Off Ver
    Excel for O365
    Posts
    197

    One instance of each value within a text range

    Hi all,

    I have a list of text cells in column A, the list is a dynamic one that changes depending on the property selected in a cell of another sheet.

    Example data... Column A is Block Name, Column B is Room Number.

    ---------A----------------B-------
    1--Main Building-------001
    2--Block A--------------001
    3--Block A--------------002
    4--Main Building-------002
    5--Block B--------------001
    6--Block A--------------003
    7--Main Building-------002a
    8--BSS House----------BSS
    9--Main Building-------003


    What I need is for a formula that lists one example of each instance included in Column A.

    As per the example data above, the expected results when dragging down from C1 through to C9 would be:


    ---------A----------------B--------------C--------
    1--Main Building-------001-------Main Building
    2--Block A--------------001-------Block A
    3--Block A--------------002-------Block B
    4--Main Building-------002-------BSS House
    5--Block B--------------001-------
    6--Block A--------------003-------
    7--Main Building-------002a-------
    8--BSS House----------BSS-------
    9--Main Building-------003-------


    I know I could use a pivot table to do this but as I need this to be dynamic, and also to be used with Excel on an iPad Air, I can't as it doesn't support pivot tables or VB to automatically refresh it.

    Any help greatly appreciated and many thanks in advance!

    Jason

  2. #2
    Valued Forum Contributor
    Join Date
    09-21-2011
    Location
    Birmingham UK
    MS-Off Ver
    Excel 2003/7/10
    Posts
    2,188

    Re: One instance of each value within a text range

    =INDEX($A$1:$A$20, MATCH(0, COUNTIF($B$1:B1, $A$1:$A$20), 0))

    is the formula you need, this is an array formula, need to ctrl shift and enter it, then drag it down.

    Problem is, you need the same amount of formulas as data, as they could all be unique, so this will have to be dynamic as well.

    This formula is for data in A1:A20, so needs formula in B1:B20 also.
    Hope this helps

    Sometimes its best to start at the beginning and learn VBA & Excel.

    Please dont ask me to do your work for you, I learnt from Reading books, Recording, F1 and Google and like having all of this knowledge in my head for the next time i wish to do it, or wish to tweak it.
    Available for remote consultancy work PM me

  3. #3
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    13,816

    Re: One instance of each value within a text range

    c1 =
    Please Login or Register  to view this content.
    and drag down.

    then filter on column C on YES.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  4. #4
    Forum Contributor
    Join Date
    07-17-2013
    Location
    Birmingham, England
    MS-Off Ver
    Excel for O365
    Posts
    197

    Re: One instance of each value within a text range

    Hi both,

    Thanks for the input but Nathan, I'm afraid I couldn't get your solution to work. When dragging down the array, all the cells read "Main Building." As for all of the cells in Column A having the possibility of being unique... that's not the case. There could potentially only be as few as 1-5 different text cells, definitely no more than 10.

    And Oeldere, although yours works fine, it's not quite what I need as I need the Name of the block to come through.

    I've uploaded a test workbook with the example data and both of your solutions included so if either of you could get the formula to replicate the expected results then I'd be very appreciative!


    Many thanks,

    Jason
    Attached Files Attached Files

  5. #5
    Valued Forum Contributor
    Join Date
    09-21-2011
    Location
    Birmingham UK
    MS-Off Ver
    Excel 2003/7/10
    Posts
    2,188

    Re: One instance of each value within a text range

    You problems my way were

    1) you were including the header
    2) you'd moved the countif from the results

    Block
    Main Building
    Block A
    Block B
    BSS House


    Is what i get, just changing the countif part to be column D, you were counting if your column a was in column a

    =INDEX($A$1:$A$20, MATCH(0, COUNTIF($D$1:D1, $A$1:$A$20), 0))

  6. #6
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    13,816

    Re: One instance of each value within a text range

    You didn't implement my formula correct (see the orange cells).

    See the green cells for the result.

    I used helpcolumns to get them.

    See the attached file.
    Attached Files Attached Files

  7. #7
    Forum Contributor
    Join Date
    07-17-2013
    Location
    Birmingham, England
    MS-Off Ver
    Excel for O365
    Posts
    197

    Re: One instance of each value within a text range

    Quote Originally Posted by nathansav View Post
    You problems my way were

    1) you were including the header
    2) you'd moved the countif from the results

    Block
    Main Building
    Block A
    Block B
    BSS House


    Is what i get, just changing the countif part to be column D, you were counting if your column a was in column a

    =INDEX($A$1:$A$20, MATCH(0, COUNTIF($D$1:D1, $A$1:$A$20), 0))
    Ahh okay I get it! Thank you!

    Finally, is there a way to adapt it so that after I've dragged the formula down to say D10 and there are only 4 unique names, the other 5 formulas read "" instead of 0 or #N/A


    Many thanks again!

    Jason

  8. #8
    Valued Forum Contributor TheCman81's Avatar
    Join Date
    11-06-2012
    Location
    Edinburgh, UK
    MS-Off Ver
    Excel 2003, 2007, 2010, 2013
    Posts
    346

    Re: One instance of each value within a text range

    Go get what you need without helper columns you can use the following (see attached)

    Array formula so please enter with CTRL Shift Ent

    Please note that the use of INDIRECT must be used on the same workbook

    Jwillis07 Example v2.xlsx
    Excel Guru in the making

    <----------If the question has been answered to your satisfication please click the Add Repuation star to the left

  9. #9
    Valued Forum Contributor
    Join Date
    09-21-2011
    Location
    Birmingham UK
    MS-Off Ver
    Excel 2003/7/10
    Posts
    2,188

    Re: One instance of each value within a text range

    Yes with ISERROR or ISNA.

    Where in sunny brum are you?

  10. #10
    Forum Contributor
    Join Date
    07-17-2013
    Location
    Birmingham, England
    MS-Off Ver
    Excel for O365
    Posts
    197

    Re: One instance of each value within a text range

    Quote Originally Posted by nathansav View Post
    Yes with ISERROR or ISNA.

    Where in sunny brum are you?
    Cool, all working and does the job perfectly! Thank you.

    I live in Castle Bromwich and work in the City Centre at Birmingham Council

  11. #11
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    13,816

    Re: One instance of each value within a text range

    What about my solution in #6.

  12. #12
    Forum Contributor
    Join Date
    07-17-2013
    Location
    Birmingham, England
    MS-Off Ver
    Excel for O365
    Posts
    197

    Re: One instance of each value within a text range

    Quote Originally Posted by oeldere View Post
    What about my solution in #6.
    Whilst it works, it depends on three helper columns which when applying this to my actual workbook and not the test sheet I attached, I need to avoid the use of helper columns.

    Have made note of your solution though for future reference so thank you.

+ 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] Code for Range does not go past first instance.
    By aadeshgandhi in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-19-2013, 11:00 AM
  2. [SOLVED] Find first and last instance of text in column range multiple times
    By ShannonHowell in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-24-2012, 11:52 AM
  3. search a range for an instance of A, B or VC
    By nigelog in forum Excel General
    Replies: 5
    Last Post: 02-15-2011, 09:02 AM
  4. Text to columns - First Instance of =
    By hyyfte in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-06-2007, 12:50 PM
  5. Find 2nd instance of a word in a range.
    By Grumpy Grandpa in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 12-05-2005, 11:35 AM

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