Data Lookup

[IM_Haries]

Hello everyone


Let Me Introduce myself, i'm Harris and work on the university as academic administration.


i want to ask a question on here


Can excell looking Variable A and B in one row,


if met Variable A and B in the same row,
then retrieve the necessary data from the database sheet in the other sheet.


Can we create Excel formulas like that? I was very frustrated and confused.


i was looking for the formula on google but i was found nothing, then i found this forum,




I also provided screenshots for ease of friends in the forum to analyze the case.



picture 1. the sheet where to put the formula.


Picture. 2 it is the database sheet.



The formula that I need is, the formula to find "Kode_MK" and "NPM" (student identification number), and if successfully found the "NPM" and "MK code" of the same in a row, then search for "Mata_Kuliah" (Name of courses)


that is it, im sorry if my english so bad and will make you all confusing,

And Thank you for views and replies

 

[MARK858]

where you learn the excel formula? just google it or you have a book or course?

That particular one... I haven't a clue. In general over the years learnt from reading in forums, books and Google (not really courses. Although have been on a couple over the years).
To start with to get over work needs, then later more from interest.

could you make this more automatically?

Yes relatively easily with the exception that I don't know what you do with C1 when dealing with larger data (previously mentioned).
If you post what you need to happen with C1 then I will look at it sometime tonight.

i have a gift for you (not money)

Thanks but I don't post on the board for gifts. I post to try and help others (and to keep in practice) .

 

[IM_Haries]

That particular one... I haven't a clue. In general over the years learnt from reading in forums, books and Google (not really courses. Although have been on a couple over the years).
To start with to get over work needs, then later more from interest.




Yes relatively easily with the exception that I don't know what you do with C1 when dealing with larger data (previously mentioned).
If you post what you need to happen with C1 then I will look at it sometime tonight.



Thanks but I don't post on the board for gifts. I post to try and help others (and to keep in practice) .

Thank you for your help. When you star learning excell, did you blind with the excell formula like me? how did you learn to mastering the excell formula?

okey, back to my excell problem,

after i get the formula from you, i've little bit changing the formula from you for completed other coloumn, but i've trouble.

if you could resolve this problem, you have finished all my problem in this spreedsheet,

here is the db_sheet

Excel 2007

	B	C	D	E
anonym a	Pemodelan Sistem + Prakt
anonym a	Organisasi dan Manajemen Perusahaan Industri
anonym a
anonym a
anonym a	Manajemen Pemasaran
anonym a	Manajemen Proyek (Pil)
anonym a	Procurement & supply Chain Management*/** (Pil)
anonym a	Metodologi Penelitian Teknik Industri
anonym b	Komunikasi Bisnis
anonym b	Otomasi Sistem Produksi
anonym b	Pemodelan Sistem + Praktek
anonym b	Sistem Produksi + Praktek
anonym b	Organisasi dan Manajemen Perusahaan Industri
anonym b	Optimisasi
anonym b	Analisis dan Perancangan Sist Informasi Manufaktur+prakt
anonym b	Sekuriti Industri
anonym b	Metodologi Penelitian Teknik Industri
anonym c	Kewirausahaan & Pengembangan Enterprais
anonym c	Simulasi Komputer + Praktek
anonym c	Perilaku dan Perancangan Organisasi
anonym c	Procurement & supply Chain Management*/** (Pil)
anonym c	Analisis dan Perancangan Sist Informasi Manufaktur+prakt
anonym c	Sekuriti Industri

