Formula needed to extract rows based on some condition

[deepak_pathania]

I have one very big spreadsheet "Input Spreadsheet"
and I need to format the data base on some conditions
to create new out put file

Input Spreadsheet :-
--------------------
Column A | Column B
---------------------
AB | 12
AB | 13
AC | 14
AB | 12
AD | 12
AB | 12
AC | 14
AC | 13
AD | 12
AD | 15
AD | 15

Condition : Column A has 4 entries for "AB" . Column B has also
4 entries corresponding to "AB" i.e 3 rows of "12" and 1 row of
"13" . So we want to represet output data as given below . In Final spreadsheet I am showing count for "AB,"AC" and "AD"

Spreadsheet for example:
--------------------------------
Column A | Column B | COunt
---------------------------------
AB | 12 | 3
AB | 13 | 1

Final Formatted Data Spreadsheet :
--------------------------------
Column A | Column B | COunt
---------------------------------
AB | 12 | 3
AB | 13 | 1
AC | 14 | 2
AC | 13 | 1
AD | 12 | 2
AD | 15 | 2

Please provide me the formula to format the data in this way

 

[venkat1926]

keep the sheet open and try this macro

Sub test()
Dim r As Range, un As Range, cun As Range, c As Range
Dim ra As Range, rb As Range, myformula, myval
Set r = Range("A1").CurrentRegion
For Each c In r
c = Trim(c)
Next c
Set ra = Range(Range("A2"), Range("A2").End(xlDown))
'MsgBox ra.Address
Set rb = ra.Offset(0, 1)
'MsgBox rb.Address
Set un = Range("A1").End(xlDown).Offset(5, 0)
r.AdvancedFilter xlFilterCopy, , un, True
Set un = Range(un.Offset(1, 0), un.End(xlDown))
For Each cun In un
cun.Offset(0, 2).Value = "=sumproduct((" & ra.Address & "=""" & cun.Value & """)*(" & rb.Address & "=" & cun.Offset(0, 1).Value & "))"
Next cun
End Sub

Sub undo()
Range(Range("A2").End(xlDown).Offset(1, 0), Cells(Rows.Count, "A")).EntireRow.Delete

End Sub