asingh
03-08-2007, 07:07 AM
Hi,
I had posted a similiar request, sometime back and closed it after I got a superb, solution. I thought I would be able to modify it, but am not being able too...
Initially:
I had asked :
If I have a range from A1:A10, and I have another range B1:B10. Now B1:B10 cells either have a "1", or a "0", in them, corresponding, to A1:A10, based on a certain logic. How can I create another Array from B1:B10, which only contains the "1" values. [Or their row locations, so where ever a "1" is found, I pick up the ROW().
Basically I am trying to create a dynamic...unique cell range. Using SUMPRODUCT, and a NESTED IF + COUNTIF I have been able to get the unique cells from A1:A10, but how to move them to another range..so they are exactly below each other...and no blanks are displayed.
By the way...this has to be done..using on excel Native Function..NO VBA...:(
I Used :
=IF(SUMPRODUCT((COUNTIF(A2:$A$1062,A2)=1)*1)=0,"",A2)
to unique cells...
To which XLD had replied:
Select a range of cells, say H1:H10, and enter this array formula in the formula bar
=IF(ISERROR(SMALL(IF($B$1:$B$10=1,ROW($A1:$A10),""),ROW($A1:$A10))),"",
INDEX(A$1:A$10,SMALL(IF($B$1:$B$10=1,ROW($A1:$A10),""),ROW($A1:$A10))))
as it is an array formula, commit with Ctrl-Shift-Enter, not just Enter.
Now the new problem is:
I had placed the formula "=IF(SUMPRODUCT((COUNTIF(A2:$A$1062,A2)=1)*1)=0,"",A2)" in column "B", to pull out the unique values from "A", and flag them with a "1". To which I implemented XLD's solution, and got a unique range.
Now I want to merge these two functions..so I can directly..put it in any column extract out the unqiue cells.
I have initially written the formula :
[Merging XLDs solution, with my SUMPRODUCT formula]
=IF(ISERROR(SMALL(IF(SUMPRODUCT((COUNTIF(A$1:$A$1063,A$1)=1)*1)=1,ROW($A1:$ A1063),""),ROW($A1:$A1063))),"",
INDEX(A$1:A$1063,SMALL(IF(SUMPRODUCT((COUNTIF(A$1:$A$1063,A$1)=1)*1)=1,ROW( $A1:$A1063),""),ROW($A1:$A1063))))
as a CSE...it is "compiling" fine. But the result it gives is:
I get all the values from column "A", the column which is being evaluated for unique values.
Is there a constraint with SUMPRODUCT being merged into Array Formulas...?
The previous thread had been :
http://vbaexpress.com/forum/showthread.php?t=11735
thanks a lot for the help,
regards,
asingh
I had posted a similiar request, sometime back and closed it after I got a superb, solution. I thought I would be able to modify it, but am not being able too...
Initially:
I had asked :
If I have a range from A1:A10, and I have another range B1:B10. Now B1:B10 cells either have a "1", or a "0", in them, corresponding, to A1:A10, based on a certain logic. How can I create another Array from B1:B10, which only contains the "1" values. [Or their row locations, so where ever a "1" is found, I pick up the ROW().
Basically I am trying to create a dynamic...unique cell range. Using SUMPRODUCT, and a NESTED IF + COUNTIF I have been able to get the unique cells from A1:A10, but how to move them to another range..so they are exactly below each other...and no blanks are displayed.
By the way...this has to be done..using on excel Native Function..NO VBA...:(
I Used :
=IF(SUMPRODUCT((COUNTIF(A2:$A$1062,A2)=1)*1)=0,"",A2)
to unique cells...
To which XLD had replied:
Select a range of cells, say H1:H10, and enter this array formula in the formula bar
=IF(ISERROR(SMALL(IF($B$1:$B$10=1,ROW($A1:$A10),""),ROW($A1:$A10))),"",
INDEX(A$1:A$10,SMALL(IF($B$1:$B$10=1,ROW($A1:$A10),""),ROW($A1:$A10))))
as it is an array formula, commit with Ctrl-Shift-Enter, not just Enter.
Now the new problem is:
I had placed the formula "=IF(SUMPRODUCT((COUNTIF(A2:$A$1062,A2)=1)*1)=0,"",A2)" in column "B", to pull out the unique values from "A", and flag them with a "1". To which I implemented XLD's solution, and got a unique range.
Now I want to merge these two functions..so I can directly..put it in any column extract out the unqiue cells.
I have initially written the formula :
[Merging XLDs solution, with my SUMPRODUCT formula]
=IF(ISERROR(SMALL(IF(SUMPRODUCT((COUNTIF(A$1:$A$1063,A$1)=1)*1)=1,ROW($A1:$ A1063),""),ROW($A1:$A1063))),"",
INDEX(A$1:A$1063,SMALL(IF(SUMPRODUCT((COUNTIF(A$1:$A$1063,A$1)=1)*1)=1,ROW( $A1:$A1063),""),ROW($A1:$A1063))))
as a CSE...it is "compiling" fine. But the result it gives is:
I get all the values from column "A", the column which is being evaluated for unique values.
Is there a constraint with SUMPRODUCT being merged into Array Formulas...?
The previous thread had been :
http://vbaexpress.com/forum/showthread.php?t=11735
thanks a lot for the help,
regards,
asingh