Unique lists

[Sean Howard]

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

 

[Harald Staff]

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

 

[Guest]

In your given constrants using a pivot table will be the only option. Try
exploring it.. if you can't mail me at (e-mail address removed) 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]

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

 

[Harald Staff]

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

 

[Frank Kabel]

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

 

[Bob Phillips]

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)

 

[Sean Howard]

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?

 

[Max]

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)),Sheet1!E:E,0)),"",INDEX(She
et1!B:B,MATCH(SMALL(Sheet1!E:E,ROW(B1:B100)),Sheet1!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 ..