First Occurences

[ExcelMonkey]

thanks frank. Assuming the data is still in column A. Lets say th
data in column A is random draw on 50 different numbers(1-50). An
lets also say that there are 1000 random draws or rows. You will se
the number 1- 50 repeated randomly throughout the colum. What I wan
to do is in column B have a formula which calls up the first occurence
in column A and displays them in column in sequece without any empy row
like below

AXXB
7XX7
26X26
0XX0
2XX2
7XX45
26X1
45X4
1XX
4XX

Do you know how to do this?

Thank

 

[Frank Kabel]

Hi
use the following formulas:
B1: =A1
B2: enter the array formula (committed with CTRL+SHIFT+ENTER):
INDEX($A$1:$A$100,MATCH(0,COUNTIF(B$1:B1,$A$1:$A$100)+70000*ISBLANK($A$
1:$A$100),0))
and copy this down.

If you have 1000 rows this formula approach could be a little bit slow.
In this case try using the advanced filter:
· Select any cell in your data range (e.g. cell A2)

· Goto the menu ‘Data – Filter – Advanced Filter’

· In the textbox for the ‘List range’ select the column(s) from
which you want to create the list of unique entries

· Leave the textbox for the criteria range empty and choose
‘Copy to another location’

· Check ‘Unique entries only’ at the bottom of this dialog

· Click ‘OK’

 

[Alan Beban]

Hi
use the following formulas:
B1: =A1
B2: enter the array formula (committed with CTRL+SHIFT+ENTER):
INDEX($A$1:$A$100,MATCH(0,COUNTIF(B$1:B1,$A$1:$A$100)+70000*ISBLANK($A$
1:$A$100),0))
and copy this down.

If you have 1000 rows this formula approach could be a little bit slow.
In this case try using the advanced filter:
· Select any cell in your data range (e.g. cell A2)

· Goto the menu ‘Data – Filter – Advanced Filter’

· In the textbox for the ‘List range’ select the column(s) from
which you want to create the list of unique entries

· Leave the textbox for the criteria range empty and choose
‘Copy to another location’

· Check ‘Unique entries only’ at the bottom of this dialog

· Click ‘OK’


--

In using Frank Kabel's suggestion of the advanced filter method, one
needs to designate a "Copy to:" range and manage the header row.

Alan Beban