Auto Generate ID

S

shafiq247

Board Regular
Joined
Feb 8, 2016
Messages
54
Hi Every One. Hope You all were enjoying good health. can anyone help me to generate auto ID based on cell value like




[TABLE="width: 690"]
<tbody>[TR]
[TD="width: 78, bgcolor: transparent"] Order_ID
[/TD]
[TD="width: 87, bgcolor: transparent"] Order Date
[/TD]
[TD="width: 141, bgcolor: transparent"] Source
[/TD]
[TD="width: 73, bgcolor: transparent"] Customer Name
[/TD]
[TD="width: 64, bgcolor: transparent"] Phone No
[/TD]
[TD="width: 64, bgcolor: transparent"] Address
[/TD]
[TD="width: 64, bgcolor: transparent"] City
[/TD]
[TD="width: 68, bgcolor: transparent"] Category
[/TD]
[TD="width: 90, bgcolor: transparent"] Product_Sku
[/TD]
[TD="width: 64, bgcolor: transparent"] Size
[/TD]
[TD="width: 64, bgcolor: transparent"] Color
[/TD]
[TD="width: 64, bgcolor: transparent"] Sale Price
[/TD]
[/TR]
[TR]
[TD="width: 78, bgcolor: transparent"] PF-0001
[/TD]
[TD="width: 87, bgcolor: transparent"]
01-12-18​
[/TD]
[TD="width: 141, bgcolor: transparent"] PinkFit
[/TD]
[TD="width: 73, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 68, bgcolor: transparent"][/TD]
[TD="width: 90, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 78, bgcolor: transparent"] GS-0001
[/TD]
[TD="width: 87, bgcolor: transparent"]
02-12-18​
[/TD]
[TD="width: 214, bgcolor: transparent, colspan: 2"] GloryShop
[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 68, bgcolor: transparent"][/TD]
[TD="width: 90, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 78, bgcolor: transparent"] WEB-0001
[/TD]
[TD="width: 87, bgcolor: transparent"]
05-12-18​
[/TD]
[TD="width: 141, bgcolor: transparent"] Website
[/TD]
[TD="width: 73, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 68, bgcolor: transparent"][/TD]
[TD="width: 90, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 78, bgcolor: transparent"] PF-0002
[/TD]
[TD="width: 87, bgcolor: transparent"]
07-12-18​
[/TD]
[TD="width: 141, bgcolor: transparent"] PinkFit
[/TD]
[TD="width: 73, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 68, bgcolor: transparent"][/TD]
[TD="width: 90, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 78, bgcolor: transparent"] GS-0002
[/TD]
[TD="width: 87, bgcolor: transparent"]
07-12-18​
[/TD]
[TD="width: 214, bgcolor: transparent, colspan: 2"] GloryShop
[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 68, bgcolor: transparent"][/TD]
[TD="width: 90, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 78, bgcolor: transparent"][/TD]
[TD="width: 87, bgcolor: transparent"]
08-12-18​
[/TD]
[TD="width: 214, bgcolor: transparent, colspan: 2"] Whats App
[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 68, bgcolor: transparent"][/TD]
[TD="width: 90, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 78, bgcolor: transparent"]?
[/TD]
[TD="width: 87, bgcolor: transparent"]
08-12-18​
[/TD]
[TD="width: 141, bgcolor: transparent"] PinkFit


[/TD]
[TD="width: 73, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 68, bgcolor: transparent"][/TD]
[TD="width: 90, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]



if I if put pinkfit or glory shop last row cell than no auto generate No. should be PF-0003 or GS-0003 and same like WhatsApp and Website
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Sort by date Sort by votes
Hello,

If you want to be consistent ... you should use WeBsite ... to get the two initials WB-0001 ...

in line with all your other choices ... :wink:
 
Last edited:
Upvote 0
[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
James006 said:
Hello,

If you want to be consistent ... you should use WeBsite ... to get the two initials WB-0001 ...

in line with all your other choices ... :wink:
Click to expand...

what should be formula to generate it auto for next entry
like if I put in source column pinkfit or gloryshop the id should be auto updated that I have highlighted .

[TABLE="width: 576"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent"] Order_ID

[/TD]
[TD="width: 64, bgcolor: transparent"]Order Date
[/TD]
[TD="width: 64, bgcolor: transparent"]Source
[/TD]
[TD="width: 64, bgcolor: transparent"]Customer Name
[/TD]
[TD="width: 64, bgcolor: transparent"]Phone No
[/TD]
[TD="width: 64, bgcolor: transparent"]Address
[/TD]
[TD="width: 64, bgcolor: transparent"]City
[/TD]
[TD="width: 64, bgcolor: transparent"]Category
[/TD]
[TD="width: 64, bgcolor: transparent"]Product_Sku
[/TD]
[TD="width: 64, bgcolor: transparent"]Size
[/TD]
[TD="width: 64, bgcolor: transparent"]Color
[/TD]
[TD="width: 64, bgcolor: transparent"]Sale Price
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]PF-0001
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]PinkFit
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]GS-0001
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, colspan: 2"]GloryShop
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]WA-0001
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, colspan: 2"]Whats App
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: yellow"]PF-0002
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]PinkFit
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: yellow"]PF-0003
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]PinkFit
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
Sorry if my explanation was not clear enough ...

