Auto Generate ID

[shafiq247]

Hi Every One. Hope You all were enjoying good health. can anyone help me to generate auto ID based on cell value like

Order_ID	Order Date	Source	Customer Name	Phone No	Address	City	Category	Product_Sku	Size	Color	Sale Price
PF-0001	01-12-18​	PinkFit
GS-0001	02-12-18​	GloryShop
WEB-0001	05-12-18​	Website
PF-0002	07-12-18​	PinkFit
GS-0002	07-12-18​	GloryShop
	08-12-18​	Whats App
?	08-12-18​	PinkFit

<tbody>
</tbody>

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

 

[James006]

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:

 

[shafiq247]

<tbody>
</tbody>

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:

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 .

Order_ID	Order Date	Source	Customer Name	Phone No	Address	City	Category	Product_Sku	Size	Color	Sale Price
PF-0001		PinkFit
GS-0001		GloryShop
WA-0001		Whats App
PF-0002		PinkFit
PF-0003		PinkFit

<tbody>
</tbody>

 

[James006]

A simple Countif ...

 

[James006]

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

 

[shafiq247]

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

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))

 

[James006]

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

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

Hope this will help

 

[r1998]

Hello Shafiq Bhai
use this vba 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

 

[shafiq247]

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

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

Hope this will help

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"),"")

 

[James006]

Glad you could solve your problem ...

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

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