Consolidating Data

[chandy]

Hi,


I have to create a table from a set of data in a spreadsheet (sample data below, the sheet has more than 1000 rows of data)

Name Strategy Maturity

ARH Grow Initial

Atal Maintain Repeatable

BAS Limited Operation Defined

Beta Retire Managed

BI Grow Optimised


I need to generate a table which will provide the summary view as below:


Maturity Initial Repeatable Defined Managed Optimised (Row)

Maintain (Count) (Count)

Grow (Count) (Count)

Retire (Count) (Count)

Limited Operation (Count) (Count)


Any suggestions on how to generate the table?


thanks in advance

 

[Hui]

I have assumed your data is in Sheet1

Name Column A

Strategy Column B

Maturity Column C


1. Apply named Ranges to your 3 columns

[pre]

Maturity	=OFFSET(Sheet1!$C$1,1,0,COUNTA(Sheet1!$C:$C)-1)
Name	=OFFSET(Sheet1!$A$1,1,0,COUNTA(Sheet1!$A:$A)-1)
Strategy	=OFFSET(Sheet1!$B$1,1,0,COUNTA(Sheet1!$B:$B)-1)[/pre]
2. Setup a report area

H1:L1 [code]Initial, Repeatable, Defined, Managed, Optimised

G2:G5 Maintain, Grow Retire, Limited Operation[/code]


3. Add A Formula

H2: =SUMPRODUCT(1*(Strategy=$G2)*(Maturity=H$1))


Copy across and down to L5


If thats too confusing have a look at: http://rapidshare.com/files/431608638/Chandy.xlsx