IF function

nahmja

New Member
Joined
May 12, 2015
Messages
15
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
 
Last edited:

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
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
 
Upvote 0
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).
 
Upvote 0
[TABLE="class: outer_border, width: 500, align: left"]
<tbody>[TR]
[TD]Devise ID (A)[/TD]
[TD]Devise Type (B)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]21411111[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]21412232[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]24600221[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]24600000[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]2K221212[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]2K221213[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]2D221213[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]2D221212[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]71717777[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]3458[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]3587[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]





















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

[TABLE="class: outer_border, width: 500, align: left"]
<tbody>[TR]
[TD]Devise ID (A)[/TD]
[TD]Devise Type (B)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]21411111[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]P80[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]21412232[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]P80[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]24600221[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]P90[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]24600000[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]P90[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]2K221212[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]S80[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]2K221213[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]S80[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]2D221213[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]S80[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]2D221212[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]S80[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]71717777[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Web[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]3458[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]WebTech[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]3587[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]WebTech[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
You have a P90 in your proposed solution. But not in your criteria
 
Upvote 0
Give this UDF (user defined function) a try...

Code:
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.
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top