Identify and count unique values

Hi all

I'm hoping someone out there can suggest a way forward with a spreadsheet
application Ive recently developed.

The spreadsheet contains data from a third party application which in effect
produces a "csv" file with the planned shifts for a group of employees. My
application is designed to analyse the hours being worked in various
categories from the data contained in the spreadsheet.

The user has the option from the third party application to select more than
one employee and more than one week. If that option is selected the output is
sorted by employee then by date. So employee 1 has a number of lines under
week 1 followed by a number of lines under week 2 followed by employee 2 with
week 1 and employee 2 with week 2 etc.

Part of my analysis of the data requires that I know the number of weeks in
the report. Presently that is done by way of a text box on the user form
asking the user to select 1 or 2 or 3 etc.

I'd prefer if the number of weeks could be identified programatically so my
question is :

Is there a way we can count the number of unique values from all of the
values in a column .....

Emp1, 22/05/06
Emp1, 29/05/06
Emp2, 22/05/06
Emp2, 29/05/06

would give the result 2

Note: the dates are not in consecutive rows. There is data in rows between
the firstd ate for Emp1 and second date for Emp2 etc., however the only date
format which exists in the column is that against the employee name. None of
the other rows between employee names contains a date format

I hope I've explained my problem and add that I'm happy with a suggestion
for the principle of how to do it as I'm confident in my own coding to be
able to move forward if I can just have an idea as to how I could appraoch it.

Any help or suggestions would be greatly appreciated.

Regards

Michael
in employee order

Have you looked at using advanced filters? This can be configured to
produce a list of unique values that may provide the list you require.

If not then you might consider using a Collection Object to build a unique
list

--
Cheers
Nigel



"mabond" <[email protected]> wrote in message
news:[email protected]...
> Hi all
>
> I'm hoping someone out there can suggest a way forward with a spreadsheet
> application Ive recently developed.
>
> The spreadsheet contains data from a third party application which in
> effect
> produces a "csv" file with the planned shifts for a group of employees. My
> application is designed to analyse the hours being worked in various
> categories from the data contained in the spreadsheet.
>
> The user has the option from the third party application to select more
> than
> one employee and more than one week. If that option is selected the output
> is
> sorted by employee then by date. So employee 1 has a number of lines under
> week 1 followed by a number of lines under week 2 followed by employee 2
> with
> week 1 and employee 2 with week 2 etc.
>
> Part of my analysis of the data requires that I know the number of weeks
> in
> the report. Presently that is done by way of a text box on the user form
> asking the user to select 1 or 2 or 3 etc.
>
> I'd prefer if the number of weeks could be identified programatically so
> my
> question is :
>
> Is there a way we can count the number of unique values from all of the
> values in a column .....
>
> Emp1, 22/05/06
> Emp1, 29/05/06
> Emp2, 22/05/06
> Emp2, 29/05/06
>
> would give the result 2
>
> Note: the dates are not in consecutive rows. There is data in rows between
> the firstd ate for Emp1 and second date for Emp2 etc., however the only
> date
> format which exists in the column is that against the employee name. None
> of
> the other rows between employee names contains a date format
>
> I hope I've explained my problem and add that I'm happy with a suggestion
> for the principle of how to do it as I'm confident in my own coding to be
> able to move forward if I can just have an idea as to how I could appraoch
> it.
>
> Any help or suggestions would be greatly appreciated.
>
> Regards
>
> Michael
> in employee order

Nigel

Thanks, it was a push in the right direction I needed. Because of the layout
of the exported file from the third party app the Filters was not an easy
option....but the collection object came up with the result. I'm already
looping through each line in the file so I included the following to identify
unique dates

Dim x As New Collection
Dim y As Integer
Dim MyObject
y = 1

Dim dateExists As Boolean
dateExists = False

x.Add Item:=Cells(4, 5).Value ' the first date is always in this cell

