Range("T4:T1443").Formula = "=IF(COUNTIF($H$4:$H$1443,H4)=1,H4)"

try as i may my ifcount always evaluates as false where as i want it show me
my unique product list - i dont care if the other cells are false

i appreciate all the help and i have got vba to enter the formula which is a
step forward but im not sure i understand the ifcount principal as i thought
it would do this

my code is shown below and i have been careful with both quotes and $ signs;

Sub spot_duplicates()

Sheets("Data").Select

Range("v4:v1443").Formula = "=IF(COUNTIF($H$4:$H$1443,$H$4)=1,H4)"

End Sub




--
C Ward

Well you do not have a false condition, so if H4 appears in the list more
than once you have no action to take. Is that the problem?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"christopher ward" wrote in
message ...
Range("T4:T1443").Formula = "=IF(COUNTIF($H$4:$H$1443,H4)=1,H4)"

try as i may my ifcount always evaluates as false where as i want it show
me
my unique product list - i dont care if the other cells are false

i appreciate all the help and i have got vba to enter the formula which is
a
step forward but im not sure i understand the ifcount principal as i
thought
it would do this

my code is shown below and i have been careful with both quotes and $
signs;

Sub spot_duplicates()

Sheets("Data").Select

Range("v4:v1443").Formula = "=IF(COUNTIF($H$4:$H$1443,$H$4)=1,H4)"

End Sub




--
C Ward

Your if statement is at fault, and the lack of anything after H4... The H4
is what appears if the item appears at all (Evaluates as true). Your If
statement says, if I have the item only one time, then show me the item. If
I do not have the item, or it appears more than once, then do nothing
(Evaluate to false which is what you are seeing.)
I tried changing the =1 to 1 and =1 and did not get a unique list, but I
did at least remove the false.

If you change your range of the countif statement to where it includes a
search of only the current row to the last row, and do an equivalency check
of =1, then in the end you will have a list of False at every instance that
there is more than one of that item, and only a list of the items at the last
usage of that item...

To implement this, if you go back to my for I = 4 to X routine that was
discussed yesterday, and revise the start row of the countif portion in the
equation being inserted, you can implement what I just described.
So it would look like this:

For I = 4 to X <- Where X is the last row you want to include data
Range("T"&I).Formula = "=IF(COUNTIF(H"&I&":H1443,H4)=1,H"&I&")"
Next I

And if you remember to copy the above Range.formula line exactly as written,
including all quotes, it will provide you a list of unique items and false at
each location where the search determines that there is another of the same
item below. If you weed out all of the False items, then you will be left
with a list of unique items though possibly spread out from top to bottom.


Basically

I don't see how you would get the unique product list from this form of
evaluation. A pivot table would be more appropriate for a unique product
list. Also explaining the reason you are trying to do what you are doing
helps us all out in resolution.



"christopher ward" wrote:

Range("T4:T1443").Formula = "=IF(COUNTIF($H$4:$H$1443,H4)=1,H4)"

try as i may my ifcount always evaluates as false where as i want it show me
my unique product list - i dont care if the other cells are false

i appreciate all the help and i have got vba to enter the formula which is a
step forward but im not sure i understand the ifcount principal as i thought
it would do this

my code is shown below and i have been careful with both quotes and $ signs;

Sub spot_duplicates()

Sheets("Data").Select

Range("v4:v1443").Formula = "=IF(COUNTIF($H$4:$H$1443,$H$4)=1,H4)"

End Sub




--
C Ward

Well, okay maybe not copying exactly, as I saw that you had implemented some
$'s, but at least get the quotes right. :)

Sorry was having trouble getting all of the lines of code we have discussed
on different threads together. Please try to continue an issue on the same
thread by replying to one of the messages there. Any message...
I think you have referred to this problem like 4 times in the last 2 days on
4 different threads.

I have revised my formula as given in my last post to include an additional
"&I&" as seen in the COUNTIF Function.

Again, please be sure to copy all quotes that are included.

For I = 4 to X <- Where X is the last row you want to include data
Range("T"&I).Formula = "=IF(COUNTIF(H"&I&":H1443,H"&I&")=1,H"&I&")"
Next I

I think your formula always tried to plug in the result of finding H4,
instead of looking to see how many times the item in the current row appears.

"GB" wrote:

Your if statement is at fault, and the lack of anything after H4... The H4
is what appears if the item appears at all (Evaluates as true). Your If
statement says, if I have the item only one time, then show me the item. If
I do not have the item, or it appears more than once, then do nothing
(Evaluate to false which is what you are seeing.)
I tried changing the =1 to 1 and =1 and did not get a unique list, but I
did at least remove the false.

If you change your range of the countif statement to where it includes a
search of only the current row to the last row, and do an equivalency check
of =1, then in the end you will have a list of False at every instance that
there is more than one of that item, and only a list of the items at the last
usage of that item...

To implement this, if you go back to my for I = 4 to X routine that was
discussed yesterday, and revise the start row of the countif portion in the
equation being inserted, you can implement what I just described.
So it would look like this:

For I = 4 to X <- Where X is the last row you want to include data
Range("T"&I).Formula = "=IF(COUNTIF(H"&I&":H1443,H4)=1,H"&I&")"
Next I

And if you remember to copy the above Range.formula line exactly as written,
including all quotes, it will provide you a list of unique items and false at
each location where the search determines that there is another of the same
item below. If you weed out all of the False items, then you will be left
with a list of unique items though possibly spread out from top to bottom.


Basically

I don't see how you would get the unique product list from this form of
evaluation. A pivot table would be more appropriate for a unique product
list. Also explaining the reason you are trying to do what you are doing
helps us all out in resolution.



"christopher ward" wrote:

Range("T4:T1443").Formula = "=IF(COUNTIF($H$4:$H$1443,H4)=1,H4)"

try as i may my ifcount always evaluates as false where as i want it show me
my unique product list - i dont care if the other cells are false

i appreciate all the help and i have got vba to enter the formula which is a
step forward but im not sure i understand the ifcount principal as i thought
it would do this

my code is shown below and i have been careful with both quotes and $ signs;

Sub spot_duplicates()

Sheets("Data").Select

Range("v4:v1443").Formula = "=IF(COUNTIF($H$4:$H$1443,$H$4)=1,H4)"

End Sub




--
C Ward

sorrry about multi thread must admit i find the usergroup system not clear -
i will try and thanks for advice i will try to do it now
--
C Ward


"GB" wrote:

Well, okay maybe not copying exactly, as I saw that you had implemented some
$'s, but at least get the quotes right. :)

Sorry was having trouble getting all of the lines of code we have discussed
on different threads together. Please try to continue an issue on the same
thread by replying to one of the messages there. Any message...
I think you have referred to this problem like 4 times in the last 2 days on
4 different threads.

I have revised my formula as given in my last post to include an additional
"&I&" as seen in the COUNTIF Function.

Again, please be sure to copy all quotes that are included.

For I = 4 to X <- Where X is the last row you want to include data
Range("T"&I).Formula = "=IF(COUNTIF(H"&I&":H1443,H"&I&")=1,H"&I&")"
Next I

I think your formula always tried to plug in the result of finding H4,
instead of looking to see how many times the item in the current row appears.

"GB" wrote:

Your if statement is at fault, and the lack of anything after H4... The H4
is what appears if the item appears at all (Evaluates as true). Your If
statement says, if I have the item only one time, then show me the item. If
I do not have the item, or it appears more than once, then do nothing
(Evaluate to false which is what you are seeing.)
I tried changing the =1 to 1 and =1 and did not get a unique list, but I
did at least remove the false.

If you change your range of the countif statement to where it includes a
search of only the current row to the last row, and do an equivalency check
of =1, then in the end you will have a list of False at every instance that
there is more than one of that item, and only a list of the items at the last
usage of that item...

To implement this, if you go back to my for I = 4 to X routine that was
discussed yesterday, and revise the start row of the countif portion in the
equation being inserted, you can implement what I just described.
So it would look like this:

