(ASK) formula penggabungan dan logic

  1. 2 tahun lalu

    Dear all,
    mohon bantuannya untuk memberikan forumula untuk kebutuhan saya menggabungkan kata pada setiap row.

    berikut contoh nya
    -image-

    nb :
    1. column warna kuning adalah data mentah, sedangkan biru adalah yang diharapkan.

    Pake ini gan :
    file terlampir

    =IF(B3="","",IF(AND(B4=" ",B5=" ",B6=" ",B7=" ",B8=" ",B9=" ",B10=" ",B11=" "),CONCATENATE(D3," ",D4," ",D5," ",D6," ",D7," ",D8," ",D9," ",D10," ",D11),IF(AND(B4="",B5="",B6="",B7="",B8="",B9="",B10=""),CONCATENATE(D3," ",D4," ",D5," ",D6," ",D7," ",D8," ",D9," ",D10),IF(AND(B4="",B5="",B6="",B7="",B8="",B9=""),CONCATENATE(D3," ",D4," ",D5," ",D6," ",D7," ",D8," ",D9),IF(AND(B4="",B5="",B6="",B7="",B8=""),CONCATENATE(D3," ",D4," ",D5," ",D6," ",D7," ",D8),IF(AND(B4="",B5="",B6="",B7=""),CONCATENATE(D3," ",D4," ",D5," ",D6," ",D7),IF(AND(B4="",B5="",B6=""),CONCATENATE(D3," ",D4," ",D5," ",D6),IF(AND(B4="",B5=""),CONCATENATE(D3," ",D4," ",D5),IF(B4="",CONCATENATE(D3," ",D4),D3)))))))))

    [attachment:58859104499f7]

  2. mungkin bisa dibantu upload file excelnya pak ?

  3. Fujiansyah92

    23 Jan 2017 Terverifikasi Jawaban Terpilih + 4.126 Poin

    Pake ini gan :
    file terlampir

    =IF(B3="","",IF(AND(B4=" ",B5=" ",B6=" ",B7=" ",B8=" ",B9=" ",B10=" ",B11=" "),CONCATENATE(D3," ",D4," ",D5," ",D6," ",D7," ",D8," ",D9," ",D10," ",D11),IF(AND(B4="",B5="",B6="",B7="",B8="",B9="",B10=""),CONCATENATE(D3," ",D4," ",D5," ",D6," ",D7," ",D8," ",D9," ",D10),IF(AND(B4="",B5="",B6="",B7="",B8="",B9=""),CONCATENATE(D3," ",D4," ",D5," ",D6," ",D7," ",D8," ",D9),IF(AND(B4="",B5="",B6="",B7="",B8=""),CONCATENATE(D3," ",D4," ",D5," ",D6," ",D7," ",D8),IF(AND(B4="",B5="",B6="",B7=""),CONCATENATE(D3," ",D4," ",D5," ",D6," ",D7),IF(AND(B4="",B5="",B6=""),CONCATENATE(D3," ",D4," ",D5," ",D6),IF(AND(B4="",B5=""),CONCATENATE(D3," ",D4," ",D5),IF(B4="",CONCATENATE(D3," ",D4),D3)))))))))

    formula penggabungan dan logic.xlsx

  4. @Fujiansyah92 Pake ini gan :
    file terlampir

    =IF(B3="","",IF(AND(B4=" ",B5=" ",B6=" ",B7=" ",B8=" ",B9=" ",B10=" ",B11=" "),CONCATENATE(D3," ",D4," ",D5," ",D6," ",D7," ",D8," ",D9," ",D10," ",D11),IF(AND(B4="",B5="",B6="",B7="",B8="",B9="",B10=""),CONCATENATE(D3," ",D4," ",D5," ",D6," ",D7," ",D8," ",D9," ",D10),IF(AND(B4="",B5="",B6="",B7="",B8="",B9=""),CONCATENATE(D3," ",D4," ",D5," ",D6," ",D7," ",D8," ",D9),IF(AND(B4="",B5="",B6="",B7="",B8=""),CONCATENATE(D3," ",D4," ",D5," ",D6," ",D7," ",D8),IF(AND(B4="",B5="",B6="",B7=""),CONCATENATE(D3," ",D4," ",D5," ",D6," ",D7),IF(AND(B4="",B5="",B6=""),CONCATENATE(D3," ",D4," ",D5," ",D6),IF(AND(B4="",B5=""),CONCATENATE(D3," ",D4," ",D5),IF(B4="",CONCATENATE(D3," ",D4),D3)))))))))

    [attachment:58859104499f7]

    gan thanks banget. sudah solved dan bisa digunakan sampai ratusan ribu row
    mohon maaf sy gabisa upload data karena banyak bgt row nya sampe ratusan ribu.

  5. siap gan.sama sama gan.

    ya sebagai sample...ambil beberapa row aja ...ga usah semua gan

 

atau Mendaftar untuk ikut berdiskusi!