Sorting unique data from a list - no filtering

**rreynol4** · 08-20-2012, 03:35 PM

Hello, I am working on a sorting sheet that works as follows:

1. The first three columns A:C provide the following: (A) lists and index number; (B) generates a non-blank/non-null number which is referenced from (C) the column that references the raw data from another sheet.
2. Column E is a return column which prints the non-zero items from Column B. Note that these items are presorted on another table.
3. Column F is the functioning column that performs a check for non-zero entries in Column B.

The problem:
I have my output data in Column H, which omits any errors. However, I would like column I to further sort Column H by excluding any duplicates. I am pretty sure this will involve some row counting, offsetting, and matching formulae, but I am not sure how I should go about organizing these formulae.

Thanks!

List 1.xlsx

**Pete_UK** · 08-20-2012, 07:31 PM

In the attached workbook I've set up a dynamic named range called List with this reference (in Name Manager on the Formulas tab):

=INDIRECT("'List 1'!$H$2:$H"&COUNTIF('List 1'!$H:$H,">0")+1)

This adjusts itself to however many values you have in column H.

Then in I2 I have this array* formula:

=IFERROR(INDEX(List,MATCH(0,COUNTIF($I$1:I1,List),0)),"")

which when copied down removes any duplicates from column H.

*An array formula needs to be committed using the key combination of Ctrl-Shift-Enter, not just Enter.

Hope this helps.

Pete