Microsoft Excel 010 - complete package

I have a list of over 2000 codes these codes are listed in col A, col B will have either yes, no or unknown. I need a formula that will take the codes that are listed in A and if there is a yes in B place it on one worksheet. If col B has no put all those codes on a sheet and if col B says unknown put those codes on anther sheet.

example

code response

60998 Yes

56743 yes

47474 No

23456 unknownlist has 2000+ codes. need yes with appropriate codes on one sheet...no's...unknowns..

With all your Codes on Sheet 1 like: A B 1) code response 2) 60998 Yes 3) 56743 yes 4) 47474 No 5) 23456 unknownthen you could use three formula:

On Sheet 2, Cell A2 enter the formula: =IF(Sheet1!B2="Yes",Sheet1!A2,"")

On Sheet 3, Cell A2 enter the formula: =IF(Sheet1!B2="No",Sheet1!A2,"")

On Sheet 4, Cell A2 enter the formula: =IF(Sheet1!B2="Unknown",Sheet1!A2,"")Drag the formula down as many rows as necessary.

MIKE

thank you. one last question. Is there a way to eliminate the spaces? for instance if cell b4 was not a yes it doesn't pull anything over to the next sheet ,however leaves a blank cell. Does that make sense?

First I would do a Copy/Paste Special/Values

so as not to loose the data

then to eliminate the blank rows, just do a Sort

or if you don't want to sort then try this:To delete Blank Rows,

First highlight the Column with your data in the cells

Next on the Task Bar

Edit

Go Tothis should bring up the Go To window,

Click Special and Select Blanks

Click OKNext on the Task Bar

Edit

DeleteThis should bring up the Delete window

Select Entire Row

Click OKAll your blank rows should now be gone, leaving only those rows that contained data.

MIKE

Ask Your Question

Weekly Poll

Do you think Microsoft's new Surface Go will be a hit?

Discuss in The Lounge

Poll History