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