Do
If Cells(y, 4).Value = "Monday" Then
dateExists = False
For Each MyObject In x
If MyObject = Cells(y, 5).Value Then
dateExists = True
End If
Next MyObject
If dateExists = False Then
x.Add Item:=Cells(y, 5).Value
End If
End If
y = y + 1
Loop Until y = 1000

z = x.Count

Many thanks again

Regards

Michael Bond

"Nigel" wrote:

> Have you looked at using advanced filters? This can be configured to
> produce a list of unique values that may provide the list you require.
>
> If not then you might consider using a Collection Object to build a unique
> list
>
> --
> Cheers
> Nigel
>
>
>
> "mabond" <[email protected]> wrote in message
> news:[email protected]...
> > Hi all
> >
> > I'm hoping someone out there can suggest a way forward with a spreadsheet
> > application Ive recently developed.
> >
> > The spreadsheet contains data from a third party application which in
> > effect
> > produces a "csv" file with the planned shifts for a group of employees. My
> > application is designed to analyse the hours being worked in various
> > categories from the data contained in the spreadsheet.
> >
> > The user has the option from the third party application to select more
> > than
> > one employee and more than one week. If that option is selected the output
> > is
> > sorted by employee then by date. So employee 1 has a number of lines under
> > week 1 followed by a number of lines under week 2 followed by employee 2
> > with
> > week 1 and employee 2 with week 2 etc.
> >
> > Part of my analysis of the data requires that I know the number of weeks
> > in
> > the report. Presently that is done by way of a text box on the user form
> > asking the user to select 1 or 2 or 3 etc.
> >
> > I'd prefer if the number of weeks could be identified programatically so
> > my
> > question is :
> >
> > Is there a way we can count the number of unique values from all of the
> > values in a column .....
> >
> > Emp1, 22/05/06
> > Emp1, 29/05/06
> > Emp2, 22/05/06
> > Emp2, 29/05/06
> >
> > would give the result 2
> >
> > Note: the dates are not in consecutive rows. There is data in rows between
> > the firstd ate for Emp1 and second date for Emp2 etc., however the only
> > date
> > format which exists in the column is that against the employee name. None
> > of
> > the other rows between employee names contains a date format
> >
> > I hope I've explained my problem and add that I'm happy with a suggestion
> > for the principle of how to do it as I'm confident in my own coding to be
> > able to move forward if I can just have an idea as to how I could appraoch
> > it.
> >
> > Any help or suggestions would be greatly appreciated.
> >
> > Regards
> >
> > Michael
> > in employee order
>
>
>

Think you missed how to use the collection to do this:

Dim x As New Collection
Dim y As Integer
y = 1

Do
If Cells(y, 4).Value = "Monday" Then
On Error Resume Next
x.Add Item:=Cells(y, 5).Value, cells(y,5).Text
On Error goto 0
End If
y = y + 1
Loop Until y = 1000

z = x.Count

--
Regards,
Tom Ogilvy


"mabond" wrote:

