Consulting

Results 1 to 6 of 6

Thread: Solved: Create an Array from Filled Range - Part II

  1. #1
    VBAX Mentor asingh's Avatar
    Joined
    Jul 2005
    Posts
    307
    Location

    Solved: Create an Array from Filled Range - Part II

    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

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    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.

  3. #3
    VBAX Mentor asingh's Avatar
    Joined
    Jul 2005
    Posts
    307
    Location
    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

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    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

  5. #5
    VBAX Mentor asingh's Avatar
    Joined
    Jul 2005
    Posts
    307
    Location
    Its working perfect now...just a quick..questions...why the &"" has been placed in the formulas, in the COUNTIF segment...?

    thanks and regards,

    asingh

  6. #6
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •