Messages In This Digest (8 Messages)

1a.: Re: Kategori Qstion From: Kid Mr.

2a.: Re: nyari suatu nilai dengan membandingkan row dan column From: Kid Mr.

3a.: Re: Ambil data dari deretan data From: Kid Mr.

4a.: Re: Mencari Harga Paling terbaru From: Kid Mr.

5.: Bls: ]] XL-mania [[ Mencari Harga Paling terbaru From: ghozi alkatiri

6a.: Bls: ]] XL-mania [[ Ambil data dari deretan data From: ghozi alkatiri

7a.: Bls: ]] XL-mania [[ Kategori Qstion From: ghozi alkatiri

8.: Copy data yg tidak beraturan. From: Exc Pras

View All Topics | Create New Topic

Messages

1a.: Re: Kategori Qstion

Posted by: "Kid Mr." mr.nmkid@gmail.com nmkid.family@ymail.com

Sun Nov 6, 2011 3:12 am (PST)

Coba dengan :
=IF(LEN(D3)>2,VLOOKUP(LEFT(D3,3),$B$9:$D$21,3,FALSE),"")&IF(LEN(D3)>5,",
"&VLOOKUP(MID(D3,4,3),$B$9:$D$21,3,FALSE),"")&IF(LEN(D3)>8,",
"&VLOOKUP(RIGHT(D3,3),$B$9:$D$21,3,FALSE),"")

Kid.

2011/11/2 <candrasigit_MC@yamaha-motor.co.id>

> Salam kenal,
>
> Para pakar xl, bantu untuk masalah saya ya...
> Thanks.
>
>
>
>
>
>
>
> Best Regards,
>
> MGS. M. CANDRA SIGIT
>

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (3)

2a.: Re: nyari suatu nilai dengan membandingkan row dan column

Posted by: "Kid Mr." mr.nmkid@gmail.com nmkid.family@ymail.com

Sun Nov 6, 2011 3:12 am (PST)

Coba formula VLookUp:

di sheet4 cell d3
=VLookUp( a3 , sheet3!$b$2:$h$8 , int( b3*10 - 3 ) , false )
copy ke bawah

Regards.
Kid.

2011/11/3 mtsf26 <mtsf26@yahoo.co.id>

> **
>
>
> dear suhu-suhu Excel,
> gan ada masalah ini.aq mo cari nilai m3 dari tabel yg ada d sheet
> lain,tapi klo mo cari m3 kudu diliat panjang and diameternya seperti pada
> gambar berikut:
>
> hxxp://i1106.photobucket.com/albums/h367/mtsf26/sheet3.jpg
>
> hxxp://i1106.photobucket.com/albums/h367/mtsf26/sheet4.jpg
>
> *ganti hxxp jadi http
> tolong ya gan kalo ad yang tau jawabanna.thanks b4
>
>
>

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (2)

3a.: Re: Ambil data dari deretan data

Posted by: "Kid Mr." mr.nmkid@gmail.com nmkid.family@ymail.com

Sun Nov 6, 2011 3:12 am (PST)

Hi Agung,

Coba dengan :
=Trim( Left( Substitute( Trim( Right( Substitute( *datanya* , "#$" , Rept("
",99) ) , 99 ) ) , "$" , Rept(" ",99) ) , 99 ) )
Sepertinya masih dalam batas 7 level nested formula.
Ganti *datanya *dengan cell teks yang akan diekstrak.
Copy ke cell lain.

Regards.
Kid.

On Thu, Nov 3, 2011 at 09:19, Anggana, Agung <agung.anggana@sc.com> wrote:

> **
>
>
> ** **
>
> Temans,****
>
> ** **
>
> Bisa bantu untuk cara formula yang ini ****
>
> ** **
>
> *DATA AWAL*
>
> *Data hasil Yang Di inginkan*
>
> EC453#JAKARTA#TARAMARA#13X#$13254rt12345$#I56DY#ESN#bblk:##FORMNUM:#$E19
> $DI:60000000$$****
>
> E19****
>
> TD3225#TRUNOTO#SAMIRI#14x#$111021CDA0054$#?J02SN?#ESN#$DI:,
> 15,500,000$#$#LINT#$A66$#NTCRS##~XTV251011~#TGB#****
>
> A66****
>
> MTB2109#RANTASTE#EMP MBNTR KLRS#13X#$110923E#ESN#CLR SCR
> PASS#FORMNUM:#*110923CLS0006*#$E3008$DI:5000000$NINI$****
>
> E3008****
>
>
> MB1358#TGRNGT#TYRNTR#$110920A060013$#?J01JP?#ESN#&#CLEAR#&#19CCB0056*##SCR:PASS#$DI:8,500,000$#$#DNMTY#$
> X22$##TYGHF#DI$****
>
> X22****
>
> KP8333#$#TANJUNG **PINANG**#MISNAR#$00A111101CDB0112$#?J03AD?#ESN#$DI:,
> 10,000,000$#$#NAWON KYUNG#$A26$#TKLRJR#****
>
> A26****
>
> ** **
>
> ** **
>
> Terimakasih****
>
> This email and any attachments are confidential and may also be
> privileged. If you are not the addressee, do not disclose, copy, circulate
> or in any other way use or rely on the information contained in this email
> or any attachments. If received in error, notify the sender immediately and
> delete this email and any attachments from your system. Emails cannot be
> guaranteed to be secure or error free as the message and any attachments
> could be intercepted, corrupted, lost, delayed, incomplete or amended.
> Standard Chartered PLC and its subsidiaries do not accept liability for
> damage caused by this email or any attachments and may monitor email
> traffic.
>
> Standard Chartered PLC is incorporated in England with limited liability
> under company number 966425 and has its registered office at 1 Aldermanbury
> Square, London, EC2V 7SB.
>
> Standard Chartered Bank ("SCB") is incorporated in England with limited
> liability by Royal Charter 1853, under reference ZC18. The Principal Office
> of SCB is situated in England at 1 Aldermanbury Square, London EC2V 7SB. In
> the United Kingdom, SCB is authorised and regulated by the Financial
> Services Authority under FSA register number 114276.
>
> If you are receiving this email from SCB outside the UK, please click
> http://www.standardchartered.com/global/email_disclaimer.html to refer to
> the information on other jurisdictions.
>
>
>

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (3)

4a.: Re: Mencari Harga Paling terbaru

Posted by: "Kid Mr." mr.nmkid@gmail.com nmkid.family@ymail.com

Sun Nov 6, 2011 3:12 am (PST)

Dear Febrin,

Untuk menyusun kode barang, gunakan array formula : (Enter dengan Ctrl
Shift Enter)
=INDEX($A$2:$A$42,SMALL(IF(LEN($A$2:$A$42),ROW($A$2:$A$42)-ROW($A$1)),ROW(1:1)))
copy ke bawah sampai bertemu error value #NUM!

Untuk Tanggal, gunakan formula :
=IF(ISERROR($H5),LOOKUP(9^9,B$2:B$42),INDEX(B$1:B$42,MATCH($H5,$A$2:$A$42,0)))
copy ke bawah sepanjang kolom TGL kemudian ke kanan untuk mendapatkan nilai
Harga.

Regards.
Kid.

2011/11/4 Febrin Miller <febrin_miller01@yahoo.com>

> **
>
>
> Dera XL-mania, minta pencerahan mengenai data terlampir.
> menampilkan harga paling baru dari data terlampir.
>
> Thank u
>
> Febrin
>
>
>
>

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (2)

5.: Bls: ]] XL-mania [[ Mencari Harga Paling terbaru

Posted by: "ghozi alkatiri" ghozialkatiri@yahoo.co.id ghozialkatiri

Sun Nov 6, 2011 3:12 am (PST)

formula array
{=IF($E4=VLOOKUP("z";$A$2:$A$42;1);INDEX(B$1:B$42;MAX(ROW(B$1:B$42)));INDEX(B$1:B$42;MATCH($E5;$A$1:$A$42;0)-1))}
isikan di F4 (Tanggal) copy ke G4 (Harga) kembalikan format di G4 menjadi format angka karena hasil copy dari F4 paste ke G4 cell G4 menjadi format tanggal.
selanjutnya copy ke baris di bawahnya.

wasssalam

Ghozi Alkatiri

--- Pada Jum, 4/11/11, Febrin Miller <febrin_miller01@yahoo.com> menulis:

Dari: Febrin Miller <febrin_miller01@yahoo.com>
Judul: ]] XL-mania [[ Mencari Harga Paling terbaru
Kepada: "XL-mania@yahoogroups.com" <XL-mania@yahoogroups.com>
Tanggal: Jumat, 4 November, 2011, 4:56 PM

Dera XL-mania, minta pencerahan mengenai data terlampir.
menampilkan harga paling baru dari data terlampir.

Thank u

Febrin

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (1)

6a.: Bls: ]] XL-mania [[ Ambil data dari deretan data

Posted by: "ghozi alkatiri" ghozialkatiri@yahoo.co.id ghozialkatiri

Sun Nov 6, 2011 3:14 am (PST)

formula globalnya adalah (formula array)
LEN(A2)-7))-LEN(SUBSTITUTE(LEFT(A2;LEN(A2)-7);"$";""))-1)+1;7);FIND("$";MID(LEFT(A2;LEN(A2)-7);SMALL(IF(MID(LEFT(A2;LEN(A2)-7);ROW(1:$200);1)="$";ROW(1:$200));LEN(LEFT(A2;LEN(A2)-7))-LEN(SUBSTITUTE(LEFT(A2;LEN(A2)-7);"$";""))-1)+1;7));10;"")

pennjelasan dari formula global
kesimpulandari kecenderungan
data
1.
posisi string data yang ingin diambil selalu diapit tanda dolar ($).
2.
karakter di belakang string yang ingin diambil ada yang masih mengandung tanda dolar ada yang tidak.
untuk solusinya dibutuhkan 4 langkah
langkah1.
untuk
memastikan tidak ada karakter ($) lagi
setelah ($) yang mengapit string yang
diinginkan adalah mengurangi jumlahkarakter di kanan data asal dengan
7 karakter formula di G2 data awal di A2
=LEFT(A2;LEN(A2)-7) .
langkah 2.
mencarinilai posisi karakter ($) pertama yang mengapit karakter dari hasil langkah 1 di G2 formula di F2
{=SMALL(IF(MID(G2;ROW(1:$200);1)="$";ROW(1:$200));LEN(G2)-LEN(SUBSTITUTE(G2;"$";""))-1)+1}
dan
menghasilkan nilai karakter ke berapa dimulainya string yang diiginkan
langkah3.
hasil
dari langkah 2 dijadikan acuan untuk
mengambil string yang diinginkan formula di I2
=MID(G2;H2;10)
yang menghsilkan string yang masih mengandung ($) penutup string yang diinginkan.
langkah 4.
membuang
karakter ($) dan seluruh karakter di
sebelah kanannya dengan fungsi formula di J2
=REPLACE(I2;FIND("$";I2);10;"")
lebih jelasnya bisa dilihat di attachment.

wassalam

Ghozi Alkatiri

________________________________
Dari: "Anggana, Agung" <agung.anggana@sc.com>
Kepada: xl-mania@yahoogroups.com
Dikirim: Kamis, 3 November 2011 9:19
Judul: ]] XL-mania [[ Ambil data dari deretan data

Temans,

Bisa bantu untuk cara formula yang ini

DATA AWAL Data hasil Yang Di inginkan
EC453#JAKARTA#TARAMARA#13X#$13254rt12345$#I56DY#ESN#bblk:##FORMNUM:#$E19$DI:60000000$$ E19
TD3225#TRUNOTO#SAMIRI#14x#$111021CDA0054$#?J02SN?#ESN#$DI:, 15,500,000$#$#LINT#$A66$#NTCRS##~XTV251011~#TGB# A66
MTB2109#RANTASTE#EMP MBNTR KLRS#13X#$110923E#ESN#CLR SCR PASS#FORMNUM:#*110923CLS0006*#$E3008$DI:5000000$NINI$ E3008
MB1358#TGRNGT#TYRNTR#$110920A060013$#?J01JP?#ESN#&#CLEAR#&#19CCB0056*##SCR:PASS#$DI:8,500,000$#$#DNMTY#$X22$##TYGHF#DI$ X22
KP8333#$#TANJUNG PINANG #MISNAR#$00A111101CDB0112$#?J03AD?#ESN#$DI:, 10,000,000$#$#NAWON KYUNG#$A26$#TKLRJR# A26

Terimakasih
This email and any attachments are confidential and may also be privileged. If you are not the addressee, do not disclose, copy, circulate or in any other way use or rely on the information contained in this email or any attachments. If received in error, notify the sender immediately and delete this email and any attachments from your system. Emails cannot be guaranteed to be secure or error free as the message and any attachments could be intercepted, corrupted, lost, delayed, incomplete or amended. Standard Chartered PLC and its subsidiaries do not accept liability for damage caused by this email or any attachments and may monitor email traffic.

Standard Chartered PLC is incorporated in England with limited liability under company number 966425 and has its registered office at 1 Aldermanbury Square, London, EC2V 7SB.

Standard Chartered Bank ("SCB") is incorporated in England with limited liability by Royal Charter 1853, under reference ZC18. The Principal Office of SCB is situated in England at 1 Aldermanbury Square, London EC2V 7SB. In the United Kingdom, SCB is authorised and regulated by the Financial Services Authority under FSA register number 114276.

If you are receiving this email from SCB outside the UK, please click http://www.standardchartered.com/global/email_disclaimer.html to refer to the information on other jurisdictions.

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (3)

7a.: Bls: ]] XL-mania [[ Kategori Qstion

Posted by: "ghozi alkatiri" ghozialkatiri@yahoo.co.id ghozialkatiri

Sun Nov 6, 2011 3:14 am (PST)

untuk mendapatkan hasil yang sama persis dengan yang diharapkan dibutuhkan formula yang lumayan tidak pendek
sbb:
=REPLACE(INDEX($C$9:$C$21;MATCH(MID(D3;1;3);$B$9:$B$21;0));FIND(" ";INDEX($C$9:$C$21;MATCH(MID(D3;1;3);$B$9:$B$21;0)));3;"")&IF(LEN(D3)<4;"";","&REPLACE(INDEX($C$9:$C$21;MATCH(MID(D3;4;3);$B$9:$B$21;0));FIND(" ";INDEX($C$9:$C$21;MATCH(MID(D3;4;3);$B$9:$B$21;0)));3;""))&IF(LEN(D3)<7;"";","&REPLACE(INDEX($C$9:$C$21;MATCH(MID(D3;7;3);$B$9:$B$21;0));FIND(" ";INDEX($C$9:$C$21;MATCH(MID(D3;7;3);$B$9:$B$21;0)));3;""))

wassalam

Ghozi Alkatiri

________________________________
Dari: "candrasigit_MC@yamaha-motor.co.id" <candrasigit_MC@yamaha-motor.co.id>
Kepada: XL-mania@yahoogroups.com
Dikirim: Rabu, 2 November 2011 12:46
Judul: ]] XL-mania [[ Kategori Qstion

Salam kenal,

Para pakar xl, bantu untuk masalah saya
ya...
Thanks.

Best Regards,

MGS. M. CANDRA SIGIT

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (3)

8.: Copy data yg tidak beraturan.

Posted by: "Exc Pras" excpras@gmail.com excpras

Sun Nov 6, 2011 3:15 am (PST)

Hi Para pakar Excel,

Saya mohon petunjuk utk pekerjaan saya yg memakai excel.

Saya membeli produk pertanian mis apel dll.

Penjual datang membawa barang dalam karung bisa 1karung,2 atau 25 lebih, yg
masing2 langsung diberi nomer urut. Nomer ini kelanjutan dari nomer urut
pembelian kemarin.

Kemudian isi diperiksa dan diberi harga yg bisa berlainan per karungnya
atau bila tidak cocok bisa dikembalikan utk dibawa kembali
penjualnya.(sehingga nomer karung bisa tidak berurutan -tapi jarang)

saya kerjakan perhitungan hasil pembelian pagi ini seperti : berat per
karung, potongan dan nilai uang per karung serta uang total yg diterima per
orang.

Repotnya adalah tiap menghitung orang yg berlainan aku harus mengcopy dan
paste kan rumusnya.

Untuk lebih jelasnya saya sertakan lampiran yg biasa aku kerjakan.

Mohon petunjuk utk penyederhanaannya.

Terimakasih untuk bantuan dan penjelasan yang diberikan.

Salam

Pras.

NB: Bila memang sudah pernah dibahas mohon diberi petunjuk kapan , agar
saya bisa melihat ulang arsip pembahasannya.

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (1)

Create New Topic | Visit Your Group on the Web

Messages

+-:: XL-mania ::::::::::::::::::::----------------------------------+
| ayooo... coba cari XL-mania di linkedin.com                       |
| selamat merayakan idul adha!                                      |
+-------------------------------------------------------------------+
| DILARANG : MLM, money game, OOT, iklan tanpa izin, SARA, testing, |
| pembicaraan pribadi, one line message, melecehkan, tidak sopan. |
+-------------------------------------------------------------------+
| Buat subjek yang kreatif, jangan : "tanya", "help", "mohon bantu" |
| Usahakan besar attachment < 200 kb. Gunakan winzip jika perlu. |
+-------------------------------------------------------------------+
| Ajak teman-teman Anda bergabung dengan mengirim e-mail kosong ke |
| XL-mania-subscribe@yahoogroups.com atau kirimkan mereka file dari |
| http://groups.yahoo.com/group/XL-mania/files/Promotion/           |
+-------------------------------------------------------------------+
| Berikan testimoni di : http://www.friendster.com/xlmania atau... |
| http://www.xl-mania.com/2008/06/testimoni-xl-mania.html           |
+-------------------------------------------------------------------+
| Message lama ada di :                                             |
| http://groups.yahoo.com/group/XL-mania/messages [perlu yahoo id] |
| http://www.mail-archive.com/xl-mania@yahoogroups.com              |
+-------------------------------------------------------------------+

MARKETPLACE

Stay on top of your group activity without leaving the page you're on - Get the Yahoo! Toolbar now.

Change settings via the Web (Yahoo! ID required)
Change settings via email: Switch delivery to Individual | Switch format to Traditional
Visit Your Group | Yahoo! Groups Terms of Use | Unsubscribe

EXCEL FEED

Minggu, 06 November 2011

]] XL-mania [[ Digest Number 2288

Messages In This Digest (8 Messages)

Messages

Re: Kategori Qstion

Posted by: "Kid Mr." mr.nmkid@gmail.com nmkid.family@ymail.com

Sun Nov 6, 2011 3:12 am (PST)

Re: nyari suatu nilai dengan membandingkan row dan column

Posted by: "Kid Mr." mr.nmkid@gmail.com nmkid.family@ymail.com

Sun Nov 6, 2011 3:12 am (PST)

Re: Ambil data dari deretan data

Posted by: "Kid Mr." mr.nmkid@gmail.com nmkid.family@ymail.com

Sun Nov 6, 2011 3:12 am (PST)

Re: Mencari Harga Paling terbaru

Posted by: "Kid Mr." mr.nmkid@gmail.com nmkid.family@ymail.com

Sun Nov 6, 2011 3:12 am (PST)

Bls: ]] XL-mania [[ Mencari Harga Paling terbaru

Posted by: "ghozi alkatiri" ghozialkatiri@yahoo.co.id ghozialkatiri

Sun Nov 6, 2011 3:12 am (PST)

Bls: ]] XL-mania [[ Ambil data dari deretan data

Posted by: "ghozi alkatiri" ghozialkatiri@yahoo.co.id ghozialkatiri

Sun Nov 6, 2011 3:14 am (PST)

Bls: ]] XL-mania [[ Kategori Qstion

Posted by: "ghozi alkatiri" ghozialkatiri@yahoo.co.id ghozialkatiri

Sun Nov 6, 2011 3:14 am (PST)

Copy data yg tidak beraturan.

Posted by: "Exc Pras" excpras@gmail.com excpras

Sun Nov 6, 2011 3:15 am (PST)

Tidak ada komentar:

Posting Komentar

Followers

Blog Archive

About Me