Formula to Count and Group Items

[k64]

I have data that looks like this:

Group	Item	Value
1	A	0
1	A	2
1	C	0
1	B	1
1	A	1
2	B	1
2	B	2
2	A	0
2	A	0
2	A	2
3	A	1
3	C	0
3	A	1
3	C	2
3	A	1

I want to use formulas to summarize the table like this:

Group
1	1A0	1A1	1A2	1B1	1C0
2	2A0	1A2	1B1	1B2
3	3A1	1C0	1C2

I know that this can be done via VBA (and would be easier), but the file is to be shared via email and our email filter doesn't like .xlsm files, so I'm trying to avoid this if possible.

[Richard Buttrey]

Not quite the same layout, but being a Pivot Table arguably much simpler and far more flexible and efficient.

[k64]

Thank you Richard,

Pivot tables are definitely more efficient. I am looking to have 1 row per group and only 6 columns in the resulting table, in the format shown. I wasn't able to produce this result from a Pivot table. I think the Pivot table provides a good supplement to the report I want though, so thanks for the contribution!

K64

[JeteMc]

Note: This solution does not arrange the summarized table in the same order as in post #1.
The solution makes use of four helper columns, which could be hidden for aesthetic purposes. The formulas of the helper columns respectively
1) combine the item and value: =B2&C2
2) count per group: =COUNTIFS(D$2:D$16,D2,A$2:A$16,A2)
3) combine count, item and value: =IF(E2=1,E2&D2,IF(COUNTIFS(D$2:D2,D2,A$2:A2,A2)=E2,E2&D2,""))
4) rank by group: =IF(F2="","",SUMPRODUCT(($A$2:$A$16=A2)*($F$2:$F$16>F2))+1)
The array entered formula* which populates the table (J7:N9):

Please Login or Register  to view this content.

Array entered formulas are confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
Let me know if you have any questions.

[k64]

Thanks JeteMc! That is exactly what I was hoping I could do. It doesn't matter that it's not in the same order, since if I need to, I can define a custom order and rank by that.

[JeteMc]

You're Welcome and thank you for the feedback. Please take a moment to mark the thread 'Solved' using the thread tools link above your first post. I hope that you have a blessed day.