Look up columns and fill vales in the intersection

[Prascena]

1st sheet of my excel:

	38522a	39522a	40582a	36559a
245
544
322
65

<tbody>
</tbody>

2nd sheet of my excel:

IR NO	ASSY NO	QTY
65	38522a	2
65	39522a	3
67	36559a	4
245	38522a	1
322	40582a	1
322	36559a	2
544	39522a	1

<tbody>
</tbody>

Required Output:

	38522a	39522a	40582a	36559a
245	1	0	0	0
544	0	1	0	0
322	0	0	1	2
65	2	3	0	0

<tbody>
</tbody>

Can anyone please help me with a formula to automate this?

Thank you so much in advance.

Regards,
Prasanna.A
****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">C

38522a	39522a	40582a	36559a
245	1
544		1
322			1	2
65	2	3		1

<tbody>
</tbody>

</body>

 

[jarjarbingie]

1st sheet of my excel:

	38522a	39522a	40582a	36559a
245
544
322
65

<tbody>
</tbody>

2nd sheet of my excel:

IR NO	ASSY NO	QTY
65	38522a	2
65	39522a	3
67	36559a	4
245	38522a	1
322	40582a	1
322	36559a	2
544	39522a	1

<tbody>
</tbody>

Required Output:

	38522a	39522a	40582a	36559a
245	1	0	0	0
544	0	1	0	0
322	0	0	1	2
65	2	3	0	0

<tbody>
</tbody>

Can anyone please help me with a formula to automate this?

Thank you so much in advance.

Regards,
Prasanna.A
****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">C

38522a	39522a	40582a	36559a
245	1
544		1
322			1	2
65	2	3		1

<tbody>
</tbody>

B2 = SUMIFS(Sheet2!$C$2:$C$8,Sheet2!$A$2:$A$8,$A2,Sheet2!$B$2:$B$8,B$1)

 

[Prascena]

Hi thank you for the reply,

This works perfect for me.

But if you can explain me a little how it works, I would be great help.

Thank you so much.

Regards,
Prasanna.A

 

[jarjarbingie]

Hi thank you for the reply,

This works perfect for me.

But if you can explain me a little how it works, I would be great help.

Thank you so much.

Regards,
Prasanna.A

the formula reads as:

sum up column C if column A is equal to the current IR No and if column B is equal to the current ASSY NO