Just another option to play around with ..
Assume the sample table below is
in Sheet1, cols A to C, data from row2 down
where the key criteria col is Value in col C
(i.e. we want only the rows where col C is 1)
Type Desc Value
Evid1 Text1 2
Evid2 Text2 1
Evid3 Text3 2
Evid4 Text4 0
Evid5 Text5 3
Using an empty col to the right, say col E?
Put in E2: =IF(AND(ISNUMBER(C2),C21),ROW(),"")
Copy down by as many rows as data is expected
in the table, say down to E500?
(can copy down ahead of expected data input)
In Sheet2
------------
With the same col headers in A1:C1, viz.:
Type Desc Value
Put in A2:
=IF(ISERROR(SMALL(Sheet1!$E:$E,ROWS($A$1:A1))),"", INDEX(Sheet1!A:A,MATCH(SMA
LL(Sheet1!$E:$E,ROWS($A$1:A1)),Sheet1!$E:$E,0)))
Copy A2 across to C2, fill down by as many rows
as was done in col E in Sheet1, i.e. down to C500
Cols A to C will extract only those rows from Sheet1
where the Value col (col C) contains values 1,
and doing so without any blank rows in-between
(lines will shift up)
For the sample data in Sheet1, you'll get:
Type Desc Value
Evid1 Text1 2
Evid3 Text3 2
Evid5 Text5 3
< rest are "blanks"
Adapt to suit ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Marc" wrote in message
...
I work at a crime laboratory and produce monthly stats on the types of
evidence analyzed. The master list is about a hundred items. Some are
encountered more than others. Does anyone know a function that will
return a
category if the value is greater than one and skip a category if it has a
zero value. This would be the montly report. I've tried using an IF
statement to no avail. Any help would be appreciated. Thanks in advance.
|