Hi All

I have an Excel Sheet named "Stats"and Column D contains a list of
alphanumeric numbers down to row 2000 after every 20 rows there are 2 rows of
headings is it possible to have a routine that will find the same unique
numbers all the way down to row 2000 and copying them across to Column E on
finding the same number no matter how many times it appears -- and without
the 2 rows of headings affecting the search capability.
--
Many thanks

hazel

Hi Hazel,

what do you mean with "the same unique numbers"?

Have a look at this page http://www.j-walk.com/ss/excel/tips/tip15.htm
to see a clever technique to identify unique items.

Regards,
Ivan

Hi

I got that one wrong eh!! The number is a part number so it could be
repeated on several rows through out Column D. For example the number could
be BG09Z56743 all the part numbers have 10 digits and I suppose they are
unique as each number refers to a specific part of the build project. My
bosses at this end want to know the number of times in a specific period that
it is ordered.
--
Many thanks

hazel


"Hazel" wrote:

Hi All

I have an Excel Sheet named "Stats"and Column D contains a list of
alphanumeric numbers down to row 2000 after every 20 rows there are 2 rows of
headings is it possible to have a routine that will find the same unique
numbers all the way down to row 2000 and copying them across to Column E on
finding the same number no matter how many times it appears -- and without
the 2 rows of headings affecting the search capability.
--
Many thanks

hazel

Hi Hazel

Just a though, base a Pivot table on the column and count the part numbers
with this. If the row headings are the same then these can be excluded from
the data in the field options.
--
Tony Green


"Hazel" wrote:

Hi

I got that one wrong eh!! The number is a part number so it could be
repeated on several rows through out Column D. For example the number could
be BG09Z56743 all the part numbers have 10 digits and I suppose they are
unique as each number refers to a specific part of the build project. My
bosses at this end want to know the number of times in a specific period that
it is ordered.
--
Many thanks

hazel


"Hazel" wrote:

Hi All

I have an Excel Sheet named "Stats"and Column D contains a list of
alphanumeric numbers down to row 2000 after every 20 rows there are 2 rows of
headings is it possible to have a routine that will find the same unique
numbers all the way down to row 2000 and copying them across to Column E on
finding the same number no matter how many times it appears -- and without
the 2 rows of headings affecting the search capability.
--
Many thanks

hazel

Hi Tony

Thanks for the info Pivot tables pretty cute eh!! spent a couple of hours
fiddling about on my own and got to the answers the big chiefs wanted now
having a crack at getting a macro to do it for me.

Thanks again

--
Many thanks

hazel


"ADG" wrote:

Hi Hazel

Just a though, base a Pivot table on the column and count the part numbers
with this. If the row headings are the same then these can be excluded from
the data in the field options.
--
Tony Green


"Hazel" wrote:

Hi

I got that one wrong eh!! The number is a part number so it could be
repeated on several rows through out Column D. For example the number could
be BG09Z56743 all the part numbers have 10 digits and I suppose they are
unique as each number refers to a specific part of the build project. My
bosses at this end want to know the number of times in a specific period that
it is ordered.
--
Many thanks

hazel


"Hazel" wrote:

Hi All

I have an Excel Sheet named "Stats"and Column D contains a list of
alphanumeric numbers down to row 2000 after every 20 rows there are 2 rows of
headings is it possible to have a routine that will find the same unique
numbers all the way down to row 2000 and copying them across to Column E on
finding the same number no matter how many times it appears -- and without
the 2 rows of headings affecting the search capability.
--
Many thanks

hazel

Hi Hazel

If you are still liooking to write your macro, have a look at the Question I
entered "Create a New Object". Using a collection to gather the data in
memory is one option. The Example by Harold Staff is an excellent
introduction to a couple of new topics. I used an example similar to yours to
for a test and it worked well.

--
Tony Green


"Hazel" wrote:

Hi Tony

Thanks for the info Pivot tables pretty cute eh!! spent a couple of hours
fiddling about on my own and got to the answers the big chiefs wanted now
having a crack at getting a macro to do it for me.

Thanks again

--
Many thanks

hazel


"ADG" wrote:

Hi Hazel

Just a though, base a Pivot table on the column and count the part numbers
with this. If the row headings are the same then these can be excluded from
the data in the field options.
--
Tony Green


"Hazel" wrote:

Hi

I got that one wrong eh!! The number is a part number so it could be
repeated on several rows through out Column D. For example the number could
be BG09Z56743 all the part numbers have 10 digits and I suppose they are
unique as each number refers to a specific part of the build project. My
bosses at this end want to know the number of times in a specific period that
it is ordered.
--
Many thanks

hazel


"Hazel" wrote:

Hi All

I have an Excel Sheet named "Stats"and Column D contains a list of
alphanumeric numbers down to row 2000 after every 20 rows there are 2 rows of
headings is it possible to have a routine that will find the same unique
numbers all the way down to row 2000 and copying them across to Column E on
finding the same number no matter how many times it appears -- and without
the 2 rows of headings affecting the search capability.
--
Many thanks

hazel

Hi Tony

Thanks for the info had a look but it appears to me that my novice
capabilities couldn't cope with class modules etc -- what I usually try and
do is use the macro recorder and hope to get somewhere near what I it to do
-- will also fiddle with the code that the recorder has produced and cross my
fingers that it works -- at the moment not having much success with this --
can't even get it to sort the pivot table
--
Many thanks

hazel


"ADG" wrote:

Hi Hazel

