Find First Occurrence

[Wayne]

I need a function that will perform the following:

Given a range of (A1:A10) I want to extract the first
occurence of any number and paste the results into
another cell range.

For example:

Column A Column B

10 10
11 11
23 23
54 54
11
23
10
23

Once a number in the range is found and pasted into
Column B the number is ignored throughout the rest of the
range of Column A.

Thanks

 

[Domenic]

Hi Wayne,

B2, copied down until you get #N/A:

=INDEX($A$1:$A$8,MATCH(0,COUNTIF($B$1:B1,$A$1:$A$8),0))
entered using CTRL+SHIFT+ENTER

Hope this helps!

 

[JulieD]

Hi Wayne

if you have a look at chip pearson's website on duplicates -
http://www.cpearson.com/excel/duplicat.htm
he has an article on extracting unique entries which i think will meet your
requirements

Cheers
julieD

 

[Aladin Akyurek]

Let A2:A10 house the sample you provided (where A2 houses a label)...

{"X";10;11;23;54;11;23;10;23}

In B2 enter: 0 (required)

In B3 enter & copy down:

=IF((A3<>"")*ISNA(MATCH(A3,$A$2:A2,0)),LOOKUP(9.99999999999999E+307,$B$2:B2)
+1,"")

In C1 enter:

=LOOKUP(9.99999999999999E+307,B:B)

In C2 enter: Distinct X [ just a label ]

In C3 enter & copy down:

=IF(ROW()-ROW(C$3)+1<=$C$1,INDEX(A:A,MATCH(ROW()-ROW(C$3)+1,B:B)),"")

Column C will house the distinct list you're looking for.

 

[Domenic]

Hi Aladin,

I see that you've used your multi-formula approach.

I take it that my formula, while effective, is extremely inefficient
since it has to carry out the calculation numerous times in order to
return a result.

So do you usually discourage the use of such a formula?

 

[Aladin Akyurek]

Hi Aladin,

I see that you've used your multi-formula approach.

I take it that my formula, while effective, is extremely inefficient
since it has to carry out the calculation numerous times in order to
return a result.

So do you usually discourage the use of such a formula?

If efficiency counts, I'd say yes. BTW, Steve at Woody's tested the
array-formula approach against a macro. He found the former far too slow.
The approach I posted equals that macro in speed.

 

[hgrove]

Aladin Akyurek wrote...

If efficiency counts, I'd say yes. BTW, Steve at Woody's tested
the array-formula approach against a macro. He found the
former far too slow. The approach I posted equals that macro in
speed.

I wonder how the following variation on Domenic's approach would far
in terms of recalc speed. Given your A2:A10,

B3:
=A3

B4 [array formula]:
=IF(PRODUCT(COUNTIF(B$3:B3,A4:A$10)),"",
INDEX(A4:A$10,MATCH(0,COUNTIF(B$3:B3,A4:A$10),0)))

B4 filled down into B5:B10

 

[Domenic]

If efficiency counts, I'd say yes

I'm now starting to think more in terms of your approach whenever
possible. In fact, I've already posted two or three replies using a
variation on your approach.

BTW, Steve at Woody's tested the
array-formula approach against a macro. He found the former far too slow.
The approach I posted equals that macro in speed.

Interesting!