Summarizing Data

[cb]

Hello,

I am working on summarizing data that was given to me in a format like:

Ref Num Group Score Category (below are in one cell)
205 AA 3 Asset, Liability
206 BB 2 Revenue, AR, Asset
207 AA 1 Liability
208 CC 3 Expense, Liability
208 AA 2 Asset, Expense
205 CC 2 Asset

I want to end up summarizing the information into a report/pivot table,
chart, something that would look something like:

Group Category Ref Score
AA Asset 205 3
AA Liability 205 3
AA Asset 208 2
BB Revenue 206 2
BB AR 206 2
BB Asset 206 2
…
This would break out the category column to allow for each category type to
have its own line. I am not sure if this possible but I am looking for ideas.

Thanks,
CB

 

[Steve Dunn]

Hi CB, this is as close as I can get in the time I have left today. Hold
onto your hat!

Start by using "Text to Columns", splitting by delimiters "," and " ", so
that your data is put into individual columns, then use the formulae below.
I have assumed that your data is now in columns A2:G10 (added one column
and a few rows for luck!), and that your results will be in J2:Mx.

J2: =$B$2
K2: =$D$2
L2: =$A$2
M2: =$C$2

J3:

=IF(SUMPRODUCT(($J$2:$J2=$J2)*($L$2:$L2=$L2))<
COUNTA(OFFSET($D$2:$H$10,
MATCH(1,INDEX(($A$2:$A$10=$L2)*($B$2:$B$10=$J2),),0)-
1,,1)),$J2,INDEX($B$2:$B$10,MATCH(1,INDEX(($A$2:$A$10=
$L2)*($B$2:$B$10=$J2),),0)+1))

K3:

=INDEX($D$2:$G$10,MATCH(1,INDEX(($A$2:$A$10=$L3)*
($B$2:$B$10=$J3),),0),SUMPRODUCT(($J$2:$J3=$J3)*
($L$2:$L3=$L3)))

L3:

=IF(SUMPRODUCT(($J$2:$J2=$J2)*($L$2:$L2=$L2))<
COUNTA(OFFSET($D$2:$H$10,
MATCH(1,INDEX(($A$2:$A$10=$L2)*($B$2:$B$10=$J2),),0)-
1,,1)),$L2,INDEX($A$2:$A$10,MATCH(1,
INDEX(($A$2:$A$10=$L2)*($B$2:$B$10=$J2),),0)+1))

M3:

=INDEX($C$2:$C$10,MATCH(1,INDEX(($A$2:$A$10=$L3)*
($B$2:$B$10=$J3),),0))

Now copy J3:M3 down as far as required.

The order of results is not perfect in relation to your example results, but
you can copy/paste values, then do a sort.

HTH
Steve D.