+ Reply to Thread
Results 1 to 19 of 19

Extract list of Names to display only once

  1. #1
    Forum Contributor
    Join Date
    08-19-2012
    Location
    Sydney
    MS-Off Ver
    MS Office 365/16
    Posts
    282

    Extract list of Names to display only once

    See attached.

    On 'sheet1' i have a list of names. Some names are listed more than once

    On 'sheet2' is the result i am trying to get. That is to list the names, but only display them once.

    Any help is always appreciated
    Attached Files Attached Files

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Extract list of Names to display only once

    1. Highlight your range of names
    2. Data-->Sort & Filter-->Advanced
    3. See the picture below.
    Attached Images Attached Images
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Forum Contributor
    Join Date
    08-19-2012
    Location
    Sydney
    MS-Off Ver
    MS Office 365/16
    Posts
    282

    Re: Extract list of Names to display only once

    Sorry, i should have specified this is in google sheets

  4. #4
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Extract list of Names to display only once

    No worries. I don't work in that area. Hopefully someone will come along. I have moved this thread to that sub-forum.

  5. #5
    Forum Contributor
    Join Date
    08-19-2012
    Location
    Sydney
    MS-Off Ver
    MS Office 365/16
    Posts
    282

    Re: Extract list of Names to display only once

    Hi all, still require some help with this please.

  6. #6
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Extract list of Names to display only once

    I'm not familiar with Google Sheets, but I assume that the "common" functions in Excel are also available in GS, so you can put this formula in B2 of Sheet1:

    =IF(COUNTIF(A$2:A2,A2)=1,MAX(B$1:B1)+1,"-")

    Copy down to the bottom of your list (or beyond, if you want to add new data in column A), and then you can use this formula in cell A2 of Sheet2:

    =IFERROR(INDEX(Sheet1!A:A,MATCH(ROWS($1:1),Sheet1!B:B,0)),"")

    Copy down until you start to get blanks.

    Hope this helps.

    Pete

  7. #7
    Forum Contributor
    Join Date
    08-19-2012
    Location
    Sydney
    MS-Off Ver
    MS Office 365/16
    Posts
    282

    Re: Extract list of Names to display only once

    That seems to do the trick. Many thanks

  8. #8
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Extract list of Names to display only once

    It's actually much easier in GS than it would be in Excel (pending the new functionality). Just use:

    =UNIQUE(Sheet1!A2:A)

    in one cell and GS will produce the full unique list for you.
    Rory

  9. #9
    Forum Contributor
    Join Date
    08-19-2012
    Location
    Sydney
    MS-Off Ver
    MS Office 365/16
    Posts
    282

    Re: Extract list of Names to display only once

    I now have an updated issue. I now have a list of names in two separate columns, but want to be listed on a different sheet showing the name only once.

    See attached
    Attached Files Attached Files

  10. #10
    Forum Contributor
    Join Date
    08-19-2012
    Location
    Sydney
    MS-Off Ver
    MS Office 365/16
    Posts
    282

    Re: Extract list of Names to display only once

    I can't use macro coding.

  11. #11
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,896

    Re: Extract list of Names to display only once

    I have just noticed that this is a triple post. Do this again and you can expect to be banned from this site.
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  12. #12
    Forum Contributor
    Join Date
    08-19-2012
    Location
    Sydney
    MS-Off Ver
    MS Office 365/16
    Posts
    282

    Re: Extract list of Names to display only once

    Sorry Glenn. I was getting confused as to where i should be posting this. I do apologies.

    In response to your comment :

    One way:

    =IFERROR(IFERROR(INDEX(Sheet1!$A$2:$A$8,MATCH(0,INDEX(COUNTIF($A$1:A1,Sheet1!$A$2:$A$8),0,0),0)),INDEX(Sheet1!$E$2:$E$6,MATCH(0,INDEX(COUNTIF($A$1:A1,Sheet1!$E$2:$E$6),0,0),0))),"")

    If thenumber of names in each column can be variable, I'd be tempted to create two named ranges to adjust the lengths of the ranges containing the names automatically, as this formula does not like blanks.

    I don't quite seem to be able to get it working for what i have. When i drag down the names seem to be doubling up and a "0" appears in the middle of it. Maybe the name ranges might work??

  13. #13
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,896

    Re: Extract list of Names to display only once

    Ok.

    Im away for a couple of hours. Two Qs. Have you used exact ranges on your real sheet ( no blank rows at the bottom)? Are there blank cells in the middle of either list?

  14. #14
    Forum Contributor
    Join Date
    08-19-2012
    Location
    Sydney
    MS-Off Ver
    MS Office 365/16
    Posts
    282

    Re: Extract list of Names to display only once

    I have attached a newer version showing more of what i am looking for. I need to extract names from "1st_XI" column B and column J into "1st_XI_Statistics" in column A.

  15. #15
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,896

    Re: Extract list of Names to display only once

    Create 2 Named Ranges List1 and List2. CTRL-F3 to view edit. As an example, here's List1:

    ='1st_XI'!$B$19:INDEX('1st_XI'!$B$19:$B$33,COUNTA('1st_XI'!$B$19:$B$33))

    then in A2, copied down:

    =IFERROR(IFERROR(INDEX(List1,MATCH(0,INDEX(COUNTIF($A$1:A1,List1),0,0),0)),INDEX(List2,MATCH(0,INDEX(COUNTIF($A$1:A1,List2),0,0),0))),"")

  16. #16
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Extract list of Names to display only once

    If this is still Google Sheets:

    =unique({A2:A8;E2:E6})

  17. #17
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,295

    Re: Extract list of Names to display only once

    As the O.P. version states 2010.
    and I spent time exercising my grey matter I may as well post my donation.
    torachan.

  18. #18
    Forum Contributor
    Join Date
    08-19-2012
    Location
    Sydney
    MS-Off Ver
    MS Office 365/16
    Posts
    282

    Re: Extract list of Names to display only once

    Thanks Glenn. Much appreciated. I still have a couple of issues.

    1) If you scroll down in "1st_XI" sheet you will see there are more names that I need to add
    2) I've noticed the name range formula does not work for google sheets. I assume this is going to cause issues.

  19. #19
    Forum Contributor
    Join Date
    08-19-2012
    Location
    Sydney
    MS-Off Ver
    MS Office 365/16
    Posts
    282

    Re: Extract list of Names to display only once

    Rorya,

    This actually works well. I can break it down to each range I am looking to extract.

    Many thanks

+ 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] Transpose Data and extract text using a list of names
    By Costasg in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-11-2018, 07:40 AM
  2. [SOLVED] Extract names from list only once
    By Immortal2014 in forum Excel General
    Replies: 7
    Last Post: 09-26-2017, 03:13 PM
  3. [SOLVED] Extract first and last names from a list of names
    By Macfool in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-12-2017, 06:44 AM
  4. Replies: 7
    Last Post: 12-23-2015, 07:43 AM
  5. Need a formula to extract names from a master list
    By Anies in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 02-12-2014, 02:24 PM
  6. [SOLVED] How to extract text from a list of names
    By busygurl in forum Excel General
    Replies: 3
    Last Post: 09-10-2013, 02:51 AM
  7. Extract client names from the list - vba
    By ebin charles in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 06-29-2011, 05:39 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