How to combine few rows data into 1

E

EugeneTan79

New Member
Joined
Apr 10, 2018
Messages
2
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

[TABLE="width: 871"]
<colgroup><col><col><col><col span="8"><col></colgroup><tbody>[TR]
[TD][TABLE="width: 500, align: left"]
<tbody>[TR]
[TD]Index[/TD]
[TD]Name[/TD]
[TD]NRIC[/TD]
[TD]Class[/TD]
[TD]EL[/TD]
[TD]MA[/TD]
[TD]CL[/TD]
[TD]ML[/TD]
[TD]TL[/TD]
[TD]HU[/TD]
[/TR]
[TR]
[TD]1552[/TD]
[TD]John[/TD]
[TD]S1234[/TD]
[TD]4-2[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1552[/TD]
[TD]John[/TD]
[TD]S1234[/TD]
[TD]4-2[/TD]
[TD][/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1552[/TD]
[TD]John[/TD]
[TD]S1234[/TD]
[TD]4-2[/TD]
[TD][/TD]
[TD][/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1552[/TD]
[TD]John[/TD]
[TD]S1234[/TD]
[TD]4-2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]1552[/TD]
[TD]John[/TD]
[TD]S1234[/TD]
[TD]4-2[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Sort by date Sort by votes
so do you want a formula to give the last row in your table ?

[TABLE="width: 704"]
<colgroup><col width="64" span="11" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl22, width: 64"]Index[/TD]
[TD="class: xl22, width: 64"]Name[/TD]
[TD="class: xl22, width: 64"]NRIC[/TD]
[TD="class: xl22, width: 64"]Class[/TD]
[TD="class: xl22, width: 64"]EL[/TD]
[TD="class: xl22, width: 64"]MA[/TD]
[TD="class: xl22, width: 64"]CL[/TD]
[TD="class: xl22, width: 64"]ML[/TD]
[TD="class: xl22, width: 64"]TL[/TD]
[TD="class: xl22, width: 64"]HU[/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD="class: xl22, width: 64"]1552[/TD]
[TD="class: xl22, width: 64"]John[/TD]
[TD="class: xl22, width: 64"]S1234[/TD]
[TD="class: xl23, width: 64"]04-Feb[/TD]
[TD="class: xl22, width: 64"]2[/TD]
[TD="class: xl22, width: 64"] [/TD]
[TD="class: xl22, width: 64"] [/TD]
[TD="class: xl22, width: 64"] [/TD]
[TD="class: xl22, width: 64"] [/TD]
[TD="class: xl22, width: 64"] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl22, width: 64"]1552[/TD]
[TD="class: xl22, width: 64"]John[/TD]
[TD="class: xl22, width: 64"]S1234[/TD]
[TD="class: xl23, width: 64"]04-Feb[/TD]
[TD="class: xl22, width: 64"] [/TD]
[TD="class: xl22, width: 64"]4[/TD]
[TD="class: xl22, width: 64"] [/TD]
[TD="class: xl22, width: 64"] [/TD]
[TD="class: xl22, width: 64"] [/TD]
[TD="class: xl22, width: 64"] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl22, width: 64"]1552[/TD]
[TD="class: xl22, width: 64"]John[/TD]
[TD="class: xl22, width: 64"]S1234[/TD]
[TD="class: xl23, width: 64"]04-Feb[/TD]
[TD="class: xl22, width: 64"] [/TD]
[TD="class: xl22, width: 64"] [/TD]
[TD="class: xl22, width: 64"]5[/TD]
[TD="class: xl22, width: 64"] [/TD]
[TD="class: xl22, width: 64"] [/TD]
[TD="class: xl22, width: 64"] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl22, width: 64"]1552[/TD]
[TD="class: xl22, width: 64"]John[/TD]
[TD="class: xl22, width: 64"]S1234[/TD]
[TD="class: xl23, width: 64"]04-Feb[/TD]
[TD="class: xl22, width: 64"] [/TD]
[TD="class: xl22, width: 64"] [/TD]
[TD="class: xl22, width: 64"] [/TD]
[TD="class: xl22, width: 64"] [/TD]
[TD="class: xl22, width: 64"] [/TD]
[TD="class: xl22, width: 64"]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl22, width: 64"]7777[/TD]
[TD="class: xl22, width: 64"]bob[/TD]
[TD="class: xl22, width: 64"]s7654[/TD]
[TD="class: xl23, width: 64"]04-Feb[/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl22, width: 64"]7777[/TD]
[TD="class: xl22, width: 64"]bob[/TD]
[TD="class: xl22, width: 64"]s7654[/TD]
[TD="class: xl23, width: 64"]04-Feb[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl22, width: 64"]7777[/TD]
[TD="class: xl22, width: 64"]bob[/TD]
[TD="class: xl22, width: 64"]s7654[/TD]
[TD="class: xl23, width: 64"]04-Feb[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl22, width: 64"]7777[/TD]
[TD="class: xl22, width: 64"]bob[/TD]
[TD="class: xl22, width: 64"]s7654[/TD]
[TD="class: xl23, width: 64"]04-Feb[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl22, width: 64"]EL[/TD]
[TD="class: xl22, width: 64"]MA[/TD]
[TD="class: xl22, width: 64"]CL[/TD]
[TD="class: xl22, width: 64"]ML[/TD]
[TD="class: xl22, width: 64"]TL[/TD]
[TD="class: xl22, width: 64"]HU[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl22, width: 64"]1552[/TD]
[TD="class: xl22, width: 64"]John[/TD]
[TD="class: xl22, width: 64"]S1234[/TD]
[TD="class: xl23, width: 64"]04-Feb[/TD]
[TD="class: xl24"]2[/TD]
[TD="class: xl24"]4[/TD]
[TD="class: xl24"]5[/TD]
[TD="class: xl24"]0[/TD]
[TD="class: xl24"]0[/TD]
[TD="class: xl24"]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl22, width: 64"]7777[/TD]
[TD="class: xl22, width: 64"]bob[/TD]
[TD="class: xl22, width: 64"]s7654[/TD]
[TD="class: xl23, width: 64"]04-Feb[/TD]
[TD="class: xl24"]0[/TD]
[TD="class: xl24"]4[/TD]
[TD="class: xl24"]0[/TD]
[TD="class: xl24"]5[/TD]
[TD="class: xl24"]3[/TD]
[TD="class: xl24"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]the 2 under EL obtained by[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"]=SUMPRODUCT(($B$2:$B$8=$B12)*(E$2:E$8))[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]easy to suppress the 0's if you need to[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Hi,

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


Excel 2010
ABCDEFGHIJ
1IndexNameNRICClassELMACLMLTLHU
21552JohnS12342-Apr2
31552JohnS12342-Apr4
41552JohnS12342-Apr5
51552JohnS12342-Apr3
61552JohnS12342-Apr2453
7
8IndexNameNRICClassELMACLMLTLHU
91552JohnS12342-Apr2
101552JohnS12342-Apr4
111552JohnS12342-Apr5
121552JohnS12342-Apr3
131552JohnS12342-Apr2453
Sheet1
Cell Formulas
RangeFormula
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.
 
Last edited:
Upvote 0
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>
 
Upvote 0
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
ABCDEFGHIJ
1IndexNameNRICClassELMACLMLTLHU
21552JohnS12344-22
31552JohnS12344-24
41552JohnS12344-25
51552JohnS12344-23
67777bobS76544-24
77777bobS76544-25
87777bobS76544-23
9
10
11IndexNameNRICClassELMACLMLTLHU
121552JohnS12344-2245003
137777bobS76544-2040530
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),""))
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Solved
combine data from two worksheets with similar data but different formats
Replies
6
Views
91
DanteAmor
DanteAmor
V
  • Solved
Show duplicates from across multiple ranges in one cell with a single nested formula?
Replies
9
Views
176
VikingLink
V
J
  • Question Question
Find Last Row of Column "A" +1 row and paste 4 columns to the right
Replies
1
Views
389
dreid1011
dreid1011
S
  • Question Question
VBA code for adding new group of columns with 3 condictions from 2 dictionary and 1 array Then fill in data from value from original columns
Replies
1
Views
114
sijpie
sijpie
J
  • Question Question
COMBINING ROW DATA FROM 2 ROWS TO 1
Replies
8
Views
368
JLouis
J

Forum statistics

Threads
1,223,911
Messages
6,175,327
Members
452,635
Latest member
laura12345

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top