<tbody>
[TD="align: center"]2[/TD]
[TD="bgcolor: #FFC000, align: center"]2010 1 0215 025[/TD]
[TD="bgcolor: #FFC000"]anonym a[/TD]
[TD="bgcolor: #FFC000, align: center"]KB120806[/TD]
[TD="bgcolor: #FFC000"]Skripsi[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FFFF00, align: center"]2010 1 0215 025[/TD]
[TD="bgcolor: #FFFF00"]anonym a[/TD]
[TD="bgcolor: #FFFF00, align: center"]KB121506[/TD]
[TD="bgcolor: #FFFF00"]Perencanaan dan perancangan Produksi[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]2010 1 0215 025[/TD]

[TD="align: center"]KB121632[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]2010 1 0215 025[/TD]

[TD="align: center"]KB121635[/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]2010 1 0215 025[/TD]

[TD="align: center"]KB121738[/TD]
[TD="bgcolor: #FFFF00"]Analisis dan Perancangan Perusahaan[/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]2010 1 0215 025[/TD]

[TD="align: center"]KB121739[/TD]
[TD="bgcolor: #FFFF00"]Perencanaan & Perancangan Produk[/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]2010 1 0215 025[/TD]

[TD="align: center"]KB121740[/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]2010 1 0215 025[/TD]

[TD="align: center"]KB121743[/TD]

[TD="align: center"]10[/TD]
[TD="align: center"]2010 1 0215 025[/TD]

[TD="align: center"]KB121851[/TD]

[TD="align: center"]11[/TD]
[TD="align: center"]2010 1 0215 025[/TD]

[TD="align: center"]PB120607[/TD]

[TD="align: center"]12[/TD]
[TD="align: center"]2010 1 0215 046[/TD]

[TD="align: center"]BB100609[/TD]

[TD="align: center"]13[/TD]
[TD="align: center"]2010 1 0215 046[/TD]

[TD="align: center"]KB121631[/TD]

[TD="align: center"]14[/TD]
[TD="align: center"]2010 1 0215 046[/TD]

[TD="align: center"]KB121632[/TD]

[TD="align: center"]15[/TD]
[TD="align: center"]2010 1 0215 046[/TD]

[TD="align: center"]KB121633[/TD]

[TD="align: center"]16[/TD]
[TD="align: center"]2010 1 0215 046[/TD]

[TD="align: center"]KB121635[/TD]

[TD="align: center"]17[/TD]
[TD="align: center"]2010 1 0215 046[/TD]

[TD="align: center"]KB121636[/TD]

[TD="align: center"]18[/TD]
[TD="align: center"]2010 1 0215 046[/TD]

[TD="align: center"]KK121625[/TD]

[TD="align: center"]19[/TD]
[TD="align: center"]2010 1 0215 046[/TD]

[TD="align: center"]PB100407[/TD]

[TD="align: center"]20[/TD]
[TD="align: center"]2010 1 0215 046[/TD]

[TD="align: center"]PB120607[/TD]

[TD="align: center"]21[/TD]
[TD="align: center"]2010 1 0215 071[/TD]

[TD="align: center"]KB100806[/TD]

[TD="align: center"]22[/TD]
[TD="bgcolor: #FFC000, align: center"]2010 1 0215 071[/TD]
[TD="bgcolor: #FFC000"]anonym c[/TD]
[TD="bgcolor: #FFC000, align: center"]KB120705[/TD]
[TD="bgcolor: #FFC000"]Kerja Praktek[/TD]

[TD="align: center"]23[/TD]
[TD="align: center"]2010 1 0215 071[/TD]

[TD="align: center"]KB121741[/TD]

[TD="align: center"]24[/TD]
[TD="align: center"]2010 1 0215 071[/TD]

[TD="align: center"]KB121848[/TD]

[TD="align: center"]25[/TD]
[TD="align: center"]2010 1 0215 071[/TD]

[TD="align: center"]KB121851[/TD]

[TD="align: center"]26[/TD]
[TD="align: center"]2010 1 0215 071[/TD]

[TD="align: center"]KK121625[/TD]

[TD="align: center"]27[/TD]
[TD="align: center"]2010 1 0215 071[/TD]

[TD="align: center"]PB100407[/TD]

</tbody>

db-sheet​

and here is the final_sheet
Excel 2007

	A	B	C	D	E	F
anonym b
a
b
c
d
e
f
g

<tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]NPM[/TD]
[TD="align: center"]NAMA[/TD]
[TD="align: center"]KD_MK1[/TD]
[TD="align: center"]KD_MK2[/TD]
[TD="align: center"]KD_MK3[/TD]
[TD="align: center"]etc[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]2010 1 0215 046[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]2012 1 0215 210[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]2012 1 0215 323[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]2012 1 0215 324[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]2012 1 0215 326[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]2013 1 0215 002[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]2013 1 0215 017[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]2013 1 0215 022[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

final_sheet​

Excel 2007

in the db-sheet, we can see for every collager had more than 3 code_MK (in the coloumn d),
and the question is, could i take all the code_MK fom the db_sheet and put all in the final_sheet automatically? and the result is not vertically (single coloumn) but horizntally (1 code_mk in the 1 coloumn)

please give me the example for the anonym b,

Thank You Very Much for your help...

I Wish sometimes my excell skill will be like you :D

 

[MARK858]

Nope sorry but I'm not understanding what you want the formula to do.

Can you create a new final sheet with the result you want to achieve from db_sheet in post #12 manually entered by yourself so I can see the end result (and what is in row 1 now of the db_sheet ?)?

 

[IM_Haries]

Nope sorry but I'm not understanding what you want the formula to do.

Can you create a new final sheet with the result you want to achieve from db_sheet in post #12 manually entered by yourself so I can see the end result (and what is in row 1 now of the db_sheet ?)?

Here it is,


db-sheet

[TABLE="class: tableizer-table"]
<tbody>[TR="class: tableizer-firstrow"]
[TH]NO[/TH]
[TH]NPM[/TH]
[TH]NAMA[/TH]
[TH]KD_MTK[/TH]
[TH]MATA KULIAH[/TH]
[/TR]
[TR]
[TD][/TD]
[TD]2010 1 0215 025[/TD]
[TD]ANONYM A[/TD]
[TD]KB120806[/TD]
[TD]Skripsi[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2010 1 0215 025[/TD]
[TD]ANONYM A[/TD]
[TD]KB121506[/TD]
[TD]Perencanaan dan perancangan Produksi[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2010 1 0215 025[/TD]
[TD]ANONYM A[/TD]
[TD]KB121632[/TD]
[TD]Pemodelan Sistem + Prakt[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2010 1 0215 025[/TD]
[TD]ANONYM A[/TD]
[TD]KB121635[/TD]
[TD]Organisasi dan Manajemen Perusahaan Industri[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2010 1 0215 025[/TD]
[TD]ANONYM A[/TD]
[TD]KB121738[/TD]
[TD]Analisis dan Perancangan Perusahaan[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2010 1 0215 025[/TD]
[TD]ANONYM A[/TD]
[TD]KB121739[/TD]
[TD]Perencanaan & Perancangan Produk[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2010 1 0215 025[/TD]
[TD]ANONYM A[/TD]
[TD]KB121740[/TD]
[TD]Manajemen Pemasaran[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2010 1 0215 025[/TD]
[TD]ANONYM A[/TD]
[TD]KB121743[/TD]
[TD]Manajemen Proyek (Pil)[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2010 1 0215 025[/TD]
[TD]ANONYM A[/TD]
[TD]KB121851[/TD]
[TD]Procurement & supply Chain Management*/** (Pil)[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2010 1 0215 025[/TD]
[TD]ANONYM A[/TD]
[TD]PB120607[/TD]
[TD]Metodologi Penelitian Teknik Industri[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2010 1 0215 046[/TD]
[TD]ANONYM B[/TD]
[TD]BB100609[/TD]
[TD]Komunikasi Bisnis[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2010 1 0215 046[/TD]
[TD]ANONYM B[/TD]
[TD]KB121631[/TD]
[TD]Otomasi Sistem Produksi[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2010 1 0215 046[/TD]
[TD]ANONYM B[/TD]
[TD]KB121632[/TD]
[TD]Pemodelan Sistem + Praktek[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2010 1 0215 046[/TD]
[TD]ANONYM B[/TD]
[TD]KB121633[/TD]
[TD]Sistem Produksi + Praktek[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2010 1 0215 046[/TD]
[TD]ANONYM B[/TD]
[TD]KB121635[/TD]
[TD]Organisasi dan Manajemen Perusahaan Industri[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2010 1 0215 046[/TD]
[TD]ANONYM B[/TD]
[TD]KB121636[/TD]
[TD]Optimisasi[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2010 1 0215 046[/TD]
[TD]ANONYM B[/TD]
[TD]KK121625[/TD]
[TD]Analisis dan Perancangan Sist Informasi Manufaktur+prakt[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2010 1 0215 046[/TD]
[TD]ANONYM B[/TD]
[TD]PB100407[/TD]
[TD]Sekuriti Industri[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2010 1 0215 046[/TD]
[TD]ANONYM B[/TD]
[TD]PB120607[/TD]
[TD]Metodologi Penelitian Teknik Industri[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2010 1 0215 071[/TD]
[TD]ANONYM C[/TD]
[TD]KB100806[/TD]
[TD]Kewirausahaan & Pengembangan Enterprais[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2010 1 0215 071[/TD]
[TD]ANONYM C[/TD]
[TD]KB120705[/TD]
[TD]Kerja Praktek[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2010 1 0215 071[/TD]
[TD]ANONYM C[/TD]
[TD]KB121741[/TD]
[TD]Simulasi Komputer + Praktek[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2010 1 0215 071[/TD]
[TD]ANONYM C[/TD]
[TD]KB121848[/TD]
[TD]Perilaku dan Perancangan Organisasi[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2010 1 0215 071[/TD]
[TD]ANONYM C[/TD]
[TD]KB121851[/TD]
[TD]Procurement & supply Chain Management*/** (Pil)[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2010 1 0215 071[/TD]
[TD]ANONYM C[/TD]
[TD]KK121625[/TD]
[TD]Analisis dan Perancangan Sist Informasi Manufaktur+prakt[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2010 1 0215 071[/TD]
[TD]ANONYM C[/TD]
[TD]PB100407[/TD]
[TD]Sekuriti Industri[/TD]
[/TR]
</tbody>[/TABLE]



final_sheet

[TABLE="class: tableizer-table"]
<tbody>[TR="class: tableizer-firstrow"]
[TH]NPM[/TH]
[TH]NAMA[/TH]
[TH]KD_MTK1[/TH]
[TH]KD_MTK2[/TH]
[TH]KD_MTK3[/TH]
[TH]KD_MTK4[/TH]
[TH]KD_MTK5[/TH]
[TH]KD_MTK6[/TH]
[TH]KD_MTK7[/TH]
[TH]KD_MTK8[/TH]
[TH]KD_MTK9[/TH]
[TH]KD_MTK10[/TH]
[/TR]
[TR]
[TD]2010 1 0215 025[/TD]
[TD]ANONYM A[/TD]
[TD]KB120806[/TD]
[TD]KB121506[/TD]
[TD]KB121632[/TD]
[TD]KB121635[/TD]
[TD]KB121738[/TD]
[TD]KB121739[/TD]
[TD]KB121740[/TD]
[TD]KB121743[/TD]
[TD]KB121851[/TD]
[TD]PB120607[/TD]
[/TR]
[TR]
[TD]2010 1 0215 046[/TD]
[TD]ANONYM B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2010 1 0215 071[/TD]
[TD]ANONYM C[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

=========================================================================================

I was put the KD_MTK (from DB_sheet) manually to final sheet,

could i do it automatically?
Thank You

let me know if this explaination is still make you confuse, and im sorry for my bad english :D

Thank you Very Much