Array subtitute

[Salman]

Is there a way that following could be used without an array

=IF($C6="","",IF(ISERROR(INDEX(Matrix!G$5:G$475,SMALL(IF(Matrix!$C$5:$C$475=$C6,ROW($1:$471)),COUNTIF('Matrix
REPORT'!$C$6:$C6,'Matrix
REPORT'!$C6)))),INDEX(Matrix!G$5:G$475,SMALL(IF(Matrix!$A$5:$A$475=$C6,ROW($1:$471)),COUNTIF('Matrix
REPORT'!$C$6:$C6,'Matrix
REPORT'!$C6))),INDEX(Matrix!G$5:G$475,SMALL(IF(Matrix!$C$5:$C$475=$C6,ROW($1:$471)),COUNTIF('Matrix REPORT'!$C$6:$C6,'Matrix REPORT'!$C6)))))

i have to get result for the same value twice or thrice, n array is taking
too much time bcz i have almost 150 columns with 450 rows

any help plz
regards

 

[Bob Phillips]

Not adapted to your condition, but here is a non-array approach

In C1: =IF(A1="","",IF(COUNTIF($A$1:A1,A1)>1,"",ROW()))
In D1: =IF(ISERROR(SMALL(C:C,ROW(A1))),"",
INDEX(A:A,MATCH(SMALL(C:C,ROW(A1)),C:C,0)))

copy C11 down

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Harlan Grove]

Not adapted to your condition, but here is a non-array approach

In C1: =IF(A1="","",IF(COUNTIF($A$1:A1,A1)>1,"",ROW()))
In D1: =IF(ISERROR(SMALL(C:C,ROW(A1))),"",
INDEX(A:A,MATCH(SMALL(C:C,ROW(A1)),C:C,0)))

copy C11 down

....

You col C formula produces the row number on the first instance of the
current row's col A value in col A from row 1 to the current row when
the col A value isn't ""; otherwise, it returns "". It'd be more
efficient to use

C1:
=IF(COUNT(1/(MATCH(A1,A$1:A1,0)=ROW())),ROW(),"")

since match could return upon finding the first instance rather than
process all of col C from row 1 to the current row.

Your col D formula doesn't do what the OP's formula does.

....

This formula appears to be entered into some cell in row 6 in the
'Matrix REPORT' worksheet. If so, and if the only error being trapped
is #NUM! when the SMALL call's 1st arg contains fewer number values
than its 2nd arg, then it could be rewritten as

=IF($C6="","",INDEX(Matrix!G$5:G$475,SMALL(
IF(IF(COUNTIF(Matrix!$C$5:$C$475,$C6)<COUNTIF($C$6:$C6,$C6),
Matrix!$A$5:$A$475,Matrix!$C$5:$C$475)=$C6,ROW($1:$471)),
COUNTIF($C$6:$C6,$C6))))

To me this begs the question whether Matrix!A5:A475 = C6 whenever
Matrix!C5:C475 = C6. If it does, simplify further to

=IF($C6="","",INDEX(Matrix!G$5:G$475,SMALL(
IF(Matrix!$A$5:$A$475=$C6,ROW($1:$471)),
COUNTIF($C$6:$C6,$C6))))

 

[Salman]

Thanks, it would be helpful i think so but it seems a bit complicated to me,
let me work on it, if i have any problem then i will come back and give you
a rescue call, thanks alot.

 

[Salman]

please look at the following and advise please that y i am not getting the
right values, this formula provide results but not correct, for first 4 rows
answer is correct but after that it is getting the values from next columns
which is not refered in this formula, please help

=IF(C6="","",IF(ISERROR(SUMPRODUCT((Matrix!$C$6:$C$536='Matrix
REPORT'!$C6)*(Matrix!$E$6:$E$536='Matrix
REPORT'!$E6)*Matrix!$H$6:$H$536)),SUMIF('SECTOR AVERAGE'!$B$5:$H$715,'Matrix
REPORT'!$C6,'SECTOR
AVERAGE'!$G$5:$G$715),SUMPRODUCT((Matrix!$C$6:$C$536='Matrix
REPORT'!$C6)*(Matrix!$E$6:$E$536='Matrix REPORT'!$E6)*Matrix!$H$6:$H$536)))