Excel Report Using IF Statement?

M

Marc

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

CLR

Hi Marc..............

Check out Data > Filter > AutoFilter, and sort on your column to show all
non-blanks

Vaya con Dios,
Chuck, CABGx3
 
M

Max

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),C2>1),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 ..
 
Top