If you are still liooking to write your macro, have a look at the Question I
entered "Create a New Object". Using a collection to gather the data in
memory is one option. The Example by Harold Staff is an excellent
introduction to a couple of new topics. I used an example similar to yours to
for a test and it worked well.

--
Tony Green


"Hazel" wrote:

Hi Tony

Thanks for the info Pivot tables pretty cute eh!! spent a couple of hours
fiddling about on my own and got to the answers the big chiefs wanted now
having a crack at getting a macro to do it for me.

Thanks again

--
Many thanks

hazel


"ADG" wrote:

Hi Hazel

Just a though, base a Pivot table on the column and count the part numbers
with this. If the row headings are the same then these can be excluded from
the data in the field options.
--
Tony Green


"Hazel" wrote:

Hi

I got that one wrong eh!! The number is a part number so it could be
repeated on several rows through out Column D. For example the number could
be BG09Z56743 all the part numbers have 10 digits and I suppose they are
unique as each number refers to a specific part of the build project. My
bosses at this end want to know the number of times in a specific period that
it is ordered.
--
Many thanks

hazel


"Hazel" wrote:

Hi All

I have an Excel Sheet named "Stats"and Column D contains a list of
alphanumeric numbers down to row 2000 after every 20 rows there are 2 rows of
headings is it possible to have a routine that will find the same unique
numbers all the way down to row 2000 and copying them across to Column E on
finding the same number no matter how many times it appears -- and without
the 2 rows of headings affecting the search capability.
--
Many thanks

hazel

Hi Hazel

To sort your pivot table right click a cell on the table containing the part
no, select filed settings, the select advanced, then pick ascending rather
then manual sort order. This order this then stored for future use.

I used your problem to play with the code in Harald's example. Its not as
scary as it first seems. The below is my code which counts the part numbers
in column D starting at D2 and going to the first empty cell. The results
then are put in a new sheet. The code is attached to a button, and uses one
function. The entries in the class module are two lines. See below:

Class1 Module Code

Public PartCount As Long
Public PartNo As String

Code attached to a new button

Private Sub CommandButton2_Click()
Dim MyCol As Collection
Dim MyObj As Class1
Dim x As Long
Dim PartNo As String
Dim MaxRow As Long

MaxRow = Range("D2").End(xlDown).Row
Set MyCol = Nothing
Set MyCol = New Collection
For x = 2 To MaxRow
PartNo = Worksheets("Sheet1").Cells(x, 4).Value
If Len(PartNo) = 10 Then
If InCol(PartNo, MyCol) Then
Set MyObj = MyCol(PartNo)
MyObj.PartCount = MyObj.PartCount + 1
MyCol.Remove (PartNo)
MyCol.Add Item:=MyObj, key:=PartNo
Else
Set MyObj = New Class1
MyObj.PartNo = PartNo
MyObj.PartCount = 1
MyCol.Add Item:=MyObj, key:=PartNo
End If
End If
Next
Application.Workbooks.Add
With ActiveSheet
.Cells(1, 1) = "PartNo"
.Cells(1, 2) = "Count"
x = 2
For Each MyObj In MyCol
.Cells(x, 1).Value = MyObj.PartNo
.Cells(x, 2).Value = MyObj.PartCount
x = x + 1
Next
End With
Set MyCol = Nothing
End Sub

code for function (can be in a module or on the same worksheet module as the
button)

Public Function InCol(strPartNo As String, AnyCol As Collection) As Boolean
Dim v As Class1
On Error GoTo NotThere
Set v = AnyCol(strPartNo)
InCol = True
Exit Function
NotThe
InCol = False
End Function

--
Tony Green


"Hazel" wrote:

Hi Tony

Thanks for the info had a look but it appears to me that my novice
capabilities couldn't cope with class modules etc -- what I usually try and
do is use the macro recorder and hope to get somewhere near what I it to do
-- will also fiddle with the code that the recorder has produced and cross my
fingers that it works -- at the moment not having much success with this --
can't even get it to sort the pivot table
--
Many thanks

hazel


"ADG" wrote:

Hi Hazel

If you are still liooking to write your macro, have a look at the Question I
entered "Create a New Object". Using a collection to gather the data in
memory is one option. The Example by Harold Staff is an excellent
introduction to a couple of new topics. I used an example similar to yours to
for a test and it worked well.

--
Tony Green


"Hazel" wrote:

Hi Tony

Thanks for the info Pivot tables pretty cute eh!! spent a couple of hours
fiddling about on my own and got to the answers the big chiefs wanted now
having a crack at getting a macro to do it for me.

Thanks again

--
Many thanks

hazel


"ADG" wrote:

Hi Hazel

Just a though, base a Pivot table on the column and count the part numbers
with this. If the row headings are the same then these can be excluded from
the data in the field options.
--
Tony Green


"Hazel" wrote:

Hi

I got that one wrong eh!! The number is a part number so it could be
repeated on several rows through out Column D. For example the number could
be BG09Z56743 all the part numbers have 10 digits and I suppose they are
unique as each number refers to a specific part of the build project. My
bosses at this end want to know the number of times in a specific period that
it is ordered.
--
Many thanks

hazel


"Hazel" wrote:

Hi All

I have an Excel Sheet named "Stats"and Column D contains a list of
alphanumeric numbers down to row 2000 after every 20 rows there are 2 rows of
headings is it possible to have a routine that will find the same unique
numbers all the way down to row 2000 and copying them across to Column E on
finding the same number no matter how many times it appears -- and without
the 2 rows of headings affecting the search capability.
--
Many thanks

hazel