Array formula pulls back extra values...

[3G]

Hello-

This is a mutation of the killer array formula Luke helped me with in a different post. Basically, I want to pull back only the first occurence of Column A, based on the data entered in B3. When I copy down, it's pulling back multiples where they occur, when, I only want the first occurence of the values.


=INDEX('Data List062911'!A$1:A$131,SMALL(IF($B$3='Data List062911'!$G$1:$G$131,ROW('Data List062911'!$G$1:$G$131)-ROW('Data List062911'!$G$1)+1,""),ROWS('Data List062911'!$G$1:$G2)))


Current results look like this:

10000000

10000000

30000000

60000000

70000000

90000000

100000000


when I only want it to look like this:

10000000

30000000

60000000

70000000

90000000

100000000


Any thoughts?

 

[Luke M]

This is probably easier to accomplist using some helper columns in your original data table. First helper (let's say it's in col AA):

=A1&G1

Second helper (col AB:

=COUNTIF(AA$1:AA1,AA1)


Array formula then becomes:

=INDEX('Data List062911'!A$1:A$131,SMALL(IF(('Data List062911'!$AB$1:$AB$131=1)*($B$3='Data List062911'!$G$1:$G$131),ROW('Data List062911'!$G$1:$G$131)-ROW('Data List062911'!$G$1)+1,""),ROWS('Data List062911'!$G$1:$G2)))

 

[blanchir les dents]

try this


=INDEX('Data List062911'!A$1:A$131,SMALL(IF(('Data List062911'!$AB$1:$AB$131=1)*($B$3='Data List062911'!$G$1:$G$131),ROW('Data List062911'!$G$1:$G$131)-ROW('Data List062911'!$G$1)+1,""),ROWS('Data List062911'!$G$1:$G2)))


dents blanches