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
2	2010 1 0215 025	anonym a	KB120806	Skripsi
3	2010 1 0215 025	anonym a	KB121506	Perencanaan dan perancangan Produksi
4	2010 1 0215 025	anonym a	KB121632	Pemodelan Sistem + Prakt
5	2010 1 0215 025	anonym a	KB121635	Organisasi dan Manajemen Perusahaan Industri
6	2010 1 0215 025	anonym a	KB121738	Analisis dan Perancangan Perusahaan
7	2010 1 0215 025	anonym a	KB121739	Perencanaan & Perancangan Produk
8	2010 1 0215 025	anonym a	KB121740	Manajemen Pemasaran
9	2010 1 0215 025	anonym a	KB121743	Manajemen Proyek (Pil)
10	2010 1 0215 025	anonym a	KB121851	Procurement & supply Chain Management*/** (Pil)
11	2010 1 0215 025	anonym a	PB120607	Metodologi Penelitian Teknik Industri
12	2010 1 0215 046	anonym b	BB100609	Komunikasi Bisnis
13	2010 1 0215 046	anonym b	KB121631	Otomasi Sistem Produksi
14	2010 1 0215 046	anonym b	KB121632	Pemodelan Sistem + Praktek
15	2010 1 0215 046	anonym b	KB121633	Sistem Produksi + Praktek
16	2010 1 0215 046	anonym b	KB121635	Organisasi dan Manajemen Perusahaan Industri
17	2010 1 0215 046	anonym b	KB121636	Optimisasi
18	2010 1 0215 046	anonym b	KK121625	Analisis dan Perancangan Sist Informasi Manufaktur+prakt
19	2010 1 0215 046	anonym b	PB100407	Sekuriti Industri
20	2010 1 0215 046	anonym b	PB120607	Metodologi Penelitian Teknik Industri
21	2010 1 0215 071	anonym c	KB100806	Kewirausahaan & Pengembangan Enterprais
22	2010 1 0215 071	anonym c	KB120705	Kerja Praktek
23	2010 1 0215 071	anonym c	KB121741	Simulasi Komputer + Praktek
24	2010 1 0215 071	anonym c	KB121848	Perilaku dan Perancangan Organisasi
25	2010 1 0215 071	anonym c	KB121851	Procurement & supply Chain Management*/** (Pil)
26	2010 1 0215 071	anonym c	KK121625	Analisis dan Perancangan Sist Informasi Manufaktur+prakt
27	2010 1 0215 071	anonym c	PB100407	Sekuriti Industri

<tbody>
</tbody>

db-sheet​

and here is the final_sheet
Excel 2007

	A	B	C	D	E	F
1	NPM	NAMA	KD_MK1	KD_MK2	KD_MK3	etc
2	2010 1 0215 046	anonym b
3	2012 1 0215 210	a
4	2012 1 0215 323	b
5	2012 1 0215 324	c
6	2012 1 0215 326	d
7	2013 1 0215 002	e
8	2013 1 0215 017	f
9	2013 1 0215 022	g

<tbody>
</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

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

<tbody>
</tbody>

final_sheet

NPM	NAMA	KD_MTK1	KD_MTK2	KD_MTK3	KD_MTK4	KD_MTK5	KD_MTK6	KD_MTK7	KD_MTK8	KD_MTK9	KD_MTK10
2010 1 0215 025	ANONYM A	KB120806	KB121506	KB121632	KB121635	KB121738	KB121739	KB121740	KB121743	KB121851	PB120607
2010 1 0215 046	ANONYM B
2010 1 0215 071	ANONYM C

<tbody>
</tbody>

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

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