Hi All,
I have the follwoing data as shown below -
A
1 BRACES
2 BRACES
3 Primary
4 Primary
5 Oracle
6 BRACES
7 BRACES


my question is I want to pinck the distinct names from column A and to print
in diffrent sheet with count of each my out put should be like...
A B
1 BRACES 4
2 Primary 2
3 Oracle 1


TIA

Hi,

A couple of steps,
First select your list and then
Data|filter|Advanced Filter check unique records only and click OK
Copy this filtered list and paste it into A1 on Sheet 2

Then back to sheet 1 and data|Filter|Show all

Put this formula in B1 on sheet 2 and drag down to the length of column A

=COUNTIF(Sheet1!A1:A20,"="&A1)

Mike


"kiran" wrote:

Hi All,
I have the follwoing data as shown below -
A
1 BRACES
2 BRACES
3 Primary
4 Primary
5 Oracle
6 BRACES
7 BRACES


my question is I want to pinck the distinct names from column A and to print
in diffrent sheet with count of each my out put should be like...
A B
1 BRACES 4
2 Primary 2
3 Oracle 1


TIA

Sub countitems()

Sh1RowCount = 1
Sh2RowCount = 1
With Sheets("Sheet1")
Do While .Range("A" & Sh1RowCount) < ""
Data = .Range("A" & Sh1RowCount)
With Sheets("Sheet2")
Set c = .Columns("A").Find(what:=Data, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
.Range("A" & Sh2RowCount) = Data
.Range("B" & Sh2RowCount) = 1
Sh2RowCount = Sh2RowCount + 1
Else
c.Offset(0, 1) = c.Offset(0, 1) + 1
End If

End With
Sh1RowCount = Sh1RowCount + 1
Loop
End With
"kiran" wrote:

Hi All,
I have the follwoing data as shown below -
A
1 BRACES
2 BRACES
3 Primary
4 Primary
5 Oracle
6 BRACES
7 BRACES


my question is I want to pinck the distinct names from column A and to print
in diffrent sheet with count of each my out put should be like...
A B
1 BRACES 4
2 Primary 2
3 Oracle 1


TIA

In Case yo uhave spaces in you data replace the following statement

from
Data = .Range("A" & Sh1RowCount)
to
Data = trim(.Range("A" & Sh1RowCount))


"kiran" wrote:

Hi All,
I have the follwoing data as shown below -
A
1 BRACES
2 BRACES
3 Primary
4 Primary
5 Oracle
6 BRACES
7 BRACES


my question is I want to pinck the distinct names from column A and to print
in diffrent sheet with count of each my out put should be like...
A B
1 BRACES 4
2 Primary 2
3 Oracle 1


TIA