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