Selecting unique occarences from a list

[raala12]

Hi,
I have a List like this (a large one in practical)

.... A B
1 cat dog
2 rat cat
3 bat cat
4 cat rat

At the bottom I want to generate an array which gives what are the text entries of this list.(ignore multiple occurrences)

so the answer should be

cat
rat
bat
dog

how to do this?

[SamuelT]

Hi,

One option would be to copy all columns into a single column, then go Data > Remove Duplicates. This will leave you with one occurence of all the unique values.

HTH,

SamuelT

[DonkeyOte]

How big is the real file ?

In these situations I'm afraid simplifying the example is unlikely to lead to a practical solution as scalability will be key.

If your matrix is big a VBA based route is perhaps most sensible given formulae would be inefficient ... you might want to use a Dictionary object in the VBA to build the collection of unique "keys".

EDIT: SamuelT's approach is also excellent if you have the capacity to list in a single contiguous vector.

[raala12]

Hi,
I wanted to do it by an equation. not manually clicking the UI.
Ths is a VBA solution given by an Expert called MickG

Please Login or Register  to view this content.

Thankful to Mick this works fine but don't u have a solution without macros.

[DonkeyOte]

A formula solution will be possible it just won't be efficient and you imply you have a big data set - if that's the case you really don't want to be using formulae.

Using your very basic matrix example - let's assume that resides in A1:B4 with final list in A7 onwards

A6: =SUMPRODUCT((A1:B4<>"")/COUNTIF(A1:B4,A1:B4&""))
B6: blank

the above gives us the count of unique items and allows to avoid needless calculations

A7: =IF(ROWS(A$7:A7)>$A$6,"",MIN(IF(ISNA(MATCH($A$1:$B$4,$B$6:$B6,0)),ROW($A$1:$B$4)+(COLUMN($A$1:$B$4)/10))))
confirmed with CTRL + SHIFT + ENTER
copied down to say A13 (8 possible unique values given matrix)

B7:
=IF($A7="","",INDEX($A$1:$B$4,INT($A7),MOD($A7,1)*10))
copied down to say B13

[djvice]

Please Login or Register  to view this content.

How can I set the Range to 2 worksheets? Sheet1.Range(A:A) and Sheet2(Range(B:B)