There are two separate problems :

1. Generate the Two Letter Prefix : PF - GS - WA - WB - etc ...

2. Create a simple Countif() function to count and increment automatically ...

HTH
 
Upvote 0
James006 said:
Sorry if my explanation was not clear enough ...

There are two separate problems :

1. Generate the Two Letter Prefix : PF - GS - WA - WB - etc ...

2. Create a simple Countif() function to count and increment automatically ...

HTH
Click to expand...

I have written this formula but It giving value error
=IF(C2="pinkfit","PF-0000" & COUNTIF($C$2:C2,C2)),IF(C2="gloryshop","GS-0000" & COUNTIF($C$2:C2,C2))
 
Upvote 0
If you only want to solve the second problem ...

( and forget the first question with the Two Letter Prefix ...)

i.e. the problem : Count and Increment automatically ...

You can test following formula in cell A2

Code: 
="-"&TEXT((COUNTIF($C$1:C1,C2)+1),"0000")

Hope this will help
 
Upvote 0
Hello Shafiq Bhai
use this vba code
Code: 
Option Explicit


Sub hiii()
 
 Dim i As Long, p As Integer, pp As String


For i = 2 To Range("C2").End(xlDown).End(xlDown).Row - 1
    
    If Cells(i, 3) = "PinkFit" Then
    p = WorksheetFunction.CountIf(Range("C2", Cells(i, 3)), "Pinkfit")
    pp = Format(p, "0000")
    Cells(i, 1) = "PF-" & pp
    
    ElseIf Cells(i, 3) = "GloryShop" Then
    p = WorksheetFunction.CountIf(Range("C2", Cells(i, 3)), "GloryShop")
    pp = Format(p, "0000")
    Cells(i, 1) = "GS-" & pp
    
    ElseIf Cells(i, 3) = "Whats App" Then
    p = WorksheetFunction.CountIf(Range("C2", Cells(i, 3)), "Whats App")
    pp = Format(p, "0000")
    Cells(i, 1) = "WA-" & pp
    End If
    
Next i


End Sub
Best of Luck :beerchug:
 
Last edited:
Upvote 0
James006 said:
If you only want to solve the second problem ...

( and forget the first question with the Two Letter Prefix ...)

i.e. the problem : Count and Increment automatically ...

You can test following formula in cell A2

Code: 
="-"&TEXT((COUNTIF($C$1:C1,C2)+1),"0000")

Hope this will help
Click to expand...

Done! working perfect Thanks

=IF(ISTEXT(C2),IF(C2="PinkFit","PF",IF(C2="Gloryshop","GS",IF(C2="Whats App","WA",IF(C2="Website","WEB"))))&"-"&TEXT((COUNTIF($C$1:C1,C2)+1),"0000"),"")
 
Upvote 0
Glad you could solve your problem ...

Another possibility for the Prefix ... in case more names appear in the future ... a dedicated UDF ..

Code: 
Function ExtractCap(Txt As String) As String
Dim xRegEx As Object
Application.Volatile
    Set xRegEx = CreateObject("VBSCRIPT.REGEXP")
    xRegEx.Pattern = "[^A-Z]"
    ' Extract Capital Letters
    xRegEx.Global = True
    ExtractCap = xRegEx.Replace(Txt, "")
End Function


And in cell A2 ... =ExtractCap(C2)&"-"&TEXT((COUNTIF($C$1:C1,C2)+1),"0000")

HTH
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
Attempting to auto generate ID names based on uncertain parameters
Replies
1
Views
254
Rijnsent
Rijnsent
F
  • Question Question
cut defined cells from a sheet paste them on another sheet then delete incompletes rows
Replies
2
Views
733
Flavien
F
M
  • Question Question
Help with FilterXML formula
Replies
0
Views
2K
montex01
M
S
  • Question Question
Auto Increment in Order No
Replies
0
Views
461
shafiq247
S
L
  • Question Question
Formula to Auto Generate and change shift Duty Weekly
Replies
0
Views
983
Lukma
L

Forum statistics

Threads
1,224,823
Messages
6,181,173
Members
453,021
Latest member
Justyna P

Share this page

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