Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Sort List and Remove Duplicates using a formula

1. Senior Member
Join Date
18th March 2012
Posts
177

## 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?

Excel Video Tutorials / Excel Dashboards Reports

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.

Excel Video Tutorials / Excel Dashboards Reports

##### Users Browsing this Thread

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

#### 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