ExcelBanter - Array Formulas

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Array Formulas (https://www.excelbanter.com/excel-worksheet-functions/141014-array-formulas.html)

I'm working on a Product Evaluation Checklist in which each item has a
checkbox (yes) indicating if that item is present. The list is quite
extensive; so I as the each item is checked/ unchecked I want a list
of all unchecked items generated at the bottom of the worksheet (as a
recap).
I have the checkbox linked to a cell that equals TRUE (let's call it
C2). I'm sure there's a better way to pass the value of the checkbox,
if so please let me know.

Worksheet layout:

_____Col A_____Col B____________Col C_____Col D
R1 ITEM ITEM in SPEC FALSE
R2 widget 1 Checkbox YES TRUE
R3 widget 2 Checkbox YES FALSE
R4 widget 3 Checkbox YES TRUE
etc. etc.

I would like the recap to list all items that are out of SPEC, this is
the idea method.
R200 widget 1
R201 widget 3
R202

I have the following array formula in the recap area {=IF($C$2:$C$4=$D
$1,$A$2:$A$4,"")}.
My results using the above example gives me the following results.
R200
R201
R202
However, if I uncheck the first item (Row2) only, I get this.
R200 widget 1
R201 widget 1
R202 widget 1

Please help.

Thanks,
Jin

Yes I've tried it without the {} and I get a #VALUE! message. I'm a
little confused with your formula, I assume this formula goes into the
recap cell (I mentioned above); if so I get an error message. Please
advice.

Thanks,

J

I'm a little confused with your formula,
I assume this formula goes into the recap cell

It's not a worksheet formula. It's a line of VBA code that will insert the
formula into the cell through a macro.

How many rows of data do you have? Roughly, how many items will meet the
criteria?

Biff

"jin" wrote in message
ups.com...
Yes I've tried it without the {} and I get a #VALUE! message. I'm a
little confused with your formula, I assume this formula goes into the
recap cell (I mentioned above); if so I get an error message. Please
advice.

Thanks,

J

So is the code entered as a Change Event (Private Sub)? I like to
avoid using VBA as the solution but if that's the only way that's
fine. I have approximately 150 rows/ items. The recap area will have
a lot less items listed; maybe 20 or so. The workaround I have is to
have each row in the recap area with this formula, =IF($O6=
$Q6,$A6,""). But I want to avoid having a 150 row recap area with
only 20 items scattered all over the place. I thought using an array
formula would be the best solution in which I could condense the recap
area with "out of spec items" on one single page (or so).
Thanks,
J

Try this:

Assume:

Widgets in the range A2:A10
Checkboxes in the range B2:B10
Linked cells in the range C2:C10

Enter this array** formula and copy down until you get blanks:

=IF(ROWS($1:1)<=COUNTIF(C$2:C$10,TRUE),INDEX(A$2:A $10,SMALL(IF(C$2:C$10,ROW(C$2:C$10)-MIN(ROW(C$2:C$10))+1),ROWS($1:1))),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Biff

"jin" wrote in message
oups.com...
So is the code entered as a Change Event (Private Sub)? I like to
avoid using VBA as the solution but if that's the only way that's
fine. I have approximately 150 rows/ items. The recap area will have
a lot less items listed; maybe 20 or so. The workaround I have is to
have each row in the recap area with this formula, =IF($O6=
$Q6,$A6,""). But I want to avoid having a 150 row recap area with
only 20 items scattered all over the place. I thought using an array
formula would be the best solution in which I could condense the recap
area with "out of spec items" on one single page (or so).
Thanks,
J

Thank you very much for your help. It works great. But I have
another question. Is there a way to copy the same array formula to 20
lines instead of 150 different array formulas entered in 150 lines?
If I have to use 150 different formulas in the recap area then I would
have to print out the additional blank pages. I just want to print
one page of the 20 or so items that are out of spec. I've tried
changing the ROWS($1:150) but that doesn't work. Maybe I'll need to
use VBA for this, what do you think?

Thanks,
J

Is there a way to copy the same array formula to 20 lines
instead of 150 different array formulas entered in 150 lines?

Why do you think you have to copy 150 different formulas in 150 different
lines?

I'm not sure what you mean by that.

Biff

"jin" wrote in message
s.com...
Thank you very much for your help. It works great. But I have
another question. Is there a way to copy the same array formula to 20
lines instead of 150 different array formulas entered in 150 lines?
If I have to use 150 different formulas in the recap area then I would
have to print out the additional blank pages. I just want to print
one page of the 20 or so items that are out of spec. I've tried
changing the ROWS($1:150) but that doesn't work. Maybe I'll need to
use VBA for this, what do you think?

Thanks,
J

If I want all 150 items possibly listed in the recap area (will
probably be 20 items only) then I would have to have your formula in
150 lines, right? But each line would have a different ROW, e.g.

In the recap area:
Each formula representing an item in the 150 list above, right?
R200 {=IF(ROWS($1:1)<=COUNTIF(C$2:C$10,TRUE),INDEX(A$2: A$10,SMALL(IF(C
$2:C$10,ROW(C$2:C$10)-MIN
(ROW(C$2:C$10))+1),ROWS($1:1))),"")}
R201 {=IF(ROWS($1:2)<=COUNTIF(C$2:C$10,TRUE),INDEX(A$2: A$10,SMALL(IF(C
$2:C$10,ROW(C$2:C$10)-MIN
(ROW(C$2:C$10))+1),ROWS($1:2))),"")}
R202 {=IF(ROWS($1:3)<=COUNTIF(C$2:C$10,TRUE),INDEX(A$2: A$10,SMALL(IF(C
$2:C$10,ROW(C$2:C$10)-MIN
(ROW(C$2:C$10))+1),ROWS($1:3))),"")}
ETC
R350 {=IF(ROWS($1:150)<=COUNTIF(C$2:C$10,TRUE),INDEX(A$ 2:A
$10,SMALL(IF(C$2:C$10,ROW(C$2:C$10)-MIN
(ROW(C$2:C$10))+1),ROWS($1:150))),"")}
I assume the ROWS($1:1) represents each line in the list of 150 items,
so if that's the case won't I need to have a different formula for
each item? Since you didn't have a $ in the second number of
ROWS($1:1) I would have 150 different formulas as I copy down. Maybe
I'm just confused.

Thanks for your help.
J

On May 1, 5:48 pm, "T. Valko" wrote:
Is there a way to copy the same array formula to 20 lines
instead of 150 different array formulas entered in 150 lines?

Why do you think you have to copy 150 different formulas in 150 different
lines?

I'm not sure what you mean by that.

Biff

"jin" wrote in message

s.com...

Thank you very much for your help. It works great. But I have
another question. Is there a way to copy the same array formula to 20
lines instead of 150 different array formulas entered in 150 lines?
If I have to use 150 different formulas in the recap area then I would
have to print out the additional blank pages. I just want to print
one page of the 20 or so items that are out of spec. I've tried
changing the ROWS($1:150) but that doesn't work. Maybe I'll need to
use VBA for this, what do you think?

Thanks,
J

If I want all 150 items possibly listed in the recap area (will
probably be 20 items only) then I would have to have your formula in
150 lines, right?

Right. The formula needs to be copied to enough cells that will meet the
criteria so all the results that meet that criteria will be returned. If
it's possible that all 150 items will meet the criteria then you need to
copy the formula to 150 cells.

But, you don't need 150 *different* formulas. You just need *1* formula
copied to 150 cells.

Just enter the *1* formula in a cell then you drag copy that formula down to
150 cells. It's the same formula, not 150 different formulas.

I assume the ROWS($1:1) represents each line in the list of 150 items

No, it doesn't. Don't "mess" with that!!!! That is simply a "counter". As
you drag copy down the "counter" will increment like it's supposed to.

Biff

"jin" wrote in message
oups.com...
If I want all 150 items possibly listed in the recap area (will
probably be 20 items only) then I would have to have your formula in
150 lines, right? But each line would have a different ROW, e.g.

In the recap area:
Each formula representing an item in the 150 list above, right?
R200 {=IF(ROWS($1:1)<=COUNTIF(C$2:C$10,TRUE),INDEX(A$2: A$10,SMALL(IF(C
$2:C$10,ROW(C$2:C$10)-MIN
(ROW(C$2:C$10))+1),ROWS($1:1))),"")}
R201 {=IF(ROWS($1:2)<=COUNTIF(C$2:C$10,TRUE),INDEX(A$2: A$10,SMALL(IF(C
$2:C$10,ROW(C$2:C$10)-MIN
(ROW(C$2:C$10))+1),ROWS($1:2))),"")}
R202 {=IF(ROWS($1:3)<=COUNTIF(C$2:C$10,TRUE),INDEX(A$2: A$10,SMALL(IF(C
$2:C$10,ROW(C$2:C$10)-MIN
(ROW(C$2:C$10))+1),ROWS($1:3))),"")}
ETC
R350 {=IF(ROWS($1:150)<=COUNTIF(C$2:C$10,TRUE),INDEX(A$ 2:A
$10,SMALL(IF(C$2:C$10,ROW(C$2:C$10)-MIN
(ROW(C$2:C$10))+1),ROWS($1:150))),"")}
I assume the ROWS($1:1) represents each line in the list of 150 items,
so if that's the case won't I need to have a different formula for
each item? Since you didn't have a $ in the second number of
ROWS($1:1) I would have 150 different formulas as I copy down. Maybe
I'm just confused.

Thanks for your help.
J

On May 1, 5:48 pm, "T. Valko" wrote:
Is there a way to copy the same array formula to 20 lines
instead of 150 different array formulas entered in 150 lines?

Why do you think you have to copy 150 different formulas in 150 different
lines?

I'm not sure what you mean by that.

Biff

"jin" wrote in message

s.com...

Thank you very much for your help. It works great. But I have
another question. Is there a way to copy the same array formula to 20
lines instead of 150 different array formulas entered in 150 lines?
If I have to use 150 different formulas in the recap area then I would
have to print out the additional blank pages. I just want to print
one page of the 20 or so items that are out of spec. I've tried
changing the ROWS($1:150) but that doesn't work. Maybe I'll need to
use VBA for this, what do you think?

Thanks,
J