Ascending order and IF

Originally Posted by kookinuttz

Hi
I have a curly one here and wondering if this is possible.

I have data as follows

| F600 |
| 342 |
| F602 |
| F601 |
| NONE |
| 350 |
| 345 |

I want to move all numbers into a column in ascending order. If it is a number with F in front I want to place YES in the adjacent column on the right. If a number without an F I want a YES in 2 colums to the right. If it has NONE in the field I want it to be ignored. So to look like as follows

| 342 | NO | YES |
| 345 | NO | YES |
| 350 | NO | YES |
| 600 | YES | NO |
| 601 | YES | NO |
| 602 | YES | NO |

I dont know if its possible. But it would be awesome if it could be done.

Thanks

PS Ignore the "NO"s. I dont need them I just put them in as the layout of the diagram got screwy with just nothing in there.

Hi,

In cells B1, C1 & D1 put

=IF(LEFT(A1,1)="F",VALUE(MID(A1,2,LEN(A1)-1)),A1)
=IF(LEFT(A1,1)="F","Yes","No")
=IF(LEFT(A1,1)="F","No","Yes")

and formula fill those down to the end of your data,
then
select those columns and Copy
Paste special = Values back over themselves.

delete column A and sort over the new column A

hth
---

Originally Posted by kookinuttz

Thanks for that, is ther a way to sort via a formula???

no, a formula can affect only the cell it is in.

Select the Data to sort (click above the 1 in row 1, left of A for A column header) and Data, Sort, no header, column A ascending.

hth
---

Originally Posted by kookinuttz

Ok i have another problem with the same data


| F600 |
| 342 |
| F602 |
| F601 |
| NONE |
| 350 |
| 345 |
| E000 |
| E002 |

but I have other data such as E000 and NONE which I want to produce a no result in both comlums

A B C
| 342 | NO | YES |
| 345 | NO | YES |
| 350 | NO | YES |
| 600 | YES | NO |
| 601 | YES | NO |
| 602 | YES | NO |
| E000 | No | No |
| E002 | No | No |
| NONE | No | No |


Your forumla above works great buth produces a yes if the first character is not an F. I want it to produce a yes in column c if the first character is a number. A "no" in both colums if the first character is either a "N" or "E"

Thanks for your time

In cells B1, C1 & D1 put

=IF(LEFT(A1,1)="E",VALUE(MID(A1,2,LEN(A1)-1)),If(A1="NONE",0,A1))
=IF(OR(LEFT(A1,1)="E",Left(A1,1)="N"),"No","Yes")
=IF(OR(LEFT(A1,1)="E",Left(A1,1)="N"),"No","")

hth
---