> Nigel
>
> Thanks, it was a push in the right direction I needed. Because of the layout
> of the exported file from the third party app the Filters was not an easy
> option....but the collection object came up with the result. I'm already
> looping through each line in the file so I included the following to identify
> unique dates
>
> Dim x As New Collection
> Dim y As Integer
> Dim MyObject
> y = 1
>
> Dim dateExists As Boolean
> dateExists = False
>
> x.Add Item:=Cells(4, 5).Value ' the first date is always in this cell
>
> Do
> If Cells(y, 4).Value = "Monday" Then
> dateExists = False
> For Each MyObject In x
> If MyObject = Cells(y, 5).Value Then
> dateExists = True
> End If
> Next MyObject
> If dateExists = False Then
> x.Add Item:=Cells(y, 5).Value
> End If
> End If
> y = y + 1
> Loop Until y = 1000
>
> z = x.Count
>
> Many thanks again
>
> Regards
>
> Michael Bond
>
> "Nigel" wrote:
>
> > Have you looked at using advanced filters? This can be configured to
> > produce a list of unique values that may provide the list you require.
> >
> > If not then you might consider using a Collection Object to build a unique
> > list
> >
> > --
> > Cheers
> > Nigel
> >
> >
> >
> > "mabond" <[email protected]> wrote in message
> > news:[email protected]...
> > > Hi all
> > >
> > > I'm hoping someone out there can suggest a way forward with a spreadsheet
> > > application Ive recently developed.
> > >
> > > The spreadsheet contains data from a third party application which in
> > > effect
> > > produces a "csv" file with the planned shifts for a group of employees. My
> > > application is designed to analyse the hours being worked in various
> > > categories from the data contained in the spreadsheet.
> > >
> > > The user has the option from the third party application to select more
> > > than
> > > one employee and more than one week. If that option is selected the output
> > > is
> > > sorted by employee then by date. So employee 1 has a number of lines under
> > > week 1 followed by a number of lines under week 2 followed by employee 2
> > > with
> > > week 1 and employee 2 with week 2 etc.
> > >
> > > Part of my analysis of the data requires that I know the number of weeks
> > > in
> > > the report. Presently that is done by way of a text box on the user form
> > > asking the user to select 1 or 2 or 3 etc.
> > >
> > > I'd prefer if the number of weeks could be identified programatically so
> > > my
> > > question is :
> > >
> > > Is there a way we can count the number of unique values from all of the
> > > values in a column .....
> > >
> > > Emp1, 22/05/06
> > > Emp1, 29/05/06
> > > Emp2, 22/05/06
> > > Emp2, 29/05/06
> > >
> > > would give the result 2
> > >
> > > Note: the dates are not in consecutive rows. There is data in rows between
> > > the firstd ate for Emp1 and second date for Emp2 etc., however the only
> > > date
> > > format which exists in the column is that against the employee name. None
> > > of
> > > the other rows between employee names contains a date format
> > >
> > > I hope I've explained my problem and add that I'm happy with a suggestion
> > > for the principle of how to do it as I'm confident in my own coding to be
> > > able to move forward if I can just have an idea as to how I could appraoch
> > > it.
> > >
> > > Any help or suggestions would be greatly appreciated.
> > >
> > > Regards
> > >
> > > Michael
> > > in employee order
> >
> >
> >

Thanks Tom

Will that avoid an entry in the collection list being duplicated? As we loop
through each line of the file a sinlge date is repeated for every employee
name it finds.

If it does it shows that I've still got a lot to learn and, though my method
works, your suggestion is clearly better and more concise.

Thanks and regards

Michael Bond

"Tom Ogilvy" wrote:

