PDA

View Full Version : Solved: Create an Array from Filled Range - Part II

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:

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

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

thanks a lot for the help,

regards,

asingh

Bob Phillips
03-09-2007, 05:13 AM
Okay, drop column B.

In C1, enter = A1

In C2, enter

=IF(ISERROR(MATCH(0,COUNTIF(\$F\$1:F1,\$A\$1:\$A\$20&""),0)),"",
INDEX(IF(ISBLANK(\$A\$1:\$A\$20),"",\$A\$1:\$A\$20),MATCH(0,COUNTIF(\$F\$1:F1,\$A\$1:\$A\$20&""),0)))

and copy down. This is astill an array formula, but not a block array formula as before, just a single cell array formula to copy down.

asingh
03-09-2007, 08:49 AM
Hi,

I Tried the following:

=IF(ISERROR(MATCH(0,COUNTIF(\$A\$2:A2,\$A\$2:\$A\$20&""),0)),"",INDEX(IF(ISBLANK(\$A\$2:\$A\$20),"",\$A\$2:\$A\$20),MATCH(0,COUNTIF(\$A\$2:A2,\$A\$2:\$A\$20&""),0)))

: where my data starts from cell A2. I placed this in column "C". Arrayed it..and dragged down. I am just getting a repetition of cell..!

What does ' \$A\$20&"" ' mean in the above formula...?

Still need help...!

thanks and regards,

Asingh

Bob Phillips
03-09-2007, 10:00 AM
C1: = A2

C2: =IF(ISERROR(MATCH(0,COUNTIF(\$C\$1:C1,\$A\$2:\$A\$20&""),0)),"",INDEX(IF(ISBLANK( \$A\$2:\$A\$20),"",\$A\$2:\$A\$20),MATCH(0,COUNTIF(\$C\$1:C1,\$A\$2:\$A\$20&""),0)))

and copy down

asingh
03-10-2007, 01:24 PM
Its working perfect now...just a quick..questions...why the &"" has been placed in the formulas, in the COUNTIF segment...?

thanks and regards,

asingh

Bob Phillips
03-10-2007, 09:42 PM
That is there in case there are any blanks in the source range, so that one (or more) item(s) don't get missed. Take it out and you will probably see no difference, but add a blank into the source range and you should see why.