For I = 4 to X <- Where X is the last row you want to include data
Range("T"&I).Formula = "=IF(COUNTIF(H"&I&":H1443,H4)=1,H"&I&")"
Next I

And if you remember to copy the above Range.formula line exactly as written,
including all quotes, it will provide you a list of unique items and false at
each location where the search determines that there is another of the same
item below. If you weed out all of the False items, then you will be left
with a list of unique items though possibly spread out from top to bottom.


Basically

I don't see how you would get the unique product list from this form of
evaluation. A pivot table would be more appropriate for a unique product
list. Also explaining the reason you are trying to do what you are doing
helps us all out in resolution.



"christopher ward" wrote:

Range("T4:T1443").Formula = "=IF(COUNTIF($H$4:$H$1443,H4)=1,H4)"

try as i may my ifcount always evaluates as false where as i want it show me
my unique product list - i dont care if the other cells are false

i appreciate all the help and i have got vba to enter the formula which is a
step forward but im not sure i understand the ifcount principal as i thought
it would do this

my code is shown below and i have been careful with both quotes and $ signs;

Sub spot_duplicates()

Sheets("Data").Select

Range("v4:v1443").Formula = "=IF(COUNTIF($H$4:$H$1443,$H$4)=1,H4)"

End Sub




--
C Ward

let me explain more , currently my system has a range of products in col H
the col H contains many repeated values so code aaa can appear many times
the column start at row 4 to row 1443 and in fact is the number of minutes
in a given day in that we have 1440 minutes in every 24 hours

the if statement must be done by programming and not by a pivot table due to
the nature of the system being built, this is my first work with vba so i do
apologise if i am frustrating you

i actually want to build a long col of values which are either false or
indeed show the unique code at least once in the list so that i can then loop
on this list and force it into a list box later on to show unique codes


--
C Ward


"GB" wrote:

Well, okay maybe not copying exactly, as I saw that you had implemented some
$'s, but at least get the quotes right. :)

Sorry was having trouble getting all of the lines of code we have discussed
on different threads together. Please try to continue an issue on the same
thread by replying to one of the messages there. Any message...
I think you have referred to this problem like 4 times in the last 2 days on
4 different threads.

I have revised my formula as given in my last post to include an additional
"&I&" as seen in the COUNTIF Function.

Again, please be sure to copy all quotes that are included.

For I = 4 to X <- Where X is the last row you want to include data
Range("T"&I).Formula = "=IF(COUNTIF(H"&I&":H1443,H"&I&")=1,H"&I&")"
Next I

I think your formula always tried to plug in the result of finding H4,
instead of looking to see how many times the item in the current row appears.

"GB" wrote:

Your if statement is at fault, and the lack of anything after H4... The H4
is what appears if the item appears at all (Evaluates as true). Your If
statement says, if I have the item only one time, then show me the item. If
I do not have the item, or it appears more than once, then do nothing
(Evaluate to false which is what you are seeing.)
I tried changing the =1 to 1 and =1 and did not get a unique list, but I
did at least remove the false.

If you change your range of the countif statement to where it includes a
search of only the current row to the last row, and do an equivalency check
of =1, then in the end you will have a list of False at every instance that
there is more than one of that item, and only a list of the items at the last
usage of that item...

To implement this, if you go back to my for I = 4 to X routine that was
discussed yesterday, and revise the start row of the countif portion in the
equation being inserted, you can implement what I just described.
So it would look like this:

For I = 4 to X <- Where X is the last row you want to include data
Range("T"&I).Formula = "=IF(COUNTIF(H"&I&":H1443,H4)=1,H"&I&")"
Next I

And if you remember to copy the above Range.formula line exactly as written,
including all quotes, it will provide you a list of unique items and false at
each location where the search determines that there is another of the same
item below. If you weed out all of the False items, then you will be left
with a list of unique items though possibly spread out from top to bottom.


Basically

I don't see how you would get the unique product list from this form of
evaluation. A pivot table would be more appropriate for a unique product
list. Also explaining the reason you are trying to do what you are doing
helps us all out in resolution.



"christopher ward" wrote:

Range("T4:T1443").Formula = "=IF(COUNTIF($H$4:$H$1443,H4)=1,H4)"

try as i may my ifcount always evaluates as false where as i want it show me
my unique product list - i dont care if the other cells are false

i appreciate all the help and i have got vba to enter the formula which is a
step forward but im not sure i understand the ifcount principal as i thought
it would do this

my code is shown below and i have been careful with both quotes and $ signs;

Sub spot_duplicates()

Sheets("Data").Select

Range("v4:v1443").Formula = "=IF(COUNTIF($H$4:$H$1443,$H$4)=1,H4)"

End Sub




--
C Ward

Maybe I'm trying to hard on this response, but want to make sure that at
least you and I are on the par with each other. The code that I provided
using the for statement, (which based on the below e-mail 1443 would be your
X in the for loop), should provide exactly what you are looking for. It is
obviously implemented in VBA, as a pivot table for this instance would not
work. However.... Do realize that you could have a standing pivot table that
gets refreshed when the source data changes. The pivot table location would
be known and you could still do other VBA work off of it. But, pending that
next step, the for statement would work...

Here is what it does. It says, if the first item is in the entire list more
than once, then the location holding the "copy" of the data is equal to
FALSE. On the second item, it says, if this item is *also* somewhere in the
following lines then it should be FALSE, however if the item in Row 5 Column
H is only in the remaining rows of column H, then it is shown. This process
continues for each additional item. Obviously the last item where it will
look at only itself is in the list at least one time but then also obviously
not in the list more than once, as there is only a group of one. (Sorry for
all the logic speak, but that's what was necessary to determine how to get
what you wanted.) The end result, is that if you work your way from the end
of the list to the beginning, the first time you find the item it will be in
your list of FALSE's and unique items. Now, programmatically and depending
on what you do with this data, it may be better to have the item show up the
first time it is used from the top. To do this, instead of adjusting the
range from current location to the end of the list, make it go from the first
cell to the current location. Your population of unique items would be
towards the top vice the bottom.

Get back with us if the code works, or at least if the concept does. :)


"christopher ward" wrote:

let me explain more , currently my system has a range of products in col H
the col H contains many repeated values so code aaa can appear many times
the column start at row 4 to row 1443 and in fact is the number of minutes
in a given day in that we have 1440 minutes in every 24 hours

the if statement must be done by programming and not by a pivot table due to
the nature of the system being built, this is my first work with vba so i do
apologise if i am frustrating you

i actually want to build a long col of values which are either false or
indeed show the unique code at least once in the list so that i can then loop
on this list and force it into a list box later on to show unique codes


--
C Ward


"GB" wrote:

Well, okay maybe not copying exactly, as I saw that you had implemented some
$'s, but at least get the quotes right. :)

Sorry was having trouble getting all of the lines of code we have discussed
on different threads together. Please try to continue an issue on the same
thread by replying to one of the messages there. Any message...
I think you have referred to this problem like 4 times in the last 2 days on
4 different threads.

I have revised my formula as given in my last post to include an additional
"&I&" as seen in the COUNTIF Function.

Again, please be sure to copy all quotes that are included.

For I = 4 to X <- Where X is the last row you want to include data
Range("T"&I).Formula = "=IF(COUNTIF(H"&I&":H1443,H"&I&")=1,H"&I&")"
Next I

I think your formula always tried to plug in the result of finding H4,
instead of looking to see how many times the item in the current row appears.

"GB" wrote:

Your if statement is at fault, and the lack of anything after H4... The H4
is what appears if the item appears at all (Evaluates as true). Your If
statement says, if I have the item only one time, then show me the item. If
I do not have the item, or it appears more than once, then do nothing
(Evaluate to false which is what you are seeing.)
I tried changing the =1 to 1 and =1 and did not get a unique list, but I
did at least remove the false.

If you change your range of the countif statement to where it includes a
search of only the current row to the last row, and do an equivalency check
of =1, then in the end you will have a list of False at every instance that
there is more than one of that item, and only a list of the items at the last
usage of that item...

