+ Reply to Thread
Results 1 to 16 of 16

Create a list from a list

  1. #1
    Forum Contributor mikehk's Avatar
    Join Date
    09-05-2017
    Location
    Hong Kong
    MS-Off Ver
    Office 365
    Posts
    483

    Create a list from a list

    I use COUNTIF function but that returns a number, how do I create a list from a list
    for example, please see the attached file.

    I have in list wherein the names are repeated
    Joe
    Smith
    John
    Joe
    Mary
    Joe


    I would like to see the list as :
    Joe
    Smith
    John
    Mary


    Thank you
    [sample file attached]
    Attached Files Attached Files

  2. #2
    Forum Contributor mikehk's Avatar
    Join Date
    09-05-2017
    Location
    Hong Kong
    MS-Off Ver
    Office 365
    Posts
    483

    Re: Create a list from a list

    Got it.... Data | Advanced Filter, helps making the list.

    Thank you

  3. #3
    Forum Contributor mikehk's Avatar
    Join Date
    09-05-2017
    Location
    Hong Kong
    MS-Off Ver
    Office 365
    Posts
    483

    Re: Create a list from a list

    sorry, I need to match ID with names which I can't do with
    advanced filter. Please see the attached file.

    Thank you
    Attached Files Attached Files

  4. #4
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Create a list from a list

    Select the data, then Click on Data (on menu Bar) > remove Duplicates (Tool Bar) > Clear Check Box if you do not select headers > OK
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  5. #5
    Forum Contributor mikehk's Avatar
    Join Date
    09-05-2017
    Location
    Hong Kong
    MS-Off Ver
    Office 365
    Posts
    483

    Re: Create a list from a list

    But I need the duplicates!
    Please see the sample file in [post #3]

  6. #6
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Create a list from a list

    Did you tried, It will give the result as you desired but they are not is Ascending order
    After doing as suggested in Post No 4, you need to sort it.

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Create a list from a list

    Your results don't make complete sense to me, but attached is a Power Query solution. If it's what you want and you want to know exactly how to do it, let me know.

    Excel 2016 (Windows) 32 bit
    H
    I
    3
    ID Name
    4
    ABC John
    5
    ABC Smith
    6
    ABC Joe
    7
    MNO Joe
    8
    OCT Joe
    9
    PPS John
    10
    QQQ John
    11
    XYZ Mary
    12
    XYZ Smith
    13
    XYZ Ricky
    Sheet: Sheet1

    M code:

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by AliGW; 02-14-2019 at 02:17 AM.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  8. #8
    Forum Contributor mikehk's Avatar
    Join Date
    09-05-2017
    Location
    Hong Kong
    MS-Off Ver
    Office 365
    Posts
    483

    Re: Create a list from a list

    AliGW, you are right....I made a mistake, I didn't make the demo file correctly. my apologies.
    Please find attached herewith a new sample file.

    Thank you again.
    Attached Files Attached Files

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Create a list from a list

    Did you look at my suggestion? You have not commented on it at all, which I find rather odd.

  10. #10
    Forum Contributor mikehk's Avatar
    Join Date
    09-05-2017
    Location
    Hong Kong
    MS-Off Ver
    Office 365
    Posts
    483

    Re: Create a list from a list

    Oops! sorry, I didn't look into power query solution as I have no idea how to make it to work,
    and to begin with I didn't explain my query correctly..but the file in post #8 is the one I need
    the solution for.... I prefer simple formulas then VBA as no time to learn new tricks

  11. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Create a list from a list

    So you have shifted the goalposts again and are not interested in learning a bit of Power Query (it's NOT VBA!). OK, that's fine. I have run out of time now, anyway. Hope you get a solution soon,

  12. #12
    Forum Contributor mikehk's Avatar
    Join Date
    09-05-2017
    Location
    Hong Kong
    MS-Off Ver
    Office 365
    Posts
    483

    Re: Create a list from a list

    Oh no...haven't shifted my goalposts from learning, want to be good at excel first before moving on to learning VBA,
    please let me know how I can use power query, may be later when you have time.
    Weekend coming up and need to wrap up this file ...next week will be in London and Reading

  13. #13
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Create a list from a list

    H3=IFERROR(INDEX($A:$A,MATCH(0,INDEX(COUNTIFS(H$2:H2,$A$1:$A$80,I$2:I2,$D$1:$D$80),0),0)),"")
    I3=IFERROR(INDEX($D:$D,MATCH(0,INDEX(COUNTIFS(H$2:H2,$A$1:$A$80,I$2:I2,$D$1:$D$80),0),0)),"")
    Try this, copy and paste towards down

  14. #14
    Forum Contributor mikehk's Avatar
    Join Date
    09-05-2017
    Location
    Hong Kong
    MS-Off Ver
    Office 365
    Posts
    483

    Re: Create a list from a list

    Thank you so much sir...this works Highly appreciate.

  15. #15
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Create a list from a list

    you can do it with remove duplicates option as said in above post

  16. #16
    Forum Contributor mikehk's Avatar
    Join Date
    09-05-2017
    Location
    Hong Kong
    MS-Off Ver
    Office 365
    Posts
    483

    Re: Create a list from a list

    Thank you samba_ravi, in fact your solution in post #4 is simple and much faster than the formula!
    Thanks again, highly appreciate.


    [SOLVED]

+ 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. Vlookup - create new list from existing list based on partial match
    By unknown_brother in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 05-15-2018, 02:31 AM
  2. [SOLVED] Create an Excel Drop Down list with Search Suggestions ( Serachable Dropdown list )
    By PMH2384 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-11-2016, 12:03 AM
  3. Create unique list from data but listed in order based on a different list
    By Groovicles in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-24-2016, 12:54 PM
  4. Replies: 1
    Last Post: 05-12-2016, 09:29 AM
  5. Replies: 0
    Last Post: 12-16-2015, 05:50 AM
  6. Replies: 4
    Last Post: 11-05-2014, 09:38 AM
  7. Replies: 2
    Last Post: 08-01-2012, 01:24 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