[belajar-excel] Formula Nama Barang-Merk

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

[belajar-excel] Formula Nama Barang-Merk

halak hita
Selamat Siang Master Excel
Mohon bantuannya, bagaimana agar saya bisa sheet "Hasil" bisa terisi
otomatis merujuk dari sheet
contohnya terlampir.

Terima kasih


Salam
HH

Nama Barang sesuai Merk.xlsx (16K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: [belajar-excel] Formula Nama Barang-Merk

ghozi alkatiri
 tulis formula di C2 sheet hasil (formula array dengan menekan tombol ctrl dan sheet enter bersamaan)
=IFERROR($B2&"-"&INDEX('Daftar-Merk'!$C$4:$C$37,SMALL(IF('Daftar-Merk'!$B$4:$B$37=$B2,ROW('Daftar-Merk'!$B$4:$B$37)),COLUMN(A:A))-1),"")
hasilnya copy ke kanan dan ke bawah
wassalam
Ghozi Alkatiri
    Pada Jumat, 14 Februari 2020 13.46.41 WIB, halak hita [hidden email] [belajar-excel] <[hidden email]> menulis:  
 
    

Selamat Siang Master ExcelMohon bantuannya, bagaimana agar saya bisa sheet "Hasil" bisa terisi otomatis merujuk dari sheet contohnya terlampir.
Terima kasih

SalamHH

  #yiv1339069467 #yiv1339069467 -- #yiv1339069467ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv1339069467 #yiv1339069467ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv1339069467 #yiv1339069467ygrp-mkp #yiv1339069467hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv1339069467 #yiv1339069467ygrp-mkp #yiv1339069467ads {margin-bottom:10px;}#yiv1339069467 #yiv1339069467ygrp-mkp .yiv1339069467ad {padding:0 0;}#yiv1339069467 #yiv1339069467ygrp-mkp .yiv1339069467ad p {margin:0;}#yiv1339069467 #yiv1339069467ygrp-mkp .yiv1339069467ad a {color:#0000ff;text-decoration:none;}#yiv1339069467 #yiv1339069467ygrp-sponsor #yiv1339069467ygrp-lc {font-family:Arial;}#yiv1339069467 #yiv1339069467ygrp-sponsor #yiv1339069467ygrp-lc #yiv1339069467hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv1339069467 #yiv1339069467ygrp-sponsor #yiv1339069467ygrp-lc .yiv1339069467ad {margin-bottom:10px;padding:0 0;}#yiv1339069467 #yiv1339069467actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv1339069467 #yiv1339069467activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv1339069467 #yiv1339069467activity span {font-weight:700;}#yiv1339069467 #yiv1339069467activity span:first-child {text-transform:uppercase;}#yiv1339069467 #yiv1339069467activity span a {color:#5085b6;text-decoration:none;}#yiv1339069467 #yiv1339069467activity span span {color:#ff7900;}#yiv1339069467 #yiv1339069467activity span .yiv1339069467underline {text-decoration:underline;}#yiv1339069467 .yiv1339069467attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv1339069467 .yiv1339069467attach div a {text-decoration:none;}#yiv1339069467 .yiv1339069467attach img {border:none;padding-right:5px;}#yiv1339069467 .yiv1339069467attach label {display:block;margin-bottom:5px;}#yiv1339069467 .yiv1339069467attach label a {text-decoration:none;}#yiv1339069467 blockquote {margin:0 0 0 4px;}#yiv1339069467 .yiv1339069467bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv1339069467 .yiv1339069467bold a {text-decoration:none;}#yiv1339069467 dd.yiv1339069467last p a {font-family:Verdana;font-weight:700;}#yiv1339069467 dd.yiv1339069467last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv1339069467 dd.yiv1339069467last p span.yiv1339069467yshortcuts {margin-right:0;}#yiv1339069467 div.yiv1339069467attach-table div div a {text-decoration:none;}#yiv1339069467 div.yiv1339069467attach-table {width:400px;}#yiv1339069467 div.yiv1339069467file-title a, #yiv1339069467 div.yiv1339069467file-title a:active, #yiv1339069467 div.yiv1339069467file-title a:hover, #yiv1339069467 div.yiv1339069467file-title a:visited {text-decoration:none;}#yiv1339069467 div.yiv1339069467photo-title a, #yiv1339069467 div.yiv1339069467photo-title a:active, #yiv1339069467 div.yiv1339069467photo-title a:hover, #yiv1339069467 div.yiv1339069467photo-title a:visited {text-decoration:none;}#yiv1339069467 div#yiv1339069467ygrp-mlmsg #yiv1339069467ygrp-msg p a span.yiv1339069467yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv1339069467 .yiv1339069467green {color:#628c2a;}#yiv1339069467 .yiv1339069467MsoNormal {margin:0 0 0 0;}#yiv1339069467 o {font-size:0;}#yiv1339069467 #yiv1339069467photos div {float:left;width:72px;}#yiv1339069467 #yiv1339069467photos div div {border:1px solid #666666;min-height:62px;overflow:hidden;width:62px;}#yiv1339069467 #yiv1339069467photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv1339069467 #yiv1339069467reco-category {font-size:77%;}#yiv1339069467 #yiv1339069467reco-desc {font-size:77%;}#yiv1339069467 .yiv1339069467replbq {margin:4px;}#yiv1339069467 #yiv1339069467ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv1339069467 #yiv1339069467ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv1339069467 #yiv1339069467ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv1339069467 #yiv1339069467ygrp-mlmsg select, #yiv1339069467 input, #yiv1339069467 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv1339069467 #yiv1339069467ygrp-mlmsg pre, #yiv1339069467 code {font:115% monospace;}#yiv1339069467 #yiv1339069467ygrp-mlmsg * {line-height:1.22em;}#yiv1339069467 #yiv1339069467ygrp-mlmsg #yiv1339069467logo {padding-bottom:10px;}#yiv1339069467 #yiv1339069467ygrp-msg p a {font-family:Verdana;}#yiv1339069467 #yiv1339069467ygrp-msg p#yiv1339069467attach-count span {color:#1E66AE;font-weight:700;}#yiv1339069467 #yiv1339069467ygrp-reco #yiv1339069467reco-head {color:#ff7900;font-weight:700;}#yiv1339069467 #yiv1339069467ygrp-reco {margin-bottom:20px;padding:0px;}#yiv1339069467 #yiv1339069467ygrp-sponsor #yiv1339069467ov li a {font-size:130%;text-decoration:none;}#yiv1339069467 #yiv1339069467ygrp-sponsor #yiv1339069467ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv1339069467 #yiv1339069467ygrp-sponsor #yiv1339069467ov ul {margin:0;padding:0 0 0 8px;}#yiv1339069467 #yiv1339069467ygrp-text {font-family:Georgia;}#yiv1339069467 #yiv1339069467ygrp-text p {margin:0 0 1em 0;}#yiv1339069467 #yiv1339069467ygrp-text tt {font-size:120%;}#yiv1339069467 #yiv1339069467ygrp-vital ul li:last-child {border-right:none !important;}#yiv1339069467  

=?UTF-8?b?Z2hvemkgNzQwMjIwTmFtYSBCYXJhbmcgc2VzdWFpIE1lcmsueGxzeA==?= (18K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Fw: [belajar-excel] Formula Nama Barang-Merk

hendrik karnadi
 Hai HH,Coba juga dengan Power Query (warisan ilmu dari Mr. Kid) terlampir.Saya pakai Office 365...untuk Excel Versi lain mungkin Applied Stepsnya berbeda.
Salam,HK

   ----- Forwarded message ----- From: ghozi alkatiri [hidden email] [belajar-excel] <[hidden email]>To: halak hita [hidden email] [belajar-excel] <[hidden email]>Sent: Friday, 14 February 2020, 03:03:04 pm GMT+7Subject: Re: [belajar-excel] Formula Nama Barang-Merk
    

 tulis formula di C2 sheet hasil (formula array dengan menekan tombol ctrl dan sheet enter bersamaan)
=IFERROR($B2&"-"&INDEX('Daftar-Merk'!$C$4:$C$37,SMALL(IF('Daftar-Merk'!$B$4:$B$37=$B2,ROW('Daftar-Merk'!$B$4:$B$37)),COLUMN(A:A))-1),"")
hasilnya copy ke kanan dan ke bawah
wassalam
Ghozi Alkatiri
    Pada Jumat, 14 Februari 2020 13.46.41 WIB, halak hita [hidden email] [belajar-excel] <[hidden email]> menulis:  
 
    

Selamat Siang Master ExcelMohon bantuannya, bagaimana agar saya bisa sheet "Hasil" bisa terisi otomatis merujuk dari sheet contohnya terlampir.
Terima kasih

SalamHH

       

ghozi 740220Nama Barang sesuai Merk.xlsx (18K) Download Attachment
=?UTF-8?b?TmFtYSBCYXJhbmcgc2VzdWFpIE1lcmsgKFRyYW5zcG9zZSkueGxzeA==?= (45K) Download Attachment