To implement this, if you go back to my for I = 4 to X routine that was
discussed yesterday, and revise the start row of the countif portion in the
equation being inserted, you can implement what I just described.
So it would look like this:

For I = 4 to X <- Where X is the last row you want to include data
Range("T"&I).Formula = "=IF(COUNTIF(H"&I&":H1443,H4)=1,H"&I&")"
Next I

And if you remember to copy the above Range.formula line exactly as written,
including all quotes, it will provide you a list of unique items and false at
each location where the search determines that there is another of the same
item below. If you weed out all of the False items, then you will be left
with a list of unique items though possibly spread out from top to bottom.


Basically

I don't see how you would get the unique product list from this form of
evaluation. A pivot table would be more appropriate for a unique product
list. Also explaining the reason you are trying to do what you are doing
helps us all out in resolution.



"christopher ward" wrote:

Range("T4:T1443").Formula = "=IF(COUNTIF($H$4:$H$1443,H4)=1,H4)"

try as i may my ifcount always evaluates as false where as i want it show me
my unique product list - i dont care if the other cells are false

i appreciate all the help and i have got vba to enter the formula which is a
step forward but im not sure i understand the ifcount principal as i thought
it would do this

my code is shown below and i have been careful with both quotes and $ signs;

Sub spot_duplicates()

Sheets("Data").Select

Range("v4:v1443").Formula = "=IF(COUNTIF($H$4:$H$1443,$H$4)=1,H4)"

End Sub




--
C Ward

dear Gb - thank you for yor efforts im afraid on this topic i have not
progressed
i tried to copy in you

For I = 4 to X <- Where X is the last row you want to include data
Range("T"&I).Formula = "=IF(COUNTIF(H"&I&":H1443,H"&I&")=1,H"&I&")"
Next I

however got lots of compile errors

im sure i can get vba to do what i want with this function but at the moment
im like a blind man looking at the screen

my code currently stands at

Sub spot_duplicates()

Sheets("Data").Select
Range("v4:v1443").Formula = "=IF(COUNTIF($H$4:$H$1443,$H$4)=1,H4)"

End Sub

my results in cells v4 to v 1443 = =IF(COUNTIF($H$4:$H$1443,$H$4)=1,H4)
which shows as a FALSE all down the 1443 cells that the formula is placed into
sorry i cant describe this issue any more - i have however solved the multi
page issue on the other thread

--
C Ward


"GB" wrote:

Maybe I'm trying to hard on this response, but want to make sure that at
least you and I are on the par with each other. The code that I provided
using the for statement, (which based on the below e-mail 1443 would be your
X in the for loop), should provide exactly what you are looking for. It is
obviously implemented in VBA, as a pivot table for this instance would not
work. However.... Do realize that you could have a standing pivot table that
gets refreshed when the source data changes. The pivot table location would
be known and you could still do other VBA work off of it. But, pending that
next step, the for statement would work...

Here is what it does. It says, if the first item is in the entire list more
than once, then the location holding the "copy" of the data is equal to
FALSE. On the second item, it says, if this item is *also* somewhere in the
following lines then it should be FALSE, however if the item in Row 5 Column
H is only in the remaining rows of column H, then it is shown. This process
continues for each additional item. Obviously the last item where it will
look at only itself is in the list at least one time but then also obviously
not in the list more than once, as there is only a group of one. (Sorry for
all the logic speak, but that's what was necessary to determine how to get
what you wanted.) The end result, is that if you work your way from the end
of the list to the beginning, the first time you find the item it will be in
your list of FALSE's and unique items. Now, programmatically and depending
on what you do with this data, it may be better to have the item show up the
first time it is used from the top. To do this, instead of adjusting the
range from current location to the end of the list, make it go from the first
cell to the current location. Your population of unique items would be
towards the top vice the bottom.

Get back with us if the code works, or at least if the concept does. :)


"christopher ward" wrote:

let me explain more , currently my system has a range of products in col H
the col H contains many repeated values so code aaa can appear many times
the column start at row 4 to row 1443 and in fact is the number of minutes
in a given day in that we have 1440 minutes in every 24 hours

the if statement must be done by programming and not by a pivot table due to
the nature of the system being built, this is my first work with vba so i do
apologise if i am frustrating you

i actually want to build a long col of values which are either false or
indeed show the unique code at least once in the list so that i can then loop
on this list and force it into a list box later on to show unique codes


--
C Ward


"GB" wrote:

Well, okay maybe not copying exactly, as I saw that you had implemented some
$'s, but at least get the quotes right. :)

Sorry was having trouble getting all of the lines of code we have discussed
on different threads together. Please try to continue an issue on the same
thread by replying to one of the messages there. Any message...
I think you have referred to this problem like 4 times in the last 2 days on
4 different threads.

I have revised my formula as given in my last post to include an additional
"&I&" as seen in the COUNTIF Function.

Again, please be sure to copy all quotes that are included.

For I = 4 to X <- Where X is the last row you want to include data
Range("T"&I).Formula = "=IF(COUNTIF(H"&I&":H1443,H"&I&")=1,H"&I&")"
Next I

I think your formula always tried to plug in the result of finding H4,
instead of looking to see how many times the item in the current row appears.

"GB" wrote:

Your if statement is at fault, and the lack of anything after H4... The H4
is what appears if the item appears at all (Evaluates as true). Your If
statement says, if I have the item only one time, then show me the item. If
I do not have the item, or it appears more than once, then do nothing
(Evaluate to false which is what you are seeing.)
I tried changing the =1 to 1 and =1 and did not get a unique list, but I
did at least remove the false.

If you change your range of the countif statement to where it includes a
search of only the current row to the last row, and do an equivalency check
of =1, then in the end you will have a list of False at every instance that
there is more than one of that item, and only a list of the items at the last
usage of that item...

To implement this, if you go back to my for I = 4 to X routine that was
discussed yesterday, and revise the start row of the countif portion in the
equation being inserted, you can implement what I just described.
So it would look like this:

For I = 4 to X <- Where X is the last row you want to include data
Range("T"&I).Formula = "=IF(COUNTIF(H"&I&":H1443,H4)=1,H"&I&")"
Next I

And if you remember to copy the above Range.formula line exactly as written,
including all quotes, it will provide you a list of unique items and false at
each location where the search determines that there is another of the same
item below. If you weed out all of the False items, then you will be left
with a list of unique items though possibly spread out from top to bottom.


Basically

I don't see how you would get the unique product list from this form of
evaluation. A pivot table would be more appropriate for a unique product
list. Also explaining the reason you are trying to do what you are doing
helps us all out in resolution.



"christopher ward" wrote:

Range("T4:T1443").Formula = "=IF(COUNTIF($H$4:$H$1443,H4)=1,H4)"

try as i may my ifcount always evaluates as false where as i want it show me
my unique product list - i dont care if the other cells are false

i appreciate all the help and i have got vba to enter the formula which is a
step forward but im not sure i understand the ifcount principal as i thought
it would do this

my code is shown below and i have been careful with both quotes and $ signs;

Sub spot_duplicates()

Sheets("Data").Select

Range("v4:v1443").Formula = "=IF(COUNTIF($H$4:$H$1443,$H$4)=1,H4)"

End Sub




--
C Ward

2 other points

i only ever select the Data sheet as I like to know the sheet that is active
although i appreciate other lines may not need the select statement

i only use lines 4 to 1443 on my Data sheet which is the minutes in any
given day so an Integer value on the loop should be ok as i never use all the
rows on the sheet although your point is well made

--
C Ward


"christopher ward" wrote:

dear Gb - thank you for yor efforts im afraid on this topic i have not
progressed
i tried to copy in you

For I = 4 to X <- Where X is the last row you want to include data
Range("T"&I).Formula = "=IF(COUNTIF(H"&I&":H1443,H"&I&")=1,H"&I&")"
Next I

however got lots of compile errors

