[Grouping Data] Moving Into One Row

[iSuw]

Hi there!

I have a list of data, many with a similar data code. As a result, I'd like to group the datas with the similar data code. Here's an example:

<STYLE type=text/css>
table.tableizer-table {border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif; font-size: 12px;} .tableizer-table td {padding: 4px; margin: 3px; border: 1px solid #ccc;}
.tableizer-table th {background-color: #104E8B; color: #FFF; font-weight: bold;}
</STYLE>
<TABLE class=tableizer-table>
<TBODY><TR class=tableizer-firstrow><TH>Code</TH><TH>Quantity</TH><TH>Amount</TH></TR><TR><TD>1110</TD><TD>15</TD><TD>8,000.00 </TD></TR><TR><TD>1110</TD><TD>18</TD><TD>4,000.00 </TD></TR><TR><TD>1547</TD><TD>40</TD><TD>2,000.00 </TD></TR><TR><TD>1547</TD><TD>58</TD><TD>3,000.00 </TD></TR><TR><TD>1558</TD><TD>78</TD><TD>4,000.00 </TD></TR><TR><TD>1558</TD><TD>102</TD><TD>1,500.00</TD></TR>


</TABLE>

When "grouped" together, the result would be:

<STYLE type=text/css>
table.tableizer-table {border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif; font-size: 12px;} .tableizer-table td {padding: 4px; margin: 3px; border: 1px solid #ccc;}
.tableizer-table th {background-color: #104E8B; color: #FFF; font-weight: bold;}
</STYLE>
<TABLE class=tableizer-table>
<TBODY><TR class=tableizer-firstrow><TH>Code</TH><TH>Quantity</TH><TH>Amount</TH></TR><TR><TD>1110</TD><TD>33</TD><TD>12,000.00 </TD></TR><TR><TD>1547</TD><TD>98</TD><TD>5,000.00 </TD></TR><TR><TD>1558</TD><TD>180</TD><TD>5,500.00 </TD></TR>


</TABLE>

Does anyone have any tips on how to do this? I'm looking for an alternative to a Pivot Table.

Thanks!

 

[Yahya]

use Sutotal Or PivotTable

 

[Biz]

Making Subtotal using VBA

Sub Subtotal()
 
    ActiveSheet.UsedRange.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(2, 3), _
        Replace:=True, PageBreaks:=False, SummaryBelowData:=True
    Columns("A:C").EntireColumn.AutoFit
    ActiveSheet.Outline.ShowLevels RowLevels:=2
End Sub

Biz

 

[villy]

You can do something like this but I know it needs a little modification I'm working on it but for the mean time you can play with this one:
I used columns K,L,M and O,P as outputrespectively.

O12=SUMIF(K$12:K$17,INDEX(K$12:K$17,MATCH(K12,K13:K$17,0),1),L12:L$17)
P12=SUMIF($K$12:$K$17,INDEX($K$12:$K$17,MATCH($K12,$K13:$K$17,0),1),M12:M$17)
change the letters as you like to fit your ranges.

 

[Yahya]

another way by formula

Excel Workbook
	A	B	C	D	E	F	G	H
1	Code	Quantity	Amount			Code	Quantity	Amount
2	1110	15	8000			1110	33	12000
3	1110	18	4000			1547	98	5000
4	1547	40	2000			1558	180	5500
5	1547	58	3000
6	1558	78	4000
7	1558	102	1500
Sheet1

Excel 2007

Cell Formulas
Range	Formula
G2	=SUMIF($A$2:$A$7,$F2,B$2:B$7)
H2	=SUMIF($A$2:$A$7,$F2,C$2:C$7)

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.