Make unique list from a cells in one row

**biber** · 04-01-2012, 04:35 PM

Hi everyone.
I need a solution for making a unique list for cells from one row.
In the pic C6:AP6 are some codes. I want to sort those codes with formula like shown in cells AR:AU. Advance filtering doesn't work in this case.

I tried to find answer on many pages and nothing. Pls help. Thanks in advance.

situation.gif

**Richard Buttrey** · 04-01-2012, 04:56 PM

Hi,

It's not clear (at least to me) exactly what you want.

You use terms like 'unique list', 'sorting; and 'filtering'.

Please upload a workbook. It is always easier to advise if we can see your request in its context.

Show a before and after situation and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

Please don't upload pictures. None of us is inclined to recreate your data when you have a workbook available - see guidelines in the Rules area of this forum.

Regards

**Haseeb Avarakkan** · 04-01-2012, 07:19 PM

If C6:AP6 is a valid numbers, enter in

AR6,

=MAX(C6:AP6)

In AS6,

=LARGE($C6:$AP6,1+COUNTIF($C6:$AP6,">="&N(AR6)))

Then copy AS6 to AT6

**biber** · 04-04-2012, 11:18 AM

Originally Posted by Haseeb A

If C6:AP6 is a valid numbers, enter in

AR6,

=MAX(C6:AP6)

In AS6,

=LARGE($C6:$AP6,1+COUNTIF($C6:$AP6,">="&N(AR6)))

Then copy AS6 to AT6

This sounded like a good solution, but when I tried to execute formula 'the formula you typed contains an error' pop-up.
The thing is in the future I will input some different working codes in this table, and it would be great that some formula recognize this entry and index it in the green colored cells. By now I have input 3 working codes that would be great to be indexed as a unique entry making it a unique list of working codes that appears in 'radni nalog' column.

Here is the example table
example table.xlsx

**Pete_UK** · 04-04-2012, 07:53 PM

You can put this array* formula in cell AQ1:

=IFERROR(INDEX($E$1:$AO$1,MATCH(0,IF($E$1:$AO$1<>"",COUNTIF($AP$1:AP1,$E$1:$AO$1),1),0)),"")

and then copy it across (e.g. to AV1). It will give you unique values from the range E1 to AO1, ignoring blanks.

*An array formula must be committed using the key combination of Ctrl-Shift-Enter (CSE) instead of the usual <Enter>. If you do this correctly then Excel will wrap curly braces { } around the formula when viewed in the formula bar, but you must not type these yourself. If you need to amend the formula subsequently, you must use CSE again.

Hope this helps.

Pete