im sure i can get vba to do what i want with this function but at the moment
im like a blind man looking at the screen

my code currently stands at

Sub spot_duplicates()

Sheets("Data").Select
Range("v4:v1443").Formula = "=IF(COUNTIF($H$4:$H$1443,$H$4)=1,H4)"

End Sub

my results in cells v4 to v 1443 = =IF(COUNTIF($H$4:$H$1443,$H$4)=1,H4)
which shows as a FALSE all down the 1443 cells that the formula is placed into
sorry i cant describe this issue any more - i have however solved the multi
page issue on the other thread

--
C Ward


"GB" wrote:

Maybe I'm trying to hard on this response, but want to make sure that at
least you and I are on the par with each other. The code that I provided
using the for statement, (which based on the below e-mail 1443 would be your
X in the for loop), should provide exactly what you are looking for. It is
obviously implemented in VBA, as a pivot table for this instance would not
work. However.... Do realize that you could have a standing pivot table that
gets refreshed when the source data changes. The pivot table location would
be known and you could still do other VBA work off of it. But, pending that
next step, the for statement would work...

Here is what it does. It says, if the first item is in the entire list more
than once, then the location holding the "copy" of the data is equal to
FALSE. On the second item, it says, if this item is *also* somewhere in the
following lines then it should be FALSE, however if the item in Row 5 Column
H is only in the remaining rows of column H, then it is shown. This process
continues for each additional item. Obviously the last item where it will
look at only itself is in the list at least one time but then also obviously
not in the list more than once, as there is only a group of one. (Sorry for
all the logic speak, but that's what was necessary to determine how to get
what you wanted.) The end result, is that if you work your way from the end
of the list to the beginning, the first time you find the item it will be in
your list of FALSE's and unique items. Now, programmatically and depending
on what you do with this data, it may be better to have the item show up the
first time it is used from the top. To do this, instead of adjusting the
range from current location to the end of the list, make it go from the first
cell to the current location. Your population of unique items would be
towards the top vice the bottom.

Get back with us if the code works, or at least if the concept does. :)


"christopher ward" wrote:

let me explain more , currently my system has a range of products in col H
the col H contains many repeated values so code aaa can appear many times
the column start at row 4 to row 1443 and in fact is the number of minutes
in a given day in that we have 1440 minutes in every 24 hours

the if statement must be done by programming and not by a pivot table due to
the nature of the system being built, this is my first work with vba so i do
apologise if i am frustrating you

i actually want to build a long col of values which are either false or
indeed show the unique code at least once in the list so that i can then loop
on this list and force it into a list box later on to show unique codes


--
C Ward


"GB" wrote:

Well, okay maybe not copying exactly, as I saw that you had implemented some
$'s, but at least get the quotes right. :)

Sorry was having trouble getting all of the lines of code we have discussed
on different threads together. Please try to continue an issue on the same
thread by replying to one of the messages there. Any message...
I think you have referred to this problem like 4 times in the last 2 days on
4 different threads.

I have revised my formula as given in my last post to include an additional
"&I&" as seen in the COUNTIF Function.

Again, please be sure to copy all quotes that are included.

For I = 4 to X <- Where X is the last row you want to include data
Range("T"&I).Formula = "=IF(COUNTIF(H"&I&":H1443,H"&I&")=1,H"&I&")"
Next I

I think your formula always tried to plug in the result of finding H4,
instead of looking to see how many times the item in the current row appears.

"GB" wrote:

Your if statement is at fault, and the lack of anything after H4... The H4
is what appears if the item appears at all (Evaluates as true). Your If
statement says, if I have the item only one time, then show me the item. If
I do not have the item, or it appears more than once, then do nothing
(Evaluate to false which is what you are seeing.)
I tried changing the =1 to 1 and =1 and did not get a unique list, but I
did at least remove the false.

If you change your range of the countif statement to where it includes a
search of only the current row to the last row, and do an equivalency check
of =1, then in the end you will have a list of False at every instance that
there is more than one of that item, and only a list of the items at the last
usage of that item...

To implement this, if you go back to my for I = 4 to X routine that was
discussed yesterday, and revise the start row of the countif portion in the
equation being inserted, you can implement what I just described.
So it would look like this:

For I = 4 to X <- Where X is the last row you want to include data
Range("T"&I).Formula = "=IF(COUNTIF(H"&I&":H1443,H4)=1,H"&I&")"
Next I

And if you remember to copy the above Range.formula line exactly as written,
including all quotes, it will provide you a list of unique items and false at
each location where the search determines that there is another of the same
item below. If you weed out all of the False items, then you will be left
with a list of unique items though possibly spread out from top to bottom.


Basically

I don't see how you would get the unique product list from this form of
evaluation. A pivot table would be more appropriate for a unique product
list. Also explaining the reason you are trying to do what you are doing
helps us all out in resolution.



"christopher ward" wrote:

Range("T4:T1443").Formula = "=IF(COUNTIF($H$4:$H$1443,H4)=1,H4)"

try as i may my ifcount always evaluates as false where as i want it show me
my unique product list - i dont care if the other cells are false

i appreciate all the help and i have got vba to enter the formula which is a
step forward but im not sure i understand the ifcount principal as i thought
it would do this

my code is shown below and i have been careful with both quotes and $ signs;

Sub spot_duplicates()

Sheets("Data").Select

Range("v4:v1443").Formula = "=IF(COUNTIF($H$4:$H$1443,$H$4)=1,H4)"

End Sub




--
C Ward

Dear GB

yes - if i create a pivot table i can see the unique codes which if i could
then get vba to get into a list i could force them into my list box and this
would indeed solve my issue although somewhat a long winded mewthod but i
applaud your thought process - with regard to pivot tables i have never
worked with these so i guess it gives me more code and learning to do - I
would rather sort the issue by code as discussed clearly but i do note you
answered the issue - im afraid my system is complex in nature so im not sure
i can progress this route rather try to get ifcount working but thanks for a
top answer
--
C Ward


"GB" wrote:

Maybe I'm trying to hard on this response, but want to make sure that at
least you and I are on the par with each other. The code that I provided
using the for statement, (which based on the below e-mail 1443 would be your
X in the for loop), should provide exactly what you are looking for. It is
obviously implemented in VBA, as a pivot table for this instance would not
work. However.... Do realize that you could have a standing pivot table that
gets refreshed when the source data changes. The pivot table location would
be known and you could still do other VBA work off of it. But, pending that
next step, the for statement would work...

Here is what it does. It says, if the first item is in the entire list more
than once, then the location holding the "copy" of the data is equal to
FALSE. On the second item, it says, if this item is *also* somewhere in the
following lines then it should be FALSE, however if the item in Row 5 Column
H is only in the remaining rows of column H, then it is shown. This process
continues for each additional item. Obviously the last item where it will
look at only itself is in the list at least one time but then also obviously
not in the list more than once, as there is only a group of one. (Sorry for
all the logic speak, but that's what was necessary to determine how to get
what you wanted.) The end result, is that if you work your way from the end
of the list to the beginning, the first time you find the item it will be in
your list of FALSE's and unique items. Now, programmatically and depending
on what you do with this data, it may be better to have the item show up the
first time it is used from the top. To do this, instead of adjusting the
range from current location to the end of the list, make it go from the first
cell to the current location. Your population of unique items would be
towards the top vice the bottom.

Get back with us if the code works, or at least if the concept does. :)


"christopher ward" wrote:

let me explain more , currently my system has a range of products in col H
the col H contains many repeated values so code aaa can appear many times
the column start at row 4 to row 1443 and in fact is the number of minutes
in a given day in that we have 1440 minutes in every 24 hours

the if statement must be done by programming and not by a pivot table due to
the nature of the system being built, this is my first work with vba so i do
apologise if i am frustrating you

i actually want to build a long col of values which are either false or
indeed show the unique code at least once in the list so that i can then loop
on this list and force it into a list box later on to show unique codes


--
C Ward


"GB" wrote:

