Ref code based on cell

[anoop0085]

Dear All ;

Could you please help me to create a formula on column j based on column A (type) .This will help me to create a product code which is unique . please keep in mind that i am using 2016 , not new version

Marine Spare Log.xlsx
	A	B	C	D	E	F	G	H		J
2	Type	Item Descp	Brands	Supplier	Units	Qty in Hand	Box Location	Minimum
3	O_RINGS	O-RING -MIL SPEC NAS 1611-03 EP - 30857	SEABIRD	UNKNOWN	EACH	14	BOX 09	1		O_RINGS_001
4	O_RINGS	O-RING -PARKER 2- 133 N674 -30858	SEABIRD	UNKNOWN	EACH	7	BOX 09	1		O_RINGS_002
5	O_RINGS	O-RING -PARKER 2- 130 N674 -31322	SEABIRD	UNKNOWN	EACH	14	BOX 09	1		O_RINGS_003
6	O_RINGS	O-RING -OF ANTIFOULING -30097	SEABIRD	UNKNOWN	EACH	7	BOX 09	1		O_RINGS_004
7	O_RINGS	O-RING -PH TEFLON JUNCTION	SEABIRD-PH	UNKNOWN	EACH	8	BOX 09	1		O_RINGS_005
8	O_RINGS	O-RING -PARKER 2- 110 N674 -30498	SEABIRD	UNKNOWN	EACH	14	BOX 09	1		O_RINGS_006
9	O_RINGS	O-RING -SPACER, SHOULDER, #10, NYLONN -311242	SEABIRD	UNKNOWN	EACH	14	BOX 09	1		O_RINGS_007
10	O_RINGS	O-RING -ELECTRONIC CANISTER PISTON SEAL	UNKNOWN	UNKNOWN	EACH	6	BOX 09	1		O_RINGS_008
11	O_RINGS	O-RING -ELECTRONIC CANISTER FACE SEAL	UNKNOWN	UNKNOWN	EACH	6	BOX 09	1		O_RINGS_009
12	O_RINGS	O-RING -BATTERY CANISTER PISTON SEAL	UNKNOWN	UNKNOWN	EACH	6	BOX 09	1		O_RINGS_010
13	O_RINGS	O-RING -BATTERY CANISTER FACE SEAL	UNKNOWN	UNKNOWN	EACH	6	BOX 09	1		O_RINGS_011
14	SCREW	MACH SCREW, 10- 24X7/8, FH -31811	SEABIRD	UNKNOWN	EACH	7	BOX 09	1		SCREW_001
15	SCREW	CAP SCREW, 8-32X 5/8" SH -31513	SEABIRD	UNKNOWN	EACH	21	BOX 09	1		SCREW_002
16	SCREW	CAP SCREW, 8-32X 1/4" SH -31755	SEABIRD	UNKNOWN	EACH	7	BOX 09	1		SCREW_003
17	SCREW	MACH SCREW, 10- 24X7/16, PH -311663	SEABIRD	UNKNOWN	EACH	28	BOX 09	1		SCREW_004
18	SCREW	MACH SCREW, 10-24 X 9/16 PH SS -30174	SEABIRD	UNKNOWN	EACH	14	BOX 09	1		SCREW_005
19	OTHERS	SHIPPING STIKER	SEABIRD	UNKNOWN	EACH	6	BOX 09	1		OTHERS_001
20	WIPER	BLADE, WIPER, POP- IN, HCO (MAA -907939)	SEABIRD	UNKNOWN	EACH	14	BOX 09	1		WIPER_001
21	OTHERS	SYRINGE BIG WITH TEFLON TUBES USED FOR CALIBRATION -30521	SEABIRD-PH	UNKNOWN	EACH	3	BOX 09	1		OTHERS_002
22	OTHERS	SYRINGE SMALL USED FOR PH MAINTENANCE	SEABIRD-PH	UNKNOWN	EACH	8	BOX 09	1		OTHERS_003
23	TOOLS	TEFLON JUNCTION SCREW DRIVE	SEABIRD-PH	UNKNOWN	EACH	8	BOX 09	1
24	SOLUTION	SATURATED KCL & AGCL HYDROLAB-005308HY	SEABIRD-PH	UNKNOWN	BOTTLE	8	BOX 09	1
Actual Stock

Cell Formulas
Range	Formula
F3:F24	F3	=SUMIF('Spare inbound'!$D$3:$D$1000,'Actual Stock'!B3,'Spare inbound'!$E$3:$E$1000)-SUMIF('Spare Outbound'!$C$3:$C$1000,'Actual Stock'!B3,'Spare Outbound'!$E$3:$E$1000)

 

[Snakehips]

@anoop0085 Try =A2&"_"&TEXT(COUNTIF(A$2:A2,A2),"000") in J2 and copy down?

Hope that helps.

 

[mohadin]

What about VBA

Option Explicit

Sub test()
    Dim a
    Dim i&
    a = Cells(1).CurrentRegion.Resize(, 1)
    With CreateObject("scripting.dictionary")
        For i = 2 To UBound(a)
            If Not .exists(a(i, 1)) Then
                .Item(a(i, 1)) = 1
                     a(i, 1) = a(i, 1) & "_" & IIf(.Item(a(i, 1)) > 9, "0", "00") & .Item(a(i, 1))
                Else
                 .Item(a(i, 1)) = .Item(a(i, 1)) + 1
                 a(i, 1) = a(i, 1) & "_" & IIf(.Item(a(i, 1)) > 9, "0", "00") & .Item(a(i, 1))
            End If
        Next
    End With
    [i1].Resize(UBound(a)) = a
End Sub

 

[anoop0085]

@Snakehips thanks , it worked