Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 2 of 2

Thread: Sort List and Remove Duplicates using a formula

  1. #1
    Join Date
    18th March 2012
    Posts
    174

    Sort List and Remove Duplicates using a formula

    Hi All,

    On sheet1, I have an array formula is colum 3 to sort and remove duplicate a list in column A:

    column A contains names from A2:A20

    In C3 coped down:
    {CSE formula}
    {=IFERROR(INDEX(nn,MATCH(0,COUNTIF(nn,"<"&nn)-SUM(COUNTIF(nn,C$1:C1)),0)),"")}

    nn is defined as:

    ='(sheet1)'!$A$2:INDEX('(sheet1)'!$A$2:$A$20,MATCH(REPT("z",255),'(sheet1)'!$A$2:$A$20))


    The problem I have is that this works perfectly when it is text only, but my data in column A has numbers, a mix of numbers and letters. It stops working as soon as I have a mix of items that contain text + numbers

    for example, some items in column A:

    a12245
    c14556
    7889987
    8990766
    000988888

    Does anyone know how to edit the formulas to make this work?

    thanks in advance!

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    31st December 2012
    Location
    Athens-Greece
    Posts
    139

    Re: Sort List and Remove Duplicates using a formula

    Is this works for you?

    =IFERROR(INDEX($A$2:$A$20,MATCH(0,COUNTIF($C$1:C1,$A$2:$A$20),0)),"")

    array(CSE) formula in C2 and copy down.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Remove duplicates from list
    By enggchintan in forum EXCEL HELP
    Replies: 7
    Last Post: October 26th, 2010, 00:57
  2. Remove All Duplicates From List
    By qwerty00 in forum EXCEL HELP
    Replies: 3
    Last Post: December 27th, 2007, 00:43
  3. Consolidating List Of Data To Remove Duplicates
    By bbromley in forum EXCEL HELP
    Replies: 1
    Last Post: March 1st, 2007, 04:01
  4. Sort & Remove Duplicates In Combobox
    By GuyGadois in forum EXCEL HELP
    Replies: 4
    Last Post: December 20th, 2006, 01:50
  5. Replies: 1
    Last Post: August 18th, 2003, 06:16

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
  •  
porno