Well, okay maybe not copying exactly, as I saw that you had implemented some
$'s, but at least get the quotes right. :)

Sorry was having trouble getting all of the lines of code we have discussed
on different threads together. Please try to continue an issue on the same
thread by replying to one of the messages there. Any message...
I think you have referred to this problem like 4 times in the last 2 days on
4 different threads.

I have revised my formula as given in my last post to include an additional
"&I&" as seen in the COUNTIF Function.

Again, please be sure to copy all quotes that are included.

For I = 4 to X <- Where X is the last row you want to include data
Range("T"&I).Formula = "=IF(COUNTIF(H"&I&":H1443,H"&I&")=1,H"&I&")"
Next I

I think your formula always tried to plug in the result of finding H4,
instead of looking to see how many times the item in the current row appears.

"GB" wrote:

Your if statement is at fault, and the lack of anything after H4... The H4
is what appears if the item appears at all (Evaluates as true). Your If
statement says, if I have the item only one time, then show me the item. If
I do not have the item, or it appears more than once, then do nothing
(Evaluate to false which is what you are seeing.)
I tried changing the =1 to 1 and =1 and did not get a unique list, but I
did at least remove the false.

If you change your range of the countif statement to where it includes a
search of only the current row to the last row, and do an equivalency check
of =1, then in the end you will have a list of False at every instance that
there is more than one of that item, and only a list of the items at the last
usage of that item...

To implement this, if you go back to my for I = 4 to X routine that was
discussed yesterday, and revise the start row of the countif portion in the
equation being inserted, you can implement what I just described.
So it would look like this:

For I = 4 to X <- Where X is the last row you want to include data
Range("T"&I).Formula = "=IF(COUNTIF(H"&I&":H1443,H4)=1,H"&I&")"
Next I

And if you remember to copy the above Range.formula line exactly as written,
including all quotes, it will provide you a list of unique items and false at
each location where the search determines that there is another of the same
item below. If you weed out all of the False items, then you will be left
with a list of unique items though possibly spread out from top to bottom.


Basically

I don't see how you would get the unique product list from this form of
evaluation. A pivot table would be more appropriate for a unique product
list. Also explaining the reason you are trying to do what you are doing
helps us all out in resolution.



"christopher ward" wrote:

Range("T4:T1443").Formula = "=IF(COUNTIF($H$4:$H$1443,H4)=1,H4)"

try as i may my ifcount always evaluates as false where as i want it show me
my unique product list - i dont care if the other cells are false

i appreciate all the help and i have got vba to enter the formula which is a
step forward but im not sure i understand the ifcount principal as i thought
it would do this

my code is shown below and i have been careful with both quotes and $ signs;

Sub spot_duplicates()

Sheets("Data").Select

Range("v4:v1443").Formula = "=IF(COUNTIF($H$4:$H$1443,$H$4)=1,H4)"

End Sub




--
C Ward

Let's see.. Yes you would get errors if you tried to directly copy the lines
indicated below. The value X must be either defined, or replaced. Where X
would be 1443. I also troubleshot my work. As I wrote the code here, and
not in Excel and rarely use the text append (&) I realize our error(s).
Before and after each usage of '&' there must exist a space. So here is a
rewrite of the code I provided earlier. I'm going to break it down to run
across several lines so that you might be able to copy it straight from here
into your code. If you want to make it one long line, then simply start at
the next line, delete until you have erased the underscore ('_') and ensure
that there is always at least one space before and after any & that is not in
the text.

As for your equation that you have used, you need to remove the last $ in
the equation. If I am not mistaken when you look at the equation that is in
every row of column H, every row compares the current row to the very first
row.

Anyways, try this code, and see if it gives you the unique list of items
intersperced with FALSE.

Sub TestThis()
Dim I As Integer

For I = 4 To 1443 '<- Where _
X is the last row you want to include data

Range("T" & I).Formula = _
"=IF(COUNTIF(H" & I & _
":H1443,H" & I & ")=1,H" _
& I & ")"

Next I

End Sub



"christopher ward" wrote:

dear Gb - thank you for yor efforts im afraid on this topic i have not
progressed
i tried to copy in you

For I = 4 to X <- Where X is the last row you want to include data
Range("T"&I).Formula = "=IF(COUNTIF(H"&I&":H1443,H"&I&")=1,H"&I&")"
Next I

however got lots of compile errors

im sure i can get vba to do what i want with this function but at the moment
im like a blind man looking at the screen

my code currently stands at

Sub spot_duplicates()

Sheets("Data").Select
Range("v4:v1443").Formula = "=IF(COUNTIF($H$4:$H$1443,$H$4)=1,H4)"

End Sub

my results in cells v4 to v 1443 = =IF(COUNTIF($H$4:$H$1443,$H$4)=1,H4)
which shows as a FALSE all down the 1443 cells that the formula is placed into
sorry i cant describe this issue any more - i have however solved the multi
page issue on the other thread

--
C Ward


"GB" wrote:

Maybe I'm trying to hard on this response, but want to make sure that at
least you and I are on the par with each other. The code that I provided
using the for statement, (which based on the below e-mail 1443 would be your
X in the for loop), should provide exactly what you are looking for. It is
obviously implemented in VBA, as a pivot table for this instance would not
work. However.... Do realize that you could have a standing pivot table that
gets refreshed when the source data changes. The pivot table location would
be known and you could still do other VBA work off of it. But, pending that
next step, the for statement would work...

Here is what it does. It says, if the first item is in the entire list more
than once, then the location holding the "copy" of the data is equal to
FALSE. On the second item, it says, if this item is *also* somewhere in the
following lines then it should be FALSE, however if the item in Row 5 Column
H is only in the remaining rows of column H, then it is shown. This process
continues for each additional item. Obviously the last item where it will
look at only itself is in the list at least one time but then also obviously
not in the list more than once, as there is only a group of one. (Sorry for
all the logic speak, but that's what was necessary to determine how to get
what you wanted.) The end result, is that if you work your way from the end
of the list to the beginning, the first time you find the item it will be in
your list of FALSE's and unique items. Now, programmatically and depending
on what you do with this data, it may be better to have the item show up the
first time it is used from the top. To do this, instead of adjusting the
range from current location to the end of the list, make it go from the first
cell to the current location. Your population of unique items would be
towards the top vice the bottom.

Get back with us if the code works, or at least if the concept does. :)


"christopher ward" wrote:

let me explain more , currently my system has a range of products in col H
the col H contains many repeated values so code aaa can appear many times
the column start at row 4 to row 1443 and in fact is the number of minutes
in a given day in that we have 1440 minutes in every 24 hours

the if statement must be done by programming and not by a pivot table due to
the nature of the system being built, this is my first work with vba so i do
apologise if i am frustrating you

i actually want to build a long col of values which are either false or
indeed show the unique code at least once in the list so that i can then loop
on this list and force it into a list box later on to show unique codes


--
C Ward


"GB" wrote:

Well, okay maybe not copying exactly, as I saw that you had implemented some
$'s, but at least get the quotes right. :)

Sorry was having trouble getting all of the lines of code we have discussed
on different threads together. Please try to continue an issue on the same
thread by replying to one of the messages there. Any message...
I think you have referred to this problem like 4 times in the last 2 days on
4 different threads.

I have revised my formula as given in my last post to include an additional
"&I&" as seen in the COUNTIF Function.

Again, please be sure to copy all quotes that are included.

For I = 4 to X <- Where X is the last row you want to include data
Range("T"&I).Formula = "=IF(COUNTIF(H"&I&":H1443,H"&I&")=1,H"&I&")"
Next I

I think your formula always tried to plug in the result of finding H4,
instead of looking to see how many times the item in the current row appears.

"GB" wrote:

Your if statement is at fault, and the lack of anything after H4... The H4
is what appears if the item appears at all (Evaluates as true). Your If
statement says, if I have the item only one time, then show me the item. If
I do not have the item, or it appears more than once, then do nothing
(Evaluate to false which is what you are seeing.)
I tried changing the =1 to 1 and =1 and did not get a unique list, but I
did at least remove the false.

