Mau bikin kode barang berurutan

  1. 7 tahun lalu

    Halo agan2... saya mentok nih....
    mau bikin kode barang otomatis dengan bentuk (Contoh) SJT0001
    Nah permasalahannya, gimana caranya biar bisa urut kebawah ya?
    jadi SJT0002, SJT0003.......dst

    tapi untuk urutan 10 keatas jadi
    SJT0010, SJT0011

    Mohon dibantu gan/aganwati

    terima kasih duluan deh

    @Adi Daulay Halo agan2... saya mentok nih....
    mau bikin kode barang otomatis dengan bentuk (Contoh) SJT0001
    Nah permasalahannya, gimana caranya biar bisa urut kebawah ya?
    jadi SJT0002, SJT0003.......dst

    tapi untuk urutan 10 keatas jadi
    SJT0010, SJT0011

    Mohon dibantu gan/aganwati

    terima kasih duluan deh

    Pake ini gan :D
    sedikit ribet memang & jangan suruh saya mengartikannya ....

    letakkan ini di A1: SJT0001
    letakkan ini di A2:

    =IFERROR(IF(LEN(MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9},A1&"123456789")),LEN(A1)-MIN(FIND({1,2,3,4,5,6,7,8,9},A1&"123456789"))+1)+1)=4,LEFT(A1,MIN(FIND({"0","1","2","3","4","5","6","7","8","9"},A1&"0123456789"))-1)&MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9},A1&"123456789")),LEN(A1)-MIN(FIND({1,2,3,4,5,6,7,8,9},A1&"123456789"))+1)+1,IF(LEN(MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9},A1&"123456789")),LEN(A1)-MIN(FIND({1,2,3,4,5,6,7,8,9},A1&"123456789"))+1)+1)=3,LEFT(A1,MIN(FIND({"0","1","2","3","4","5","6","7","8","9"},A1&"0123456789"))-1)&"0"&MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9},A1&"123456789")),LEN(A1)-MIN(FIND({1,2,3,4,5,6,7,8,9},A1&"123456789"))+1)+1,IF(LEN(MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9},A1&"123456789")),LEN(A1)-MIN(FIND({1,2,3,4,5,6,7,8,9},A1&"123456789"))+1)+1)=2,LEFT(A1,MIN(FIND({"0","1","2","3","4","5","6","7","8","9"},A1&"0123456789"))-1)&"00"&MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9},A1&"123456789")),LEN(A1)-MIN(FIND({1,2,3,4,5,6,7,8,9},A1&"123456789"))+1)+1,IF(LEN(MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9},A1&"123456789")),LEN(A1)-MIN(FIND({1,2,3,4,5,6,7,8,9},A1&"123456789"))+1)+1)=1,LEFT(A1,MIN(FIND({"0","1","2","3","4","5","6","7","8","9"},A1&"0123456789"))-1)&"000"&MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9},A1&"123456789")),LEN(A1)-MIN(FIND({1,2,3,4,5,6,7,8,9},A1&"123456789"))+1)+1,"")))),"Limit reached")

    file terlampir yaa

    semoga membantu

    [attachment:5888118f2db34]

  2. langsung aja gan blok di sel kode barang trus tarik kebawah...
    lampirkan contoh kalo mau lebih terjawab gan

  3. Fujiansyah92

    25 Jan 2017 Terverifikasi Jawaban Terpilih + 4.131 Poin

    @Adi Daulay Halo agan2... saya mentok nih....
    mau bikin kode barang otomatis dengan bentuk (Contoh) SJT0001
    Nah permasalahannya, gimana caranya biar bisa urut kebawah ya?
    jadi SJT0002, SJT0003.......dst

    tapi untuk urutan 10 keatas jadi
    SJT0010, SJT0011

    Mohon dibantu gan/aganwati

    terima kasih duluan deh

    Pake ini gan :D
    sedikit ribet memang & jangan suruh saya mengartikannya ....

    letakkan ini di A1: SJT0001
    letakkan ini di A2:

    =IFERROR(IF(LEN(MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9},A1&"123456789")),LEN(A1)-MIN(FIND({1,2,3,4,5,6,7,8,9},A1&"123456789"))+1)+1)=4,LEFT(A1,MIN(FIND({"0","1","2","3","4","5","6","7","8","9"},A1&"0123456789"))-1)&MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9},A1&"123456789")),LEN(A1)-MIN(FIND({1,2,3,4,5,6,7,8,9},A1&"123456789"))+1)+1,IF(LEN(MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9},A1&"123456789")),LEN(A1)-MIN(FIND({1,2,3,4,5,6,7,8,9},A1&"123456789"))+1)+1)=3,LEFT(A1,MIN(FIND({"0","1","2","3","4","5","6","7","8","9"},A1&"0123456789"))-1)&"0"&MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9},A1&"123456789")),LEN(A1)-MIN(FIND({1,2,3,4,5,6,7,8,9},A1&"123456789"))+1)+1,IF(LEN(MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9},A1&"123456789")),LEN(A1)-MIN(FIND({1,2,3,4,5,6,7,8,9},A1&"123456789"))+1)+1)=2,LEFT(A1,MIN(FIND({"0","1","2","3","4","5","6","7","8","9"},A1&"0123456789"))-1)&"00"&MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9},A1&"123456789")),LEN(A1)-MIN(FIND({1,2,3,4,5,6,7,8,9},A1&"123456789"))+1)+1,IF(LEN(MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9},A1&"123456789")),LEN(A1)-MIN(FIND({1,2,3,4,5,6,7,8,9},A1&"123456789"))+1)+1)=1,LEFT(A1,MIN(FIND({"0","1","2","3","4","5","6","7","8","9"},A1&"0123456789"))-1)&"000"&MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9},A1&"123456789")),LEN(A1)-MIN(FIND({1,2,3,4,5,6,7,8,9},A1&"123456789"))+1)+1,"")))),"Limit reached")

    file terlampir yaa

    semoga membantu

    bikin kode barang berurutan.xlsx

  4. @Fujiansyah92 Pake ini gan :D
    sedikit ribet memang & jangan suruh saya mengartikannya ....

    letakkan ini di A1: SJT0001
    letakkan ini di A2:

    =IFERROR(IF(LEN(MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9},A1&"123456789")),LEN(A1)-MIN(FIND({1,2,3,4,5,6,7,8,9},A1&"123456789"))+1)+1)=4,LEFT(A1,MIN(FIND({"0","1","2","3","4","5","6","7","8","9"},A1&"0123456789"))-1)&MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9},A1&"123456789")),LEN(A1)-MIN(FIND({1,2,3,4,5,6,7,8,9},A1&"123456789"))+1)+1,IF(LEN(MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9},A1&"123456789")),LEN(A1)-MIN(FIND({1,2,3,4,5,6,7,8,9},A1&"123456789"))+1)+1)=3,LEFT(A1,MIN(FIND({"0","1","2","3","4","5","6","7","8","9"},A1&"0123456789"))-1)&"0"&MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9},A1&"123456789")),LEN(A1)-MIN(FIND({1,2,3,4,5,6,7,8,9},A1&"123456789"))+1)+1,IF(LEN(MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9},A1&"123456789")),LEN(A1)-MIN(FIND({1,2,3,4,5,6,7,8,9},A1&"123456789"))+1)+1)=2,LEFT(A1,MIN(FIND({"0","1","2","3","4","5","6","7","8","9"},A1&"0123456789"))-1)&"00"&MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9},A1&"123456789")),LEN(A1)-MIN(FIND({1,2,3,4,5,6,7,8,9},A1&"123456789"))+1)+1,IF(LEN(MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9},A1&"123456789")),LEN(A1)-MIN(FIND({1,2,3,4,5,6,7,8,9},A1&"123456789"))+1)+1)=1,LEFT(A1,MIN(FIND({"0","1","2","3","4","5","6","7","8","9"},A1&"0123456789"))-1)&"000"&MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9},A1&"123456789")),LEN(A1)-MIN(FIND({1,2,3,4,5,6,7,8,9},A1&"123456789"))+1)+1,"")))),"Limit reached")

    file terlampir yaa

    semoga membantu

    [attachment:5888118f2db34]

    Beuuhhh!!!!
    sakti mandraguna gan. berhasil pake rumus diatas (y)
    tengkyu pak broo

  5. sama sama pak.

    senang bisa membantu :)

  6. Sebenarnya bisa lebih praktis dengan rumus ini
    =IF(C1<>"";"SJT"&TEXT(ROW();"0000");"")

    Monggo kalau mau di coba

 

atau Mendaftar untuk ikut berdiskusi!