+ Reply to Thread
Results 1 to 8 of 8

Create unique list from dynamic list with dupes

  1. 03-10-2014, 07:08 PM #1
    kfryar
    kfryar is offline
    Registered User
    Join Date
    03-10-2014
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    42

    Create unique list from dynamic list with dupes

    Hi all,

    I have a dynamic list which contains dupes and am trying to create a second list with unique entries..

    My situation:

    I have a long list of companies on sheet1 A2:A800 (with duplicates)

    I need the refined list of companies on sheet2, starting from A2

    However, the list on sheet 1 is a dynamic worksheet that updates info from our CRM system, however the companies will always be in column A... There will be blanks so i guess if anyone knows how to also get a formula to incorporate ignoring those.

    I have tried...
    =IFERROR(INDEX(Table1[Introducer Company],MATCH(0,COUNTIF(A$2:A2,Table1[Introducer Company]),0)),"")

    then dragging it down, but that just gives me a list of zeros with circular referencing.

    I am using Excel 2010 if that makes a difference

    Any help!?
    Reply With QuoteRegister To Reply
  2. 03-10-2014, 08:29 PM #2
    AlKey
    AlKey is offline
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Create unique list from dynamic list with dupes

    Hi kfryar and welcome to the forum

    Try this array formula to extract unique values. This formula will ignore blanks.


    =IFERROR(INDEX($A$2:$A$800,MATCH(0,IF(ISBLANK($A$2:$A$800),"",COUNTIF(B$1:$B1, $A$2:$A$800)), 0)),"")



    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne
    Reply With QuoteRegister To Reply
  3. 03-10-2014, 08:46 PM #3
    kfryar
    kfryar is offline
    Registered User
    Join Date
    03-10-2014
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    42

    Re: Create unique list from dynamic list with dupes

    Thank you for your reply,

    I have put the below into the spreadsheet and got the {} around the formula, but it is still coming up with zeros...

    =IFERROR(INDEX(Sheet1!$A$2:$A$800,MATCH(0,IF(ISBLANK(Sheet1!$A$2:$A$800),"",COUNTIF(B$3:$B3,Sheet1!$A$2:$A$800)),0)),"")

    Not sure if i made an error somewhere
    Reply With QuoteRegister To Reply
  4. 03-10-2014, 09:06 PM #4
    kfryar
    kfryar is offline
    Registered User
    Join Date
    03-10-2014
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    42

    Re: Create unique list from dynamic list with dupes

    Thank you for your reply,

    I have put the below into the spreadsheet and got the {} around the formula, but it is still coming up with zeros...

    =IFERROR(INDEX(Sheet1!$A$2:$A$800,MATCH(0,IF(ISBLANK(Sheet1!$A$2:$A$800),"",COUNTIF(B$3:$B3,Sheet1!$A$2:$A$800)),0)),"")

    Not sure if i made an error somewhere
    Reply With QuoteRegister To Reply
  5. 03-10-2014, 09:06 PM #5
    tigeravatar
    tigeravatar is offline
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Create unique list from dynamic list with dupes

    Hello kfryar,

    Using the information provided in your original post, give this regular formula a try (no need to array-enter it):
    Please Login or Register  to view this content.
    Hope that helps,
    ~tigeravatar

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
    Reply With QuoteRegister To Reply
  6. 03-10-2014, 09:13 PM #6
    kfryar
    kfryar is offline
    Registered User
    Join Date
    03-10-2014
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    42

    Re: Create unique list from dynamic list with dupes

    Sorry for reposting my response... i guess i am struggling with both excel and "copy+paste" today, haha

    I am still getting zeros - and i can't for the life of me see why...

    Is the A$1:A1 meant to be where my new list is starting from? does it make a difference if that starting point is in a table at all?
    Reply With QuoteRegister To Reply
  7. 03-10-2014, 09:14 PM #7
    tigeravatar
    tigeravatar is offline
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Create unique list from dynamic list with dupes

    A1 should be the cell above where your formula starts. Otherwise the formula is referencing itself and causing circular reference errors
    Reply With QuoteRegister To Reply
  8. 03-10-2014, 09:20 PM #8
    kfryar
    kfryar is offline
    Registered User
    Join Date
    03-10-2014
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    42

    Re: Create unique list from dynamic list with dupes

    geez, what a doofus i am!

    THANK YOU SOO SOO MUCH... it worked!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Create new list based on data from another dynamic list
    By y_not in forum Excel General
    Replies: 6
    Last Post: 01-04-2013, 09:16 AM
  2. VBA: Create unique list based on latest entry in list
    By welchs101 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 06-08-2011, 09:55 AM
  3. Create a unique list from a list of duplicated dynamically?
    By Tommy1005 in forum Excel General
    Replies: 5
    Last Post: 01-12-2011, 08:49 AM
  4. Copy from list when different (create unique list with formulas)
    By pansovic in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-20-2010, 11:14 AM
  5. [SOLVED] Need to create unique list from list of multiple entries
    By Jeff in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-17-2005, 01:05 AM

Tags for this Thread

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1