+ Reply to Thread
Results 1 to 19 of 19

Create a list that contains duplicates and unique names and no blanks

  1. #1
    Registered User
    Join Date
    02-05-2015
    Location
    Massachusetts
    MS-Off Ver
    2013
    Posts
    14

    Create a list that contains duplicates and unique names and no blanks

    I have a list that contains duplicates, unique names and blanks. I want to create another one with the duplicates and unique names and no blanks. Column AH won’t expand much beyond row 15.

    AF AH
    4 Customer 1 Customer 1
    5 Customer 4 Customer 4
    6 Customer 2 Customer 2
    7 Customer 3 Customer 3
    8 Customer 4 Customer 4
    9 Customer 5 Customer 5
    10 Customer 6
    11 Customer 7
    12
    13
    14
    15
    16
    17
    18
    19
    20 Customer 1
    21 Customer 4
    22 Customer 2
    23 Customer 3
    23 Customer 6
    24 Customer 7
    25
    26
    27
    28
    29
    30
    Continues 243

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

    Re: Create a list that contains duplicates and unique names and no blanks

    Try this...

    Data Range
    AF
    AG
    AH
    4
    Data 4
    ------
    Data 4
    5
    Data 3
    Data 3
    6
    Data 16
    Data 16
    7
    Data 5
    8
    Data 20
    9
    Data 5
    Data 15
    10
    Data 20
    11
    Data 20
    Data 14
    12
    Data 19
    13
    14
    15
    16
    17
    Data 15
    18
    Data 20
    19
    Data 14
    20
    Data 19

    This array formula** entered in AH4:

    =LOOKUP("zzzzz",CHOOSE({1,2},"",INDEX(AF:AF,SMALL(IF(AF$4:AF$20<>"",ROW(AF$4:AF$20)),ROWS(AH$4:AH4)))))

    ** 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.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    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,900

    Re: Create a list that contains duplicates and unique names and no blanks

    If you'd prefer to have the list alphabetically sorted you can have that, too; but it's a bit more complicated and also uses an array formula. I also used a named range (CTRL F3) to make the long formula a bit shorter!! Drag it down as far as you need...
    Attached Files Attached Files
    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

  4. #4
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Create a list that contains duplicates and unique names and no blanks

    This also works
    Please Login or Register  to view this content.
    ARRAY formula is used

    To enter ARRAY formula
    Paste the formula
    Press F2
    Press Ctrl+Shift+Enter keys together.
    Attached Files Attached Files

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

    Re: Create a list that contains duplicates and unique names and no blanks

    Quote Originally Posted by kvsrinivasamurthy View Post
    =IFERROR(INDEX($AF$4:$AF$25,SMALL(IF(($AF$4:$AF$25<>"")*(COUNTIF($AH$3:$AH3,$AF$4:$AF$25)=0),ROW($AF$4:$AF$25),""),1)-ROW($AF$4)+1),"")
    The OP's profile says they're using Excel 2003 which is why I didn't use IFERROR in my suggestion.

  6. #6
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Create a list that contains duplicates and unique names and no blanks

    Sorry, revised formula
    Please Login or Register  to view this content.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    02-05-2015
    Location
    Massachusetts
    MS-Off Ver
    2013
    Posts
    14

    Re: Create a list that contains duplicates and unique names and no blanks

    I have to apologize I acted in haste, could you please take a look at the attached. This explains in much more detail what I am trying to accomplish with the customer list and now also the dates. Thank you all for your support. [ATTACH=CONFIG]

  8. #8
    Registered User
    Join Date
    02-05-2015
    Location
    Massachusetts
    MS-Off Ver
    2013
    Posts
    14

    Re: Create a list that contains duplicates and unique names and no blanks

    I posted instead of replying, if you could would you take a look at the attachment. Again I acted in haste when I posted.

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

    Re: Create a list that contains duplicates and unique names and no blanks

    Sorry, I don't understand.

    Please update your profile to include the most recent version(s) of Excel that you use. This helps us as we can make suggestions based on the specific version(s) you use. Some things may work in one version but not another version.
    Last edited by Tony Valko; 06-18-2015 at 07:30 PM.

  10. #10
    Registered User
    Join Date
    02-05-2015
    Location
    Massachusetts
    MS-Off Ver
    2013
    Posts
    14

    Re: Create a list that contains duplicates and unique names and no blanks

    Hi-sorry about that, but for some reason my profile will not update. I am using 2013.

  11. #11
    Registered User
    Join Date
    02-05-2015
    Location
    Massachusetts
    MS-Off Ver
    2013
    Posts
    14

    Re: Create a list that contains duplicates and unique names and no blanks

    HI-I have revised the attachment, given an explanation on the attachment what I am trying do with afore mention list and what the formula given is doing.

  12. #12
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Create a list that contains duplicates and unique names and no blanks

    1 Select the whole table, till last row.
    2 Remove the duplicates Data
    Remove Duplicates
    Check customer, ID, REG Hours, Dates
    OK
    3 Sort Data
    Sort
    Date, ID
    OK

    4 Result

    Copy the table to other range and try it.

  13. #13
    Registered User
    Join Date
    02-05-2015
    Location
    Massachusetts
    MS-Off Ver
    2013
    Posts
    14

    Re: Create a list that contains duplicates and unique names and no blanks

    That's exactly what I want, thank you very much. Now just one question is there a way to do it with a formula? I am doing this for my kid and I'm trying to make it simple as possible for him and me .. Thanks again.

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

    Re: Create a list that contains duplicates and unique names and no blanks

    Quote Originally Posted by Whard42 View Post
    is there a way to do it with a formula?
    I'm still not sure what you're wanting to do with this.

    Can you post a SMALL sample file that shows your data and includes what result you expect?

  15. #15
    Registered User
    Join Date
    02-05-2015
    Location
    Massachusetts
    MS-Off Ver
    2013
    Posts
    14

    Re: Create a list that contains duplicates and unique names and no blanks

    Hi, sorry for the delay. I have much more information on the attached. The attached also is shown the results in the customer table for the formulas used.

    Underneath the customer table I have shown another table and the way the customer table, should look with the listed employees. Also a scenario of employee worked location.

    Please delete if need be, my table and explanation.
    Attached Files Attached Files

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

    Re: Create a list that contains duplicates and unique names and no blanks

    Sorry, I have a file size download limit. Also, I won't download files that contain VBA code.

  17. #17
    Registered User
    Join Date
    02-05-2015
    Location
    Massachusetts
    MS-Off Ver
    2013
    Posts
    14

    Re: Create a list that contains duplicates and unique names and no blanks

    I removed the macros sorry about that, didn't think of it until after I sent it. I also reduced the file size.Hope you can help.

    If the file is still to large let me know I can remove more. Just wanted to leave a little more info.
    Attached Files Attached Files

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

    Re: Create a list that contains duplicates and unique names and no blanks

    Sorry, I don't understand.

  19. #19
    Registered User
    Join Date
    02-05-2015
    Location
    Massachusetts
    MS-Off Ver
    2013
    Posts
    14

    Re: Create a list that contains duplicates and unique names and no blanks

    Ok. Thank you for trying to 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. Replies: 8
    Last Post: 06-15-2016, 09:53 AM
  2. Formula to create a list of unique values / remove duplicates
    By TicklyTigger in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-12-2014, 08:39 AM
  3. Replies: 3
    Last Post: 06-25-2014, 03:28 AM
  4. [SOLVED] Looking to create a list of unique names that match criteria without using an array
    By john dalton in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 03-28-2014, 04:28 AM
  5. [SOLVED] Remove duplicates to create list of unique values?
    By hulayogi in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-17-2013, 01:46 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