If you change your range of the countif statement to where it includes a
search of only the current row to the last row, and do an equivalency check
of =1, then in the end you will have a list of False at every instance that
there is more than one of that item, and only a list of the items at the last
usage of that item...

To implement this, if you go back to my for I = 4 to X routine that was
discussed yesterday, and revise the start row of the countif portion in the
equation being inserted, you can implement what I just described.
So it would look like this:

For I = 4 to X <- Where X is the last row you want to include data
Range("T"&I).Formula = "=IF(COUNTIF(H"&I&":H1443,H4)=1,H"&I&")"
Next I

And if you remember to copy the above Range.formula line exactly as written,
including all quotes, it will provide you a list of unique items and false at
each location where the search determines that there is another of the same
item below. If you weed out all of the False items, then you will be left
with a list of unique items though possibly spread out from top to bottom.


Basically

I don't see how you would get the unique product list from this form of
evaluation. A pivot table would be more appropriate for a unique product
list. Also explaining the reason you are trying to do what you are doing
helps us all out in resolution.



"christopher ward" wrote:

Range("T4:T1443").Formula = "=IF(COUNTIF($H$4:$H$1443,H4)=1,H4)"

try as i may my ifcount always evaluates as false where as i want it show me
my unique product list - i dont care if the other cells are false

i appreciate all the help and i have got vba to enter the formula which is a
step forward but im not sure i understand the ifcount principal as i thought
it would do this

my code is shown below and i have been careful with both quotes and $ signs;

Sub spot_duplicates()

Sheets("Data").Select

Range("v4:v1443").Formula = "=IF(COUNTIF($H$4:$H$1443,$H$4)=1,H4)"

End Sub




--
C Ward

One thing about the results of a pivot table, is that the values that result
are actually put into the cells that are there. So for example, if a pivot
table produces a column of data from Cells B8 through B20, you can refer to
the data of B8 through B20 as if it were stored in the cell itself, so if you
know that your pivot table data always starts at a location, then you can
search downward or across as necessary to evaluate the data necessary. As
for creating the pivot table, well there are two ways, one you can set it up
by hand (And maybe even record the macro that does that.) or program it. If
the data location/size of the pivot table doesn't change, then lo and behold,
you have a useful sorting tool from which you can do all sorts of "glorious"
things. :) Okay getting a little dramatic, sorry.

Anyways, a pivot table is an interim sorting tool the results of which can
be readily used for analysis. If you have to review any excel data, see if
you can find a book on it, or just sit down in like Barnes & Nobles or
Borders for a little bit and rummage through some books on pivot tables.
There's a lot of nuiances with them, but the quick and dirty understanding of
them could take you a long way.

In fact, if you macro'd the function of creating the pivot table, and then
just use the data of the pivot table, it *would* be quick. But I completely
understand the, do what I can right now, learn other options, and make/do it
better the next time. (Gotta' say, I'm glad I didn't take the time several
years ago to implement a database in Access. Because I did it in Excel, it's
still around, had I not, then well, someone would have said it needed to go..
*smirk*)


"christopher ward" wrote:

Dear GB

yes - if i create a pivot table i can see the unique codes which if i could
then get vba to get into a list i could force them into my list box and this
would indeed solve my issue although somewhat a long winded mewthod but i
applaud your thought process - with regard to pivot tables i have never
worked with these so i guess it gives me more code and learning to do - I
would rather sort the issue by code as discussed clearly but i do note you
answered the issue - im afraid my system is complex in nature so im not sure
i can progress this route rather try to get ifcount working but thanks for a
top answer
--
C Ward


"GB" wrote:

Maybe I'm trying to hard on this response, but want to make sure that at
least you and I are on the par with each other. The code that I provided
using the for statement, (which based on the below e-mail 1443 would be your
X in the for loop), should provide exactly what you are looking for. It is
obviously implemented in VBA, as a pivot table for this instance would not
work. However.... Do realize that you could have a standing pivot table that
gets refreshed when the source data changes. The pivot table location would
be known and you could still do other VBA work off of it. But, pending that
next step, the for statement would work...

Here is what it does. It says, if the first item is in the entire list more
than once, then the location holding the "copy" of the data is equal to
FALSE. On the second item, it says, if this item is *also* somewhere in the
following lines then it should be FALSE, however if the item in Row 5 Column
H is only in the remaining rows of column H, then it is shown. This process
continues for each additional item. Obviously the last item where it will
look at only itself is in the list at least one time but then also obviously
not in the list more than once, as there is only a group of one. (Sorry for
all the logic speak, but that's what was necessary to determine how to get
what you wanted.) The end result, is that if you work your way from the end
of the list to the beginning, the first time you find the item it will be in
your list of FALSE's and unique items. Now, programmatically and depending
on what you do with this data, it may be better to have the item show up the
first time it is used from the top. To do this, instead of adjusting the
range from current location to the end of the list, make it go from the first
cell to the current location. Your population of unique items would be
towards the top vice the bottom.

Get back with us if the code works, or at least if the concept does. :)


"christopher ward" wrote:

let me explain more , currently my system has a range of products in col H
the col H contains many repeated values so code aaa can appear many times
the column start at row 4 to row 1443 and in fact is the number of minutes
in a given day in that we have 1440 minutes in every 24 hours

the if statement must be done by programming and not by a pivot table due to
the nature of the system being built, this is my first work with vba so i do
apologise if i am frustrating you

i actually want to build a long col of values which are either false or
indeed show the unique code at least once in the list so that i can then loop
on this list and force it into a list box later on to show unique codes


--
C Ward


"GB" wrote:

Well, okay maybe not copying exactly, as I saw that you had implemented some
$'s, but at least get the quotes right. :)

Sorry was having trouble getting all of the lines of code we have discussed
on different threads together. Please try to continue an issue on the same
thread by replying to one of the messages there. Any message...
I think you have referred to this problem like 4 times in the last 2 days on
4 different threads.

I have revised my formula as given in my last post to include an additional
"&I&" as seen in the COUNTIF Function.

Again, please be sure to copy all quotes that are included.

For I = 4 to X <- Where X is the last row you want to include data
Range("T"&I).Formula = "=IF(COUNTIF(H"&I&":H1443,H"&I&")=1,H"&I&")"
Next I

I think your formula always tried to plug in the result of finding H4,
instead of looking to see how many times the item in the current row appears.

"GB" wrote:

Your if statement is at fault, and the lack of anything after H4... The H4
is what appears if the item appears at all (Evaluates as true). Your If
statement says, if I have the item only one time, then show me the item. If
I do not have the item, or it appears more than once, then do nothing
(Evaluate to false which is what you are seeing.)
I tried changing the =1 to 1 and =1 and did not get a unique list, but I
did at least remove the false.

If you change your range of the countif statement to where it includes a
search of only the current row to the last row, and do an equivalency check
of =1, then in the end you will have a list of False at every instance that
there is more than one of that item, and only a list of the items at the last
usage of that item...

To implement this, if you go back to my for I = 4 to X routine that was
discussed yesterday, and revise the start row of the countif portion in the
equation being inserted, you can implement what I just described.
So it would look like this:

For I = 4 to X <- Where X is the last row you want to include data
Range("T"&I).Formula = "=IF(COUNTIF(H"&I&":H1443,H4)=1,H"&I&")"
Next I

And if you remember to copy the above Range.formula line exactly as written,
including all quotes, it will provide you a list of unique items and false at
each location where the search determines that there is another of the same
item below. If you weed out all of the False items, then you will be left
with a list of unique items though possibly spread out from top to bottom.


Basically

I don't see how you would get the unique product list from this form of
evaluation. A pivot table would be more appropriate for a unique product
list. Also explaining the reason you are trying to do what you are doing
helps us all out in resolution.



"christopher ward" wrote:

Range("T4:T1443").Formula = "=IF(COUNTIF($H$4:$H$1443,H4)=1,H4)"

