I have a list which looks something like this :-

A
1 RTL
2 RTL
3 RTL
4 TV2
5 Viasat
6 Viasat
7 Viasat
8 Viasat
9 Duna
10 Duna
11 Duna
12 Duna
13 Duna
14 M1
15 M1
16 M1
17 M1
18 M1

and from that I need to generate a unique list that looks something like
this :-

A
1 RTL
2 TV2
3 Viasat
4 Duna
5 M1

The problem is that I DO NOT want to achieve this thru VBA but thru
standard Excel functions as I do not want to add any VB code to my
spreadsheet (the users will normally have their macro security set to
HIGH)

I cannot find in Excel any way to get the "1st unique value", "2nd
unique value", etc from a list and think that maybe using array formulae
may be the answer, but I do not know much about using them.

Any ideas

Sean

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Hi Sean

See Chip's page
http://www.cpearson.com/excel/duplicat.htm
on this. Consider also a pivot table
http://www.cpearson.com/excel/pivots.htm

HTH. Best wishes Harald

"Sean Howard" skrev i melding
...
I have a list which looks something like this :-

A
1 RTL
2 RTL
3 RTL
4 TV2
5 Viasat
6 Viasat
7 Viasat
8 Viasat
9 Duna
10 Duna
11 Duna
12 Duna
13 Duna
14 M1
15 M1
16 M1
17 M1
18 M1

and from that I need to generate a unique list that looks something like
this :-

A
1 RTL
2 TV2
3 Viasat
4 Duna
5 M1

The problem is that I DO NOT want to achieve this thru VBA but thru
standard Excel functions as I do not want to add any VB code to my
spreadsheet (the users will normally have their macro security set to
HIGH)

I cannot find in Excel any way to get the "1st unique value", "2nd
unique value", etc from a list and think that maybe using array formulae
may be the answer, but I do not know much about using them.

Any ideas

Sean

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

In your given constrants using a pivot table will be the only option. Try
exploring it.. if you can't mail me at I will tell you
how it can be done. It's a lengthy procedure to describe but very logical to
do. In case you can't do it I will mail you a worksheet where I have done a
similar thing. That would be easier than describing the method.

Vikrant

"Sean Howard" wrote:

I have a list which looks something like this :-

A
1 RTL
2 RTL
3 RTL
4 TV2
5 Viasat
6 Viasat
7 Viasat
8 Viasat
9 Duna
10 Duna
11 Duna
12 Duna
13 Duna
14 M1
15 M1
16 M1
17 M1
18 M1

and from that I need to generate a unique list that looks something like
this :-

A
1 RTL
2 TV2
3 Viasat
4 Duna
5 M1

The problem is that I DO NOT want to achieve this thru VBA but thru
standard Excel functions as I do not want to add any VB code to my
spreadsheet (the users will normally have their macro security set to
HIGH)

I cannot find in Excel any way to get the "1st unique value", "2nd
unique value", etc from a list and think that maybe using array formulae
may be the answer, but I do not know much about using them.

Any ideas

Sean

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Firstly thanks to Harald (I had already seen Chip's site) and Vikrant
for the PivotTable idea but that will not solve my problem by a long
way, reasons being :-
1) the PivotTable it would have to be refreshed during every
calculation.
2) the list would be in alphabetical order (I need the original order)
3) it seems like overkill to me

Close but no cigars boys

------
Sean

"If at first you don't succeed, go to sleep"

------
Sean

"If at first you don't succeed, go to sleep"

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

"Sean Howard" skrev i melding
...

Close but no cigars boys

I take it you didn't try the advanced filter technique on Chip's page. See
"filter unique records" on
http://www.contextures.com/xladvfilter01.html

HTH. Best wishes Harald

Hi
try the following formulas:
B1:
=A1
B2: enter the following array formula (committed wih CTRL+SHIFT+ENTER):
=INDEX(IF(ISBLANK($A$1:$A$20),"",$A$1:$A$20),MATCH (0,
COUNTIF(B$1:B1,$A$1:$A$20&""),0))

and copy this formula down

--
Regards
Frank Kabel
Frankfurt, Germany

"Sean Howard" schrieb im Newsbeitrag
...
I have a list which looks something like this :-

A
1 RTL
2 RTL
3 RTL
4 TV2
5 Viasat
6 Viasat
7 Viasat
8 Viasat
9 Duna
10 Duna
11 Duna
12 Duna
13 Duna
14 M1
15 M1
16 M1
17 M1
18 M1

and from that I need to generate a unique list that looks something
like
this :-

A
1 RTL
2 TV2
3 Viasat
4 Duna
5 M1

The problem is that I DO NOT want to achieve this thru VBA but thru
standard Excel functions as I do not want to add any VB code to my
spreadsheet (the users will normally have their macro security set to
HIGH)

