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