Announcement

Collapse
No announcement yet.

Sort List and Remove Duplicates using a formula

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

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

  • #2
    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.
    Regards

    Fotis

    . Array(CSE) { }, formulae are confirmed with CONTROL+SHIFT+ENTER.

    . -Replace commas ( , ) with semicolons ( ; )-or vice versa in formulae, if your locale setting demands.

    .--Don't attach a screenshot--Just attach your Excel file!

    .--KISS(Keep it simple Stupid)

    Comment

    Working...
    X