try as i may my ifcount always evaluates as false where as i want it show me
my unique product list - i dont care if the other cells are false

i appreciate all the help and i have got vba to enter the formula which is a
step forward but im not sure i understand the ifcount principal as i thought
it would do this

my code is shown below and i have been careful with both quotes and $ signs;

Sub spot_duplicates()

Sheets("Data").Select

Range("v4:v1443").Formula = "=IF(COUNTIF($H$4:$H$1443,$H$4)=1,H4)"

End Sub




--
C Ward

dear GB - thanks for keeping this thread going

also i have been playing about with excels filter and advanced filter -
these may do a similar job to pick out unique values from a list - as yet i
have not coded it but all i have really is ;

col H

run 1
run 1
run 1
run 2
run 2
run 1
run 1
run 1

all i want in my unique list is Run1 and Run 2 - im not trying to count how
many occurances - this may be a simple approach - have you used this ? i will
then loop on the unique list and stuff them into a listbox within a form -
which is what i desire


chris
--
C Ward


"GB" wrote:

One thing about the results of a pivot table, is that the values that result
are actually put into the cells that are there. So for example, if a pivot
table produces a column of data from Cells B8 through B20, you can refer to
the data of B8 through B20 as if it were stored in the cell itself, so if you
know that your pivot table data always starts at a location, then you can
search downward or across as necessary to evaluate the data necessary. As
for creating the pivot table, well there are two ways, one you can set it up
by hand (And maybe even record the macro that does that.) or program it. If
the data location/size of the pivot table doesn't change, then lo and behold,
you have a useful sorting tool from which you can do all sorts of "glorious"
things. :) Okay getting a little dramatic, sorry.

Anyways, a pivot table is an interim sorting tool the results of which can
be readily used for analysis. If you have to review any excel data, see if
you can find a book on it, or just sit down in like Barnes & Nobles or
Borders for a little bit and rummage through some books on pivot tables.
There's a lot of nuiances with them, but the quick and dirty understanding of
them could take you a long way.

In fact, if you macro'd the function of creating the pivot table, and then
just use the data of the pivot table, it *would* be quick. But I completely
understand the, do what I can right now, learn other options, and make/do it
better the next time. (Gotta' say, I'm glad I didn't take the time several
years ago to implement a database in Access. Because I did it in Excel, it's
still around, had I not, then well, someone would have said it needed to go..
*smirk*)


"christopher ward" wrote:

Dear GB

yes - if i create a pivot table i can see the unique codes which if i could
then get vba to get into a list i could force them into my list box and this
would indeed solve my issue although somewhat a long winded mewthod but i
applaud your thought process - with regard to pivot tables i have never
worked with these so i guess it gives me more code and learning to do - I
would rather sort the issue by code as discussed clearly but i do note you
answered the issue - im afraid my system is complex in nature so im not sure
i can progress this route rather try to get ifcount working but thanks for a
top answer
--
C Ward


"GB" wrote:

Maybe I'm trying to hard on this response, but want to make sure that at
least you and I are on the par with each other. The code that I provided
using the for statement, (which based on the below e-mail 1443 would be your
X in the for loop), should provide exactly what you are looking for. It is
obviously implemented in VBA, as a pivot table for this instance would not
work. However.... Do realize that you could have a standing pivot table that
gets refreshed when the source data changes. The pivot table location would
be known and you could still do other VBA work off of it. But, pending that
next step, the for statement would work...

Here is what it does. It says, if the first item is in the entire list more
than once, then the location holding the "copy" of the data is equal to
FALSE. On the second item, it says, if this item is *also* somewhere in the
following lines then it should be FALSE, however if the item in Row 5 Column
H is only in the remaining rows of column H, then it is shown. This process
continues for each additional item. Obviously the last item where it will
look at only itself is in the list at least one time but then also obviously
not in the list more than once, as there is only a group of one. (Sorry for
all the logic speak, but that's what was necessary to determine how to get
what you wanted.) The end result, is that if you work your way from the end
of the list to the beginning, the first time you find the item it will be in
your list of FALSE's and unique items. Now, programmatically and depending
on what you do with this data, it may be better to have the item show up the
first time it is used from the top. To do this, instead of adjusting the
range from current location to the end of the list, make it go from the first
cell to the current location. Your population of unique items would be
towards the top vice the bottom.

Get back with us if the code works, or at least if the concept does. :)


"christopher ward" wrote:

let me explain more , currently my system has a range of products in col H
the col H contains many repeated values so code aaa can appear many times
the column start at row 4 to row 1443 and in fact is the number of minutes
in a given day in that we have 1440 minutes in every 24 hours

the if statement must be done by programming and not by a pivot table due to
the nature of the system being built, this is my first work with vba so i do
apologise if i am frustrating you

i actually want to build a long col of values which are either false or
indeed show the unique code at least once in the list so that i can then loop
on this list and force it into a list box later on to show unique codes


--
C Ward


"GB" wrote:

Well, okay maybe not copying exactly, as I saw that you had implemented some
$'s, but at least get the quotes right. :)

Sorry was having trouble getting all of the lines of code we have discussed
on different threads together. Please try to continue an issue on the same
thread by replying to one of the messages there. Any message...
I think you have referred to this problem like 4 times in the last 2 days on
4 different threads.

I have revised my formula as given in my last post to include an additional
"&I&" as seen in the COUNTIF Function.

Again, please be sure to copy all quotes that are included.

For I = 4 to X <- Where X is the last row you want to include data
Range("T"&I).Formula = "=IF(COUNTIF(H"&I&":H1443,H"&I&")=1,H"&I&")"
Next I

I think your formula always tried to plug in the result of finding H4,
instead of looking to see how many times the item in the current row appears.

"GB" wrote:

Your if statement is at fault, and the lack of anything after H4... The H4
is what appears if the item appears at all (Evaluates as true). Your If
statement says, if I have the item only one time, then show me the item. If
I do not have the item, or it appears more than once, then do nothing
(Evaluate to false which is what you are seeing.)
I tried changing the =1 to 1 and =1 and did not get a unique list, but I
did at least remove the false.

If you change your range of the countif statement to where it includes a
search of only the current row to the last row, and do an equivalency check
of =1, then in the end you will have a list of False at every instance that
there is more than one of that item, and only a list of the items at the last
usage of that item...

To implement this, if you go back to my for I = 4 to X routine that was
discussed yesterday, and revise the start row of the countif portion in the
equation being inserted, you can implement what I just described.
So it would look like this:

For I = 4 to X <- Where X is the last row you want to include data
Range("T"&I).Formula = "=IF(COUNTIF(H"&I&":H1443,H4)=1,H"&I&")"
Next I

And if you remember to copy the above Range.formula line exactly as written,
including all quotes, it will provide you a list of unique items and false at
each location where the search determines that there is another of the same
item below. If you weed out all of the False items, then you will be left
with a list of unique items though possibly spread out from top to bottom.


Basically

I don't see how you would get the unique product list from this form of
evaluation. A pivot table would be more appropriate for a unique product
list. Also explaining the reason you are trying to do what you are doing
helps us all out in resolution.



"christopher ward" wrote:

Range("T4:T1443").Formula = "=IF(COUNTIF($H$4:$H$1443,H4)=1,H4)"

try as i may my ifcount always evaluates as false where as i want it show me
my unique product list - i dont care if the other cells are false

i appreciate all the help and i have got vba to enter the formula which is a
step forward but im not sure i understand the ifcount principal as i thought
it would do this

my code is shown below and i have been careful with both quotes and $ signs;

Sub spot_duplicates()

Sheets("Data").Select

Range("v4:v1443").Formula = "=IF(COUNTIF($H$4:$H$1443,$H$4)=1,H4)"

End Sub




--
C Ward

thanks all help is excellent

this topic is now resolved - i ended up using filter list in place - which
created a unique list for me easily - of course i captured vba as i did this

once i had my list i then read it into a list box -

i guess many ways to skin a cat
--
C Ward


