need an if than statement

Microsoft Excel 010 - complete package
January 2, 2012 at 16:22:56
Specs: Windows 7
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 unknown

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


See More: need an if than statement

Report •


#1
January 2, 2012 at 18:28:02
With all your Codes on Sheet 1 like:

      A      B
1)  code   response
2) 60998     Yes
3) 56743     yes
4) 47474     No
5) 23456   unknown 

then 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

http://www.skeptic.com/


Report •

#2
January 2, 2012 at 18:47:59
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?

Report •

#3
January 2, 2012 at 18:56:32
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 To

this should bring up the Go To window,
Click Special and Select Blanks
Click OK

Next on the Task Bar
Edit
Delete

This should bring up the Delete window
Select Entire Row
Click OK

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

MIKE

http://www.skeptic.com/


Report •

Related Solutions


Ask Question