PDA

View Full Version : Solved: Create an Array from a Filled Range

asingh
03-04-2007, 10:28 AM
Hi,

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...

thanks a lot..for the help...

regards,

asingh

Bob Phillips
03-04-2007, 12:04 PM
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.

asingh
03-04-2007, 12:39 PM
This is working perfect..I have a unique list from A column..based of my logic criteria..from B column..and NO VBA was used...........!

XLD...You are great..and I just love this forum......!

regards,

asingh