copying names to list depending on certain conditions

M

macca

I have a spreadsheet which contains people's names and numbers associated
with those people. I would like to set it up so if the number is greater
than 5 then excel will copy the name into a different list. I can do this
with IF statements but i get lots of blank cells where that person does not
fill the criteria.

Any ideas how to solve this????
 
B

Bob Phillips

This looks like a job for Data>Filter>Advanced Filter to me.

You can set a conditional criteria and specify a destination location.
 
M

macca

This seems to only work for week 1
Week 2 I want it to compare the week 2 number column (Column C) to the names
(column A) and then copy the name into a seperate list but it seems unable to
skip the row inbetween.


Is this possible?
 
B

Bob Phillips

I think some sample data, type not an attachment, would help with expected
results as I have difficulty envisaging what you want.
 
M

Max

.. I want it to compare the week 2 number
column (Column C) to the names (column A)
and then copy the name into a seperate list
but it seems unable to skip the row inbetween ..
Click to expand...

Harzarding one guess ..

Assume data is in row2 down,
(Names in col A, numbers in col C as above)

Using 2 empty cols to the right, say cols E & F

Put in E2:
=IF(ISERROR(SMALL(F:F,ROWS($A$1:A1))),"",
INDEX(A:A,MATCH(SMALL(F:F,ROWS($A$1:A1)),F:F,0)))

Put in F2:
=IF(C2="","",IF(C2>5,C2+ROW()/10^10,""))
(Leave F1 empty)

Select E2:F2, copy down until the last row of data in cols A & C

Col E will return the list of names from col A
with numbers > 5 in col C, all neatly bunched at the top ..
 
Top