+ Reply to Thread
Results 1 to 7 of 7

Extracting only unique names from a worksheet

  1. #1
    Forum Contributor
    Join Date
    10-02-2021
    Location
    Belgium
    MS-Off Ver
    Excel 2019
    Posts
    180

    Extracting only unique names from a worksheet

    Hi,

    In sheet1, I have a list of sales to customers. Of course, if a customer buys more than one time, his name will appear more than one time.

    I am trying to figure out, how can I get a unique list in sheet 2 of all the customers ( only one row for each different customer)

    Thanks for helping,

    Daniel
    Last edited by danielfr; 11-27-2021 at 11:21 AM.

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: Extracting only unique names from a worksheet

    Well, a Pivot Table would be the easiest way (and doesn't necessarily need VBA).
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Expert
    Join Date
    01-25-2011
    Location
    Belgium, Alveringem
    MS-Off Ver
    Excel 2003, 2007, 365
    Posts
    1,418

    Re: Extracting only unique names from a worksheet

    the unique-function, but excel2016 doesn't provide that !
    With advanced filter, you can create an unique list on the same worksheet, with VBA on a different one
    otherwise with spill-formulas

    Best way is adding an example in excel for the last solution.
    Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

  4. #4
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Extracting only unique names from a worksheet

    In the Formula tab. Select "Name Manager".

    Then create the name nn using the formula

    =Sheet1!$A$2:INDEX(Sheet1!$A$2:$A$20000,COUNT(Sheet1!$A$2:A$20000),1)


    On Sheet 2

    A1 = Unique List
    A2 = =IFERROR(INDEX(nn,MATCH(0,COUNTIF(nn,"<"&nn)-SUM(COUNTIF(nn,A$1:A1)),0)),"")

    Finally fill A2 down untill you get empty cells.
    Attached Files Attached Files
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

  5. #5
    Forum Contributor
    Join Date
    10-02-2021
    Location
    Belgium
    MS-Off Ver
    Excel 2019
    Posts
    180

    Re: Extracting only unique names from a worksheet

    Hi Mehmetcik,

    Thank you so much for your help.

    I tried with your file. It works well but when I try a copy- paste -in sheet 2 from A2 down- with your formula, I am receiving the error message : "You can't change part of an array"

  6. #6
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Extracting only unique names from a worksheet

    Ok. Hopefully one of these will work for you.

    Try this.

    In the Formula tab. Select "Name Manager".

    Then create the name nn using the formula

    =Sheet1!$A$2:INDEX(Sheet1!$A$2:$A$20000,COUNT(Sheet1!$A$2:A$20000),1)


    On Sheet 2

    A1 = Unique List


    Enter this using Ctrl Shift Enter
    A2 = =IFERROR(INDEX(nn,MATCH(0,COUNTIF(nn,"<"&nn)-SUM(COUNTIF(nn,A$1:A1)),0)),"")
    Now Fill down.


    Or

    Enter this in A2:A200

    =IFERROR(INDEX(nn,MATCH(0,COUNTIF(nn,"<"&nn)-SUM(COUNTIF(nn,A$1:A1)),0)),"")


    Try these Macros. Once you have defined nn.
    Please Login or Register  to view this content.
    Or

    Please Login or Register  to view this content.
    Last edited by mehmetcik; 11-27-2021 at 04:05 PM.

  7. #7
    Forum Contributor
    Join Date
    10-02-2021
    Location
    Belgium
    MS-Off Ver
    Excel 2019
    Posts
    180

    Re: Extracting only unique names from a worksheet

    Again thank you so much for your amazing help.

+ 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. Create unique # of worksheets based on list count with unique names
    By Groovicles in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 05-24-2016, 09:51 AM
  2. Replies: 7
    Last Post: 12-23-2015, 07:43 AM
  3. Replies: 1
    Last Post: 10-06-2014, 09:44 AM
  4. Listing worksheet names and extracting specific row data
    By boatbabe in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-17-2011, 12:03 PM
  5. [SOLVED] Extracting Worksheet names within a spreadsheet
    By Sunny in forum Excel General
    Replies: 5
    Last Post: 08-10-2006, 12:25 PM
  6. Automatic saving of worksheet with unique names
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-08-2005, 09:05 AM
  7. [SOLVED] Extracting worksheet names....
    By johnT in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-04-2005, 10:06 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