CREATE SEQUENCE ID IN EXCEL DATA COLUMNS

FOR EXAMPLE DATA EXCEL :

DATA :
A
B
C
A
A
B
D
C
C
C


So, we need to create the sequence number in the excel data using function,
if the data in Column "A" and the first row is 2 the function is :

=COUNTIFS($A$2:$A2, A2)


It will be creating the sequence id for all columns checking the all data.

it has worked well as proof picture on top.

FARIZ SETIAWAN

in Bahasa,
Ini merupakan cara untuk membuat sequence number didalam excel dengan menggunakan function,
id urut dalam excel dengan melakukan pengecekan terlebih dahulu pada seluruh data.
sehingga data yang dihasilkan valid.

VBA Text to Column solution with functionable

Text to column VBA to generate general type to text with function. following this script :

1. Create Function
2. Call the function
3. Function detail

---1. Create function----

Function TextToColumn(Reng As Range, Col As Integer, tipe As Variant)

Reng.TextToColumns Destination:=Reng, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=False, _
Semicolon:=False, _
Comma:=True, _
Space:=False, _
Other:=False, _
FieldInfo:=Array(Col, tipe), _
TrailingMinusNumbers:=True

End Function

---2. Call the function----
Worksheets("Sheet1").Select
Call TextToColumn(ActiveWorkbook.Sheets("Sheet1").Range("DD:DD"), 108, xlTextFormat)

'Reng = ActiveWorkbook.Sheets("Sheet1").Range("DD:DD") So, the range is columns DD
'Index Columns = 108 as counting column A = 1 so Column DD is 108
'Tipe = xlTextFormat
'example for other type such as xlGeneralFormat or following this reference table :
'xlDMYFormat 4 DMY date format.
'xlDYMFormat 7 DYM date format.
'xlEMDFormat 10 EMD date format.
'xlGeneralFormat 1 General.
'xlMDYFormat 3 MDY date format.
'xlMYDFormat 6 MYD date format.
'xlSkipColumn 9 Column is not parsed.
'xlTextFormat 2 Text.
'xlYDMFormat 8 YDM date format.
'xlYMDFormat 5 YMD date format.
'if you want the type is date you can choose xlDMYFormat to change xlTextFormat

'it is work well for my worksheet.