> Think you missed how to use the collection to do this:
>
> Dim x As New Collection
> Dim y As Integer
> y = 1
>
> Do
> If Cells(y, 4).Value = "Monday" Then
> On Error Resume Next
> x.Add Item:=Cells(y, 5).Value, cells(y,5).Text
> On Error goto 0
> End If
> y = y + 1
> Loop Until y = 1000
>
> z = x.Count
>
> --
> Regards,
> Tom Ogilvy
>
>
> "mabond" wrote:
>
> > Nigel
> >
> > Thanks, it was a push in the right direction I needed. Because of the layout
> > of the exported file from the third party app the Filters was not an easy
> > option....but the collection object came up with the result. I'm already
> > looping through each line in the file so I included the following to identify
> > unique dates
> >
> > Dim x As New Collection
> > Dim y As Integer
> > Dim MyObject
> > y = 1
> >
> > Dim dateExists As Boolean
> > dateExists = False
> >
> > x.Add Item:=Cells(4, 5).Value ' the first date is always in this cell
> >
> > Do
> > If Cells(y, 4).Value = "Monday" Then
> > dateExists = False
> > For Each MyObject In x
> > If MyObject = Cells(y, 5).Value Then
> > dateExists = True
> > End If
> > Next MyObject
> > If dateExists = False Then
> > x.Add Item:=Cells(y, 5).Value
> > End If
> > End If
> > y = y + 1
> > Loop Until y = 1000
> >
> > z = x.Count
> >
> > Many thanks again
> >
> > Regards
> >
> > Michael Bond
> >
> > "Nigel" wrote:
> >
> > > Have you looked at using advanced filters? This can be configured to
> > > produce a list of unique values that may provide the list you require.
> > >
> > > If not then you might consider using a Collection Object to build a unique
> > > list
> > >
> > > --
> > > Cheers
> > > Nigel
> > >
> > >
> > >
> > > "mabond" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Hi all
> > > >
> > > > I'm hoping someone out there can suggest a way forward with a spreadsheet
> > > > application Ive recently developed.
> > > >
> > > > The spreadsheet contains data from a third party application which in
> > > > effect
> > > > produces a "csv" file with the planned shifts for a group of employees. My
> > > > application is designed to analyse the hours being worked in various
> > > > categories from the data contained in the spreadsheet.
> > > >
> > > > The user has the option from the third party application to select more
> > > > than
> > > > one employee and more than one week. If that option is selected the output
> > > > is
> > > > sorted by employee then by date. So employee 1 has a number of lines under
> > > > week 1 followed by a number of lines under week 2 followed by employee 2
> > > > with
> > > > week 1 and employee 2 with week 2 etc.
> > > >
> > > > Part of my analysis of the data requires that I know the number of weeks
> > > > in
> > > > the report. Presently that is done by way of a text box on the user form
> > > > asking the user to select 1 or 2 or 3 etc.
> > > >
> > > > I'd prefer if the number of weeks could be identified programatically so
> > > > my
> > > > question is :
> > > >
> > > > Is there a way we can count the number of unique values from all of the
> > > > values in a column .....
> > > >
> > > > Emp1, 22/05/06
> > > > Emp1, 29/05/06
> > > > Emp2, 22/05/06
> > > > Emp2, 29/05/06
> > > >
> > > > would give the result 2
> > > >
> > > > Note: the dates are not in consecutive rows. There is data in rows between
> > > > the firstd ate for Emp1 and second date for Emp2 etc., however the only
> > > > date
> > > > format which exists in the column is that against the employee name. None
> > > > of
> > > > the other rows between employee names contains a date format
> > > >
> > > > I hope I've explained my problem and add that I'm happy with a suggestion
> > > > for the principle of how to do it as I'm confident in my own coding to be
> > > > able to move forward if I can just have an idea as to how I could appraoch
> > > > it.
> > > >
> > > > Any help or suggestions would be greatly appreciated.
> > > >
> > > > Regards
> > > >
> > > > Michael
> > > > in employee order
> > >
> > >
> > >

Tom

don't bother replying to my question. I coded it with your suggestion and it
worked as I need it to. Thanks for your additional guidance here. Very neat
solution compared with my amatuerish attempt.

Regards

Michael Bond



"Tom Ogilvy" wrote:

