How to combine few rows data into 1

[EugeneTan79]

Hi,
Anyone is able to help me to combine the different rows data into 1 row. I have data that are show in the first 5 rows. What formula should I use so that I am able to combine the data into the last row. Tks

Index Name NRIC Class EL MA CL ML TL HU 1552 John S1234 4-2 2 1552 John S1234 4-2 4 1552 John S1234 4-2 5 1552 John S1234 4-2 3 1552 John S1234 4-2 2 4 5 3 <tbody> </tbody>

<colgroup><col><col><col><col span="8"><col></colgroup><tbody>
</tbody>

 

[oldbrewer]

so do you want a formula to give the last row in your table ?

Index	Name	NRIC	Class	EL	MA	CL	ML	TL	HU
1552	John	S1234	04-Feb	2
1552	John	S1234	04-Feb		4
1552	John	S1234	04-Feb			5
1552	John	S1234	04-Feb						3
7777	bob	s7654	04-Feb		4
7777	bob	s7654	04-Feb				5
7777	bob	s7654	04-Feb					3
7777	bob	s7654	04-Feb
				EL	MA	CL	ML	TL	HU
1552	John	S1234	04-Feb	2	4	5	0	0	3
7777	bob	s7654	04-Feb	0	4	0	5	3	0
				the 2 under EL obtained by
				=SUMPRODUCT(($B$2:$B$8=$B12)*(E$2:E$8))
				easy to suppress the 0's if you need to

<colgroup><col width="64" span="11" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[jtakw]

Hi,

As you didn't specify the criterion for the results, here are 2 examples:

Excel 2010
	A	B	C	D	E	F	G	H	I	J
1	Index	Name	NRIC	Class	EL	MA	CL	ML	TL	HU
2	1552	John	S1234	2-Apr	2
3	1552	John	S1234	2-Apr		4
4	1552	John	S1234	2-Apr			5
5	1552	John	S1234	2-Apr						3
6	1552	John	S1234	2-Apr	2	4	5			3
7
8	Index	Name	NRIC	Class	EL	MA	CL	ML	TL	HU
9	1552	John	S1234	2-Apr	2
10	1552	John	S1234	2-Apr		4
11	1552	John	S1234	2-Apr			5
12	1552	John	S1234	2-Apr						3
13	1552	John	S1234	2-Apr	2	4	5			3
Sheet1

Cell Formulas
Range	Formula
E6	=IF(SUM(E$2:E$5)=0,"",SUM(E$2:E$5))
E13	=IF(SUMIFS(E$9:E$12,$A9:$A12,$A13,$B9:$B12,$B13,$C9:$C12,$C13,$D9:$D12,$D13)=0,"",SUMIFS(E$9:E$12,$A9:$A12,$A13,$B9:$B12,$B13,$C9:$C12,$C13,$D9:$D12,$D13))

Formulas copied across.
D6 formula if it's a simple sum.
D13 formula if Index, Name, NRIC, Class has to match.

 

[EugeneTan79]

Hi,

Sorry. Let me repost my question again. I have more than 100 students taking the different subjects and different students will take different number of subjects. I am now just using 2 students as example. How am I able to get the output at the last 2 rows. Really appreciate the help

Index	Name	NRIC	Class	EL	MA	CL	ML	TL	HU
1552	John	S1234	4-2	2
1552	John	S1234	4-2		4
1552	John	S1234	4-2			5
1552	John	S1234	4-2						3
7777	bob	S7654	4-2		4
7777	bob	S7654	4-2				5
7777	bob	S7654	4-2					3
1552	John	S1234	4-2	2	4	5			3
7777	Bob	S7654	4-2		4		5	3

<tbody>
</tbody>

 

[Peter_SSs]

Welcome to the MrExcel board!

See if this would work for you.

Formula in A12 is copied down
Formula in B12 is copied across to column D and down
Formula in E12 is copied across to column J and down

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	Index	Name	NRIC	Class	EL	MA	CL	ML	TL	HU
2	1552	John	S1234	4-2	2
3	1552	John	S1234	4-2		4
4	1552	John	S1234	4-2			5
5	1552	John	S1234	4-2						3
6	7777	bob	S7654	4-2		4
7	7777	bob	S7654	4-2				5
8	7777	bob	S7654	4-2					3
9
10
11	Index	Name	NRIC	Class	EL	MA	CL	ML	TL	HU
12	1552	John	S1234	4-2	2	4	5	0	0	3
13	7777	bob	S7654	4-2	0	4	0	5	3	0
14
Sheet3

If you don't want the '0' values in columns E:J you could either
- custom format those columns as 0;0;
or
- change the E2 formula to =IF($A12="","",IF(SUMIF($A$2:$A$8,$A12,E$2:E$8),SUMIF($A$2:$A$8,$A12,E$2:E$8),""))