"GB" wrote:

You're welcome, been trying to make sure that things I have helped with are
either resolved or I reply as able.

I have tried that and gotten similar results. Then stuffed them into a
pivot table, or created an array of items. The array being that I add each
next row into the array if it is not already there. (Have used a binary tree
of binary trees to cut the time down.) The filter will allow you to select
an item from the list, and show all instances of that item. If you can
programatically pull the items that would be shown in the drop down, then yes
you could use that to create your unique list. Otherwise, if you go back to
the code that I made Excel friendly, then ran from the first item to the last
item, if the item is not equal to FALSE, copy it to your next "location"
whether it be added to a list in a form, or a worksheet, or just a data
array. Whatever, the list would be unique.



"christopher ward" wrote:

dear GB - thanks for keeping this thread going

also i have been playing about with excels filter and advanced filter -
these may do a similar job to pick out unique values from a list - as yet i
have not coded it but all i have really is ;

col H

run 1
run 1
run 1
run 2
run 2
run 1
run 1
run 1

all i want in my unique list is Run1 and Run 2 - im not trying to count how
many occurances - this may be a simple approach - have you used this ? i will
then loop on the unique list and stuff them into a listbox within a form -
which is what i desire


chris
--
C Ward


"GB" wrote:

One thing about the results of a pivot table, is that the values that result
are actually put into the cells that are there. So for example, if a pivot
table produces a column of data from Cells B8 through B20, you can refer to
the data of B8 through B20 as if it were stored in the cell itself, so if you
know that your pivot table data always starts at a location, then you can
search downward or across as necessary to evaluate the data necessary. As
for creating the pivot table, well there are two ways, one you can set it up
by hand (And maybe even record the macro that does that.) or program it. If
the data location/size of the pivot table doesn't change, then lo and behold,
you have a useful sorting tool from which you can do all sorts of "glorious"
things. :) Okay getting a little dramatic, sorry.

Anyways, a pivot table is an interim sorting tool the results of which can
be readily used for analysis. If you have to review any excel data, see if
you can find a book on it, or just sit down in like Barnes & Nobles or
Borders for a little bit and rummage through some books on pivot tables.
There's a lot of nuiances with them, but the quick and dirty understanding of
them could take you a long way.

In fact, if you macro'd the function of creating the pivot table, and then
just use the data of the pivot table, it *would* be quick. But I completely
understand the, do what I can right now, learn other options, and make/do it
better the next time. (Gotta' say, I'm glad I didn't take the time several
years ago to implement a database in Access. Because I did it in Excel, it's
still around, had I not, then well, someone would have said it needed to go..
*smirk*)


"christopher ward" wrote:

Dear GB

yes - if i create a pivot table i can see the unique codes which if i could
then get vba to get into a list i could force them into my list box and this
would indeed solve my issue although somewhat a long winded mewthod but i
applaud your thought process - with regard to pivot tables i have never
worked with these so i guess it gives me more code and learning to do - I
would rather sort the issue by code as discussed clearly but i do note you
answered the issue - im afraid my system is complex in nature so im not sure
i can progress this route rather try to get ifcount working but thanks for a
top answer
--
C Ward


"GB" wrote:

Maybe I'm trying to hard on this response, but want to make sure that at
least you and I are on the par with each other. The code that I provided
using the for statement, (which based on the below e-mail 1443 would be your
X in the for loop), should provide exactly what you are looking for. It is
obviously implemented in VBA, as a pivot table for this instance would not
work. However.... Do realize that you could have a standing pivot table that
gets refreshed when the source data changes. The pivot table location would
be known and you could still do other VBA work off of it. But, pending that
next step, the for statement would work...

Here is what it does. It says, if the first item is in the entire list more
than once, then the location holding the "copy" of the data is equal to
FALSE. On the second item, it says, if this item is *also* somewhere in the
following lines then it should be FALSE, however if the item in Row 5 Column
H is only in the remaining rows of column H, then it is shown. This process
continues for each additional item. Obviously the last item where it will
look at only itself is in the list at least one time but then also obviously
not in the list more than once, as there is only a group of one. (Sorry for
all the logic speak, but that's what was necessary to determine how to get
what you wanted.) The end result, is that if you work your way from the end
of the list to the beginning, the first time you find the item it will be in
your list of FALSE's and unique items. Now, programmatically and depending
on what you do with this data, it may be better to have the item show up the
first time it is used from the top. To do this, instead of adjusting the
range from current location to the end of the list, make it go from the first
cell to the current location. Your population of unique items would be
towards the top vice the bottom.

Get back with us if the code works, or at least if the concept does. :)


"christopher ward" wrote:

let me explain more , currently my system has a range of products in col H
the col H contains many repeated values so code aaa can appear many times
the column start at row 4 to row 1443 and in fact is the number of minutes
in a given day in that we have 1440 minutes in every 24 hours

the if statement must be done by programming and not by a pivot table due to
the nature of the system being built, this is my first work with vba so i do
apologise if i am frustrating you

i actually want to build a long col of values which are either false or
indeed show the unique code at least once in the list so that i can then loop
on this list and force it into a list box later on to show unique codes


--
C Ward


"GB" wrote:

Well, okay maybe not copying exactly, as I saw that you had implemented some
$'s, but at least get the quotes right. :)

Sorry was having trouble getting all of the lines of code we have discussed
on different threads together. Please try to continue an issue on the same
thread by replying to one of the messages there. Any message...
I think you have referred to this problem like 4 times in the last 2 days on
4 different threads.

I have revised my formula as given in my last post to include an additional
"&I&" as seen in the COUNTIF Function.

Again, please be sure to copy all quotes that are included.

For I = 4 to X <- Where X is the last row you want to include data
Range("T"&I).Formula = "=IF(COUNTIF(H"&I&":H1443,H"&I&")=1,H"&I&")"
Next I

I think your formula always tried to plug in the result of finding H4,
instead of looking to see how many times the item in the current row appears.

"GB" wrote:

Your if statement is at fault, and the lack of anything after H4... The H4
is what appears if the item appears at all (Evaluates as true). Your If
statement says, if I have the item only one time, then show me the item. If
I do not have the item, or it appears more than once, then do nothing
(Evaluate to false which is what you are seeing.)
I tried changing the =1 to 1 and =1 and did not get a unique list, but I
did at least remove the false.

If you change your range of the countif statement to where it includes a
search of only the current row to the last row, and do an equivalency check
of =1, then in the end you will have a list of False at every instance that
there is more than one of that item, and only a list of the items at the last
usage of that item...

To implement this, if you go back to my for I = 4 to X routine that was
discussed yesterday, and revise the start row of the countif portion in the
equation being inserted, you can implement what I just described.
So it would look like this:

For I = 4 to X <- Where X is the last row you want to include data
Range("T"&I).Formula = "=IF(COUNTIF(H"&I&":H1443,H4)=1,H"&I&")"
Next I

And if you remember to copy the above Range.formula line exactly as written,
including all quotes, it will provide you a list of unique items and false at
each location where the search determines that there is another of the same
item below. If you weed out all of the False items, then you will be left
with a list of unique items though possibly spread out from top to bottom.


Basically

I don't see how you would get the unique product list from this form of
evaluation. A pivot table would be more appropriate for a unique product
list. Also explaining the reason you are trying to do what you are doing
helps us all out in resolution.



"christopher ward" wrote:

Range("T4:T1443").Formula = "=IF(COUNTIF($H$4:$H$1443,H4)=1,H4)"

try as i may my ifcount always evaluates as false where as i want it show me
my unique product list - i dont care if the other cells are false

i appreciate all the help and i have got vba to enter the formula which is a
step forward but im not sure i understand the ifcount principal as i thought
it would do this

my code is shown below and i have been careful with both quotes and $ signs;

Sub spot_duplicates()

Sheets("Data").Select

Range("v4:v1443").Formula = "=IF(COUNTIF($H$4:$H$1443,$H$4)=1,H4)"

End Sub




--
C Ward