create a separate list from checked items only in a workbook

B

Bob Woolbert

I want to create a separate list from only checked items in a workbook,
without empty lines between the items.
 
M

Max

One way .. try this option

Suppose you have
in Sheet1, cols A and B,
data from row2 down

Prod Desc Check
Item1 Text1 x
Item2 Text2
Item3 Text3 x
Item4 Text4 x
etc

where col C is used to check (type: "x") items
for extract into a separate list

Using an empty col to the right, say, col E
Put in E2: =IF(C2="","",IF(C2="x",ROW(),""))

Copy E2 down by a safe "max" number of rows
to accomodate the data expected in cols A and B
say, down to E100?

In a new Sheet2
----------------------
With the same labels in A1:B1, : Prod Desc

Put in A2:

=IF(ISERROR(MATCH(SMALL(Sheet1!$E:$E,ROW(A1)),Sheet1!$E:$E,0)),"",OFFSET(She
et1!$A$1,MATCH(SMALL(Sheet1!$E:$E,ROW(A1)),Sheet1!$E:$E,0)-1,COLUMN(A1)-1))

Copy A2 across to B2, then fill down by as many rows as
was done in col E of Sheet1, viz. copy down to B100

Cols A and B will return only those rows from Sheet1
which were checked in col C of Sheet1

For the sample data in Sheet1,
it'll show in Sheet2 as:

Prod Desc
Item1 Text1
Item3 Text3
Item4 Text4

(with Item2 removed, no empty lines)
 
S

swatsp0p

Another way is to use Auto-Filter (found in the Data menu). Make sur
your columns have a Header Row. Place a descriptor for each column
e.g. "Desc", "Number", "X", etc., in the top cell of each column.

Place the cursor anywhere within your range of data, selec
Data>Filter>Auto Filter

Each column header cell will now have a small 'down' arrow. Click o
the arrow in your "X" column and select "X" from the dropdown list

The result will be only rows containing the "x". copy and paste thes
cells to your desired worksheet.

To reshow all rows, reclick the arrow (will be a different colo
{blue?} to indicate it is a filtered column) and select "All".

HTH

Bruc
 
Top