Excel Formula question

[Jarad]

Hi All,

Im new to posting on this forum and have learnt alot viewing i
frequently!

I have a query with regards to excel formula. If not allowed to pos
queries, i do apologise.

Please see attached jpg. The data is on the left. On the right is
"summary" of this data that i need to automate into formula
(Everything in RED needs to be dynamic).
It also needs to stay in this exact format. No additional cells o
fields can be created.
Can anybody assist?

Thanks in advance.
Regards
Jarad

+-------------------------------------------------------------------
|Filename: Query.JPG
|Download: http://www.excelforum.com/attachment.php?postid=4409
+-------------------------------------------------------------------

 

[Bernard Liengme]

No attached diagram.
Try to give an example of the data in your message and then a simple
statement of what is needed.
best wishes (we want to help!)

 

[Bob Phillips]

Slightly upside down

E2: Total
F2: =SUMPRODUCT((A2:A20<>"")/COUNTIF(A2:A20,A2:A20&""))
G2: =SUMPRODUCT((B2:B20<>"")/COUNTIF(B2:B20,B2:B20&""))

F3: = A2
G3:
=IF(F3="","",SUM(--(FREQUENCY(IF($A$2:$A$20=F3,MATCH($B$2:$B$20,$B$2:$B$20,0
)),ROW(INDIRECT("1:"&ROWS($B$2:$B$20))))>0)))

F4: =IF(ISERROR(MATCH(0,COUNTIF(F$3:F3,$A$2:$A$20&""),0)),"",
INDEX(IF(ISBLANK($A$2:$A$20),"",$A$2:$A$20),MATCH(0,COUNTIF(F$3:F3,$A$2:$A$2
0&""),0)))

G3 and F4 are array formulae, which should be committed with
Ctrl-Shift-Enter, not just Enter.

Copy F4 down as far as you need, then copy G3 down to the same row.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)