> Think you missed how to use the collection to do this:
>
> Dim x As New Collection
> Dim y As Integer
> y = 1
>
> Do
> If Cells(y, 4).Value = "Monday" Then
> On Error Resume Next
> x.Add Item:=Cells(y, 5).Value, cells(y,5).Text
> On Error goto 0
> End If
> y = y + 1
> Loop Until y = 1000
>
> z = x.Count
>
> --
> Regards,
> Tom Ogilvy
>
>
> "mabond" wrote:
>
> > Nigel
> >
> > Thanks, it was a push in the right direction I needed. Because of the layout
> > of the exported file from the third party app the Filters was not an easy
> > option....but the collection object came up with the result. I'm already
> > looping through each line in the file so I included the following to identify
> > unique dates
> >
> > Dim x As New Collection
> > Dim y As Integer
> > Dim MyObject
> > y = 1
> >
> > Dim dateExists As Boolean
> > dateExists = False
> >
> > x.Add Item:=Cells(4, 5).Value ' the first date is always in this cell
> >
> > Do
> > If Cells(y, 4).Value = "Monday" Then
> > dateExists = False
> > For Each MyObject In x
> > If MyObject = Cells(y, 5).Value Then
> > dateExists = True
> > End If
> > Next MyObject
> > If dateExists = False Then
> > x.Add Item:=Cells(y, 5).Value
> > End If
> > End If
> > y = y + 1
> > Loop Until y = 1000
> >
> > z = x.Count
> >
> > Many thanks again
> >
> > Regards
> >
> > Michael Bond
> >
> > "Nigel" wrote:
> >
> > > Have you looked at using advanced filters? This can be configured to
> > > produce a list of unique values that may provide the list you require.
> > >
> > > If not then you might consider using a Collection Object to build a unique
> > > list
> > >
> > > --
> > > Cheers
> > > Nigel
> > >
> > >
> > >
> > > "mabond" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Hi all
> > > >
> > > > I'm hoping someone out there can suggest a way forward with a spreadsheet
> > > > application Ive recently developed.
> > > >
> > > > The spreadsheet contains data from a third party application which in
> > > > effect
> > > > produces a "csv" file with the planned shifts for a group of employees. My
> > > > application is designed to analyse the hours being worked in various
> > > > categories from the data contained in the spreadsheet.
> > > >
> > > > The user has the option from the third party application to select more
> > > > than
> > > > one employee and more than one week. If that option is selected the output
> > > > is
> > > > sorted by employee then by date. So employee 1 has a number of lines under
> > > > week 1 followed by a number of lines under week 2 followed by employee 2
> > > > with
> > > > week 1 and employee 2 with week 2 etc.
> > > >
> > > > Part of my analysis of the data requires that I know the number of weeks
> > > > in
> > > > the report. Presently that is done by way of a text box on the user form
> > > > asking the user to select 1 or 2 or 3 etc.
> > > >
> > > > I'd prefer if the number of weeks could be identified programatically so
> > > > my
> > > > question is :
> > > >
> > > > Is there a way we can count the number of unique values from all of the
> > > > values in a column .....
> > > >
> > > > Emp1, 22/05/06
> > > > Emp1, 29/05/06
> > > > Emp2, 22/05/06
> > > > Emp2, 29/05/06
> > > >
> > > > would give the result 2
> > > >
> > > > Note: the dates are not in consecutive rows. There is data in rows between
> > > > the firstd ate for Emp1 and second date for Emp2 etc., however the only
> > > > date
> > > > format which exists in the column is that against the employee name. None
> > > > of
> > > > the other rows between employee names contains a date format
> > > >
> > > > I hope I've explained my problem and add that I'm happy with a suggestion
> > > > for the principle of how to do it as I'm confident in my own coding to be
> > > > able to move forward if I can just have an idea as to how I could appraoch
> > > > it.
> > > >
> > > > Any help or suggestions would be greatly appreciated.
> > > >
> > > > Regards
> > > >
> > > > Michael
> > > > in employee order
> > >
> > >
> > >

amatuerish, not at all. Very well done and the way I would do it as well
until someone showed me how to really use a collection. Just trying to pass
along what I have learned in the past.

A dictionary object from the scripting runtime can be even better for many
of these situations, but in this case, I think a collection is sufficient.


--
regards,
Tom Ogilvy