I cannot find in Excel any way to get the "1st unique value", "2nd
unique value", etc from a list and think that maybe using array
formulae
may be the answer, but I do not know much about using them.

Any ideas

Sean

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

You might want to amend Frank's formula in B2 to

=IF(ISERROR(MATCH(0,COUNTIF(B$1:B1,$A$1:$A$20&""), 0)),"",INDEX(IF(ISBLANK($A
$1:$A$20),"",$A$1:$A$20),MATCH(0,COUNTIF(B$1:B1,$A $1:$A$20&""),0)))

to avoid the #N/As at the end - still Ctrl-Shift-Enter to commit

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Frank Kabel" wrote in message
...
Hi
try the following formulas:
B1:
=A1
B2: enter the following array formula (committed wih CTRL+SHIFT+ENTER):
=INDEX(IF(ISBLANK($A$1:$A$20),"",$A$1:$A$20),MATCH (0,
COUNTIF(B$1:B1,$A$1:$A$20&""),0))

and copy this formula down

--
Regards
Frank Kabel
Frankfurt, Germany

"Sean Howard" schrieb im Newsbeitrag
...
I have a list which looks something like this :-

A
1 RTL
2 RTL
3 RTL
4 TV2
5 Viasat
6 Viasat
7 Viasat
8 Viasat
9 Duna
10 Duna
11 Duna
12 Duna
13 Duna
14 M1
15 M1
16 M1
17 M1
18 M1

and from that I need to generate a unique list that looks something
like
this :-

A
1 RTL
2 TV2
3 Viasat
4 Duna
5 M1

The problem is that I DO NOT want to achieve this thru VBA but thru
standard Excel functions as I do not want to add any VB code to my
spreadsheet (the users will normally have their macro security set to
HIGH)

I cannot find in Excel any way to get the "1st unique value", "2nd
unique value", etc from a list and think that maybe using array
formulae
may be the answer, but I do not know much about using them.

Any ideas

Sean

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Thanks Frank, that is exactly what I was trying to accomplish.


Just a couple of questions

1) Is it possible to exclude blank cells from the unique list? I
forgot about that possibility in the original question.

2) What if the result had to look a bit like this :-
Order Unique
1 RTL
3 Viasat
4 Duna
where I would only want to see the 1st, 3rd and 4th unique strings from
the original list. Is that doable?


------
Sean

"If at first you don't succeed, go to sleep"

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

"Sean Howard" wrote
.....
1) Is it possible to exclude blank cells from the unique list? I
forgot about that possibility in the original question.

An option to play around with,
which will exclude blanks in the unique list ..

Meanings:
--------------
Uniques = 1st instance of the item in the list,
Duplicates = 2nd, 3rd, etc instances of the item in the list

Note that there's no case sensitivity distinction for uniques,
i.e. RTL = rtl = RtL (all are considered identical, not unique)

Assume the original data is
in Sheet1, in cols A and B,
-------------------------------------
data from row1 down, viz.:
(names in col B)

1 RTL
2 RTL
3 RTL
4 TV2
5 Viasat
6 Viasat
etc

Put in say, E1:
=IF(OR(COUNTIF($B$1:B1,B1)1,B1=""),"",ROW())

Copy E1 down by a safe "max" number of rows
that data will be expected in col B, down to say, E100?
(can copy down ahead of expected data input in cols A and B)

In Sheet2
-------------
Select B1:B100
(a range of the same size as col E in Sheet1)

Put in the *formula bar*:

=IF(ISERROR(MATCH(SMALL(Sheet1!E:E,ROW(B1:B100)),S heet1!E:E,0)),"",INDEX(She
et1!B:B,MATCH(SMALL(Sheet1!E:E,ROW(B1:B100)),Sheet 1!E:E,0)))

Array-enter the formula with CTRL+SHIFT+ENTER
instead of just pressing ENTER

Put in A1: =IF(B1="","",ROW())
Copy down to A100
(cover the same range as before)

The above will extract and sequentially number
the list of uniques from Sheet1's col B
w/o any blank rows in-between ..

2) What if the result had to look a bit like this :-
Order Unique
1 RTL
3 Viasat
4 Duna
where I would only want to see the 1st, 3rd and
4th unique strings from the original list. Is that doable?

In Sheet3
-------------
Assuming the "pre-defined" list of unique strings desired for
viewing from Sheet2 will be numbered in col A,
in A1 downwards, viz.:
1
3
4

Put in B1:
=IF(A1="","",VLOOKUP(A1,Sheet2!A:B,2,0))

Copy B1 down to B100
(to match the same range size as col E in Sheet1)

The above will return the results that you're after

Adapt to suit ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----