IF function

[nahmja]

Hi,

i have huge date contain text/number/combined devises.
sample data as below
A B
A2 21411111
A3 21412232
A4 24600221
A5 24600000
A6 2K221212
A7 2K221213
A8 2D221213
A9 2D221212
A10 71717777
A11 3458
A12 3587


I wanted to get the devises model, result should as below.
A B C
A2 21411111 = P80
A3 21412232 = P80
A4 24600221 = P90
A5 24600000 = P90
A6 2K221212 = S80
A7 2K221213 = S80
A8 2D221213 = S80
A9 2D221212 = S80
A10 71717777 = Web
A11 3458 = Tech
A12 3587 = Tech

 

[alansidman]

Where are the Devise model located? and what criteria is available to link them. Please explain your situation in more detail. We have not enough information to provide you with a solution

 

[Rick Rothstein]

One thing you must keep in mind when you ask a question in a forum... the people you are asking to help you know absolutely nothing about your data, absolutely nothing about how it is laid out in the workbook, absolutely nothing about what you want done with it and absolutely nothing about how whatever it is you want done presented back to you as a result... you must be very specific about describing each of these areas, in detail, and you should not assume that we will be able to "figure it out" on our own. Remember, you are asking us for help... so help us to be able to help you by providing the information we need to do so, even if that information seems "obvious" to you (remember, it is only obvious to you because of your familiarity with your the data, its layout and the overall objective for it).

 

[nahmja]

Devise ID (A)	Devise Type (B)
21411111 <tbody> </tbody>
21412232 <tbody> </tbody>
24600221 <tbody> </tbody>
24600000 <tbody> </tbody>
2K221212 <tbody> </tbody>
2K221213 <tbody> </tbody>
2D221213 <tbody> </tbody>
2D221212 <tbody> </tbody>
71717777 <tbody> </tbody>
3458 <tbody> </tbody>
3587 <tbody> </tbody>

<tbody>
</tbody>

Above the actual data i would like to get the result as below table.

Devise ID (A)	Devise Type (B)
21411111 <tbody> </tbody>	P80
21412232 <tbody> </tbody>	P80
24600221 <tbody> </tbody>	P90
24600000 <tbody> </tbody>	P90
2K221212 <tbody> </tbody>	S80
2K221213 <tbody> </tbody>	S80
2D221213 <tbody> </tbody>	S80
2D221212 <tbody> </tbody>	S80
71717777 <tbody> </tbody>	Web
3458 <tbody> </tbody>	WebTech
3587 <tbody> </tbody>	WebTech

<tbody>
</tbody>

 

[Rick Rothstein]

We will try once more... where did the P80, P90, S80, Web, WebTech come from and how are we supposed to know how to put them in the cells you show them in?

 

[nahmja]

Thanks for your quick response. Model name we have to put using formula

If devise I'd start with (21) model will be P80
If devise I'd strats with (2K or 2D or 2L) model will be S80
If devise I'd start with (32 or 3D or 3L or 3K) model will be S90
If devise I'd start with 70 above model will be web
If devise strat with 30 above model will be webtech

 

[alansidman]

You have a P90 in your proposed solution. But not in your criteria

 

[nahmja]

You have a P90 in your proposed solution. But not in your criteria

Sorry missed that one,here is the information


If devise I'd start with (24) model will be P90

 

[Rick Rothstein]

Give this UDF (user defined function) a try...

Function DeviseType(DeviseID As String) As String
  Select Case UCase(Left(DeviseID, 2))
    Case 21: DeviseType = "P80"
    Case 24: DeviseType = "P90"
    Case "2K", "2D", "2L": DeviseType = "S80"
    Case "32", "3D", "3L", "3K": DeviseType = "S90"
    Case Is >= 70: DeviseType = "web"
    Case Is >= 30: DeviseType = "webtech"
  End Select
End Function

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use DeviseType just like it was a built-in Excel function. For example,

=DeviseType(A2)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.