Help please!!!

[Guest]

I have a spreadsheet that i am trying to filter. I have tried auto filter but
it is not working. What I have is a coumn with a part number and then 4
columns with other numbers. I am trying to pull out the part numbers that
have a 1 in any of the other 4 columns. below is a sample of my spreadsheet.
A number 1 may show up in column A for one part number and a number 1 may
show up in column C for another part number. I need to be able to put all of
the part numbers that have 1 in one of those columns together and so on. This
is for cycle counts for inventory so the 4 columns represent the week that
those part numbers need to be counted.

wk wk wk wk part #
1 13 26 39 19080
2 14 27 40 100039
4 16 29 1 101007

Any help would be great. I am beating my head against a wall here.

 

[Guest]

the simplest way is to use a helper column with
=if(or(a1=1,b1=1,c1=1,d1=1),1,0)
copy down and use this column for your auto filter

 

[Guest]

In F1 put:

=MAX((A1=1),(B1=1),(C1=1),(D1=1))*E1

and copy down. Column F will contain only the part numbers that meet your
criteria.

Have a good day!

 

[Dave Peterson]

You could also use a formula like:

=countif(a2:d2,1)
and drag down.

Then filter to show everything greater than 0.

 

[Ron Rosenfeld]

I have a spreadsheet that i am trying to filter. I have tried auto filter but
it is not working. What I have is a coumn with a part number and then 4
columns with other numbers. I am trying to pull out the part numbers that
have a 1 in any of the other 4 columns. below is a sample of my spreadsheet.
A number 1 may show up in column A for one part number and a number 1 may
show up in column C for another part number. I need to be able to put all of
the part numbers that have 1 in one of those columns together and so on. This
is for cycle counts for inventory so the 4 columns represent the week that
those part numbers need to be counted.

wk wk wk wk part #
1 13 26 39 19080
2 14 27 40 100039
4 16 29 1 101007

Any help would be great. I am beating my head against a wall here.

You could use the Advanced Filter.

Name your first four columns differently: e.g:

wk1 wk2 wk3 wk4

Assume your data table is in A10:A5000

Set up your Criteria Range in A15 as so:

wk1 wk2 wk3 wk4
1
1
1
1

Then, with the cursor in the table, select Data/Filter/Advanced Filter

I would suggest "Copy to another Location"

The List Range should be filled out properly.

For Criteria Range enter A15

For Copy To just select the upper left hand corner cell (it can be on a
different sheet).

Unique records -- up to you.

And that should work to give you a list.

You can have different criteria ranges for the different week numbers, or make
changes in the criteria range you have.




--ron