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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|