Look-Up Question

[carl]

I have a 2 columns of data

ColA ColB
-1 ABC
0 DEF
1 HIJ
-5 KLM

Is it possible, on another worksheet, to have only the
rows that have a negative value in ColA visible with no
blank rows between them - kind of like this...

2nd Worksheet

ColA ColB
-1 ABC
-5 KLM

Thank you in advance.

 

[Aladin Akyurek]

Two options...

1) Use Advanced Filter with a computed criteria;
2) A formula system:

Assuming that A2:B6 houses your sample (with labels added)...

{"X","Y";-1,"ABC";0,"DEF";1,"HIJ";-5,"KLM"}

where X and Y are labels.

C2 must house a 0.

In C3 enter & copy down:

=IF(A3<0,LOOKUP(9.99999999999999E+307,$C$2:C2)+1,"")

In D1 enter:

=LOOKUP(9.99999999999999E+307,C:C)

In D3 enter & copy down:

=IF(ROW()-ROW(D$3)+1<=$D$1,MATCH(ROW()-ROW(D$3)+1,C:C),"")

In E3 enter & copy across to F3 then down:

=IF(N(D3),INDEX(A:A,$D3),"")

 

[Stephen Dunn]

Hi Carl,

in cell A1 of the second sheet:

=IF(ROW()-ROW($1:$1)+1>COUNTIF('Sheet(1)'!$A$1:$A$4,"<0"),"",INDEX('Sheet(1)
'!$A$1:$A$4,SMALL(IF('Sheet(1)'!$A$1:$A$4<0,ROW('Sheet(1)'!$A$1:$A$4)-CELL("
ROW",'Sheet(1)'!$A$1:$A$4)+1),ROW()-ROW($1:$1)+1)))

as an array formula (hold Ctrl+Shift when you press Enter).

Of course 'Sheet(1)'!$A$1:$A$4 should be changed to reflect the actual
location of your data.

If you need the result in a different cell to A1 change ROW($1:$1) to refer
to the row that you type it into. For instance if you need it in D4 use:


=IF(ROW()-ROW($4:$4)+1>COUNTIF('Sheet(1)'!$A$1:$A$4,"<0"),"",INDEX('Sheet(1)
'!$A$1:$A$4,SMALL(IF('Sheet(1)'!$A$1:$A$4<0,ROW('Sheet(1)'!$A$1:$A$4)-CELL("
ROW",'Sheet(1)'!$A$1:$A$4)+1),ROW()-ROW($4:$4)+1)))


In the next cell (B1 for my example) use:

=IF(A1="","",VLOOKUP(A1,'Sheet(1)'!$A$1:$B$4,2,0))

which is not an array formula.

This assumes that all of the negative numbers in your list are unique.
Otherwise you can use:

=IF(A1="","",INDEX('Sheet(1)'!$B$1:$B$5,SMALL(IF('Sheet(1)'!$A$1:$A$5<0,ROW(
'Sheet(1)'!$A$1:$A$5)-CELL("ROW",'Sheet(1)'!$A$1:$A$5)+1),ROW()-ROW($1:$1)+1
)))

which is an array formula.

Now copy A1:B1 down as far as necessary.


Steve D.