A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Summarizing Data



 
 
Thread Tools Display Modes
  #1  
Old June 2nd, 2010, 02:53 PM posted to microsoft.public.excel.worksheet.functions
CB
external usenet poster
 
Posts: 184
Default Summarizing Data

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

  #2  
Old June 2nd, 2010, 03:51 PM posted to microsoft.public.excel.worksheet.functions
Steve Dunn
external usenet poster
 
Posts: 192
Default Summarizing Data

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.



"cb" wrote in message
...
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


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 03:26 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.