MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Sorting Data

Posted by Ken on November 21, 2001 10:49 AM

A1 "Code" B1 "Description" C1 "Amount"
A2 1 B2 "Postage" C2 "$6.00"
A3 2 B3 "Car Expenses" C3 "$12.00"
A4 1 B4 "Postage" C4 "$2.00"
A5 2 B5 "Car Expenses" C5 "$38.00"
A6 2 B6 "Car Expenses" C6 "$26.00"

I would like to sort this data like this.

D1 "Postage" E1 "Car Expenses"
D2 "$6.00" E2 "$12.00"
D3 "$2.00" E3 "$38.00"
D4 E4 "$26.00"

I understand about PivotTable but is there another way to do this?


Posted by Aladin Akyurek on November 21, 2001 11:28 AM

Ken --

Create a unique list of codes in D from D2 on (use Adv Filter, you have too many of them).

In E1 enter: Postage
In F1 enter: Car Expenses

In E2 enter: =SUMPRODUCT(($A$2:$A$100=$D2)*($B$2:$B$100=E$1),($C$2:$C$100))

Copy this first across then down.