"mabond" <[email protected]> wrote in message
news:[email protected]...
> Tom
>
> don't bother replying to my question. I coded it with your suggestion and
it
> worked as I need it to. Thanks for your additional guidance here. Very
neat
> solution compared with my amatuerish attempt.
>
> Regards
>
> Michael Bond
>
>
>
> "Tom Ogilvy" wrote:
>
> > Think you missed how to use the collection to do this:
> >
> > Dim x As New Collection
> > Dim y As Integer
> > y = 1
> >
> > Do
> > If Cells(y, 4).Value = "Monday" Then
> > On Error Resume Next
> > x.Add Item:=Cells(y, 5).Value, cells(y,5).Text
> > On Error goto 0
> > End If
> > y = y + 1
> > Loop Until y = 1000
> >
> > z = x.Count
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "mabond" wrote:
> >
> > > Nigel
> > >
> > > Thanks, it was a push in the right direction I needed. Because of the
layout
> > > of the exported file from the third party app the Filters was not an
easy
> > > option....but the collection object came up with the result. I'm
already
> > > looping through each line in the file so I included the following to
identify
> > > unique dates
> > >
> > > Dim x As New Collection
> > > Dim y As Integer
> > > Dim MyObject
> > > y = 1
> > >
> > > Dim dateExists As Boolean
> > > dateExists = False
> > >
> > > x.Add Item:=Cells(4, 5).Value ' the first date is always in this
cell
> > >
> > > Do
> > > If Cells(y, 4).Value = "Monday" Then
> > > dateExists = False
> > > For Each MyObject In x
> > > If MyObject = Cells(y, 5).Value Then
> > > dateExists = True
> > > End If
> > > Next MyObject
> > > If dateExists = False Then
> > > x.Add Item:=Cells(y, 5).Value
> > > End If
> > > End If
> > > y = y + 1
> > > Loop Until y = 1000
> > >
> > > z = x.Count
> > >
> > > Many thanks again
> > >
> > > Regards
> > >
> > > Michael Bond
> > >
> > > "Nigel" wrote:
> > >
> > > > Have you looked at using advanced filters? This can be configured
to
> > > > produce a list of unique values that may provide the list you
require.
> > > >
> > > > If not then you might consider using a Collection Object to build a
unique
> > > > list
> > > >
> > > > --
> > > > Cheers
> > > > Nigel
> > > >
> > > >
> > > >
> > > > "mabond" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > Hi all
> > > > >
> > > > > I'm hoping someone out there can suggest a way forward with a
spreadsheet
> > > > > application Ive recently developed.
> > > > >
> > > > > The spreadsheet contains data from a third party application which
in
> > > > > effect
> > > > > produces a "csv" file with the planned shifts for a group of
employees. My
> > > > > application is designed to analyse the hours being worked in
various
> > > > > categories from the data contained in the spreadsheet.
> > > > >
> > > > > The user has the option from the third party application to select
more
> > > > > than
> > > > > one employee and more than one week. If that option is selected
the output
> > > > > is
> > > > > sorted by employee then by date. So employee 1 has a number of
lines under
> > > > > week 1 followed by a number of lines under week 2 followed by
employee 2
> > > > > with
> > > > > week 1 and employee 2 with week 2 etc.
> > > > >
> > > > > Part of my analysis of the data requires that I know the number of
weeks
> > > > > in
> > > > > the report. Presently that is done by way of a text box on the
user form
> > > > > asking the user to select 1 or 2 or 3 etc.
> > > > >
> > > > > I'd prefer if the number of weeks could be identified
programatically so
> > > > > my
> > > > > question is :
> > > > >
> > > > > Is there a way we can count the number of unique values from all
of the
> > > > > values in a column .....
> > > > >
> > > > > Emp1, 22/05/06
> > > > > Emp1, 29/05/06
> > > > > Emp2, 22/05/06
> > > > > Emp2, 29/05/06
> > > > >
> > > > > would give the result 2
> > > > >
> > > > > Note: the dates are not in consecutive rows. There is data in rows
between
> > > > > the firstd ate for Emp1 and second date for Emp2 etc., however the
only
> > > > > date
> > > > > format which exists in the column is that against the employee
name. None
> > > > > of
> > > > > the other rows between employee names contains a date format
> > > > >
> > > > > I hope I've explained my problem and add that I'm happy with a
suggestion
> > > > > for the principle of how to do it as I'm confident in my own
coding to be
> > > > > able to move forward if I can just have an idea as to how I could
appraoch
> > > > > it.
> > > > >
> > > > > Any help or suggestions would be greatly appreciated.
> > > > >
> > > > > Regards
> > > > >
> > > > > Michael
> > > > > in employee order
> > > >
> > > >
> > > >