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.

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

"Teric506" wrote:

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.

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!
--
Gary''s Student


"Teric506" wrote:

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 also use a formula like:

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

Then filter to show everything greater than 0.



Teric506 wrote:

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.

--

Dave Peterson

On Wed, 21 Sep 2005 05:16:10 -0700, "Teric506"
wrote:

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 